Custom lookup select issue

Question

The idea is to contruct a lookup with unique Employer name. The lookup works fine but when I select a value and then select the lookup button again and click on the place marked in RED, there are duplicate values, which is wrong.

Kindly refer the snippet and snapshot

QueryBuildDataSource    qbds;
Query                   query = new Query();
FormStringControl       control = dialog.formRun().controlCallingMethod();
SysTableLookup          sysTableLookup =  SysTableLookup::newParameters(tablenum(VendTable), control);
;

qbds  = query.addDataSource(tablenum(VendTable));
qbds.addGroupByField(fieldnum(VendTable,EmployerName));
sysTableLookup.addLookupfield(fieldnum(VendTable, EmployerName));

sysTableLookup.parmQuery(query);
sysTableLookup.parmUseLookupValue(false);
sysTableLookup.performFormLookup();

tion here]2

Answer 1

In these cases I use this approach. It's similar to the answer proposed by Jan, but simpler.

Create a TMP table with fields you want to see in lookup, including EmployerName, I will call it MyTmpTable. Well, in fact you can use VendTable as tmp table with setTmp(), but it's prone to errors (what if you insert() and forgot setTmp() before?) and it has many fields (more RAM consumption, even if they're empty); so i'd rather create a new TMPTable.

Now in VendTable here goes this lookup method:

static voidlookup_EmployerName(FormControl _callingControl)
{
    VendTable       vendTable;
    MyTmpTable      tmpTable;
    SysTableLookup  sysTableLookup;
    ;
    while select EmployerName from vendTable
        group by EmployerName
    {
        tmpTable.EmployerName = vendTable.EmployerName;
        tmpTable.insert();
    }        

    sysTableLookup = SysTableLookup::newParameters(tableNum(MyTmpTable),_callingControl);
    sysTableLookup.addLookupField(fieldNum(MyTmpTable, EmployerName),true);
    sysTableLookup.addLookupMethod(tableMethodStr(MyTmpTable, yourMethod));
    sysTableLookup.addLookupField(fieldNum(MyTmpTable, otherfieldtosee),false);
    //More field/methods...
    sysTableLookup.parmTmpBuffer(tmpTable);
    sysTableLookup.performFormLookup();
}

Now you can use this lookup at pleasure. The While Select can be rewritten to gain performance, but used this here to be clearer.

Answer 2

Make a view on table VendTable and field EmployerName and a count of RecId, then base the lookup on the view.

As shown below for CustTable and CustGroup: