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();

enter image description here enter image descrip![enter image description here tion here]2

possible duplicate of [Custom Lookup and Group By](http://stackoverflow.com/questions/11453756/custom-lookup-and-group-by) — Jan B. Kjeldsen, Mar 11 '14 at 09:06
this is not duplicate. I am facing the problem while clicking on the "Employee Name" marked in red area above. — piku, Mar 11 '14 at 09:24

Jan B. Kjeldsen · Accepted Answer · 2014-03-12T07:38:51.453

1

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:

enter image description here

edited Mar 12 '14 at 07:38

answered Mar 11 '14 at 09:43

Jan B. Kjeldsen

17,817
5
32
50

I have created a view and added the fields in it `qbds = query.addDataSource(tablenum(VendTableEmplViewLookup)); qbds.addGroupByField(fieldnum(VendTableEmplViewLookup,EmployerName)); sysTableLookup.addLookupfield(fieldnum(VendTableEmplViewLookup, EmployerName));` but when i click on the lookup btn nothing happens, debugging i see that the query is rightly constructed even running a job on the View results correctly. – piku Mar 12 '14 at 06:44
Answer updated. There is no need for `qbds.addGroupByField` if the grouping is done in the view. – Jan B. Kjeldsen Mar 12 '14 at 07:56
awesome! thanks for your promptness.. VIEW is nice :) solves my problem. But to the thread i raised can this be considered as a solution? coz its a workaround... – piku Mar 12 '14 at 09:12
We may assume there is a bug in `SysTableLookup`, which is neither your or my problem. So if I solved your problem, consider accepting the answer. – Jan B. Kjeldsen Mar 12 '14 at 11:56

score 1 · Answer 2 · answered Mar 14 '14 at 12:25

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.

Custom lookup select issue

2 Answers2

Linked