Create lookup based on values in 4 tables

Finance and Operations/AX Developer Forum
1

Hello, friends

I need to create lookup which finds finds range of values combined from 4 tables. The code succesfully works with two tables but as I add the third one it doesnt work anymore. Would be nice if you could help me on this.

     public void lookup()
        {
            Query query;
            QueryBuildDataSource datasourceSalesTable,datasourceSalesLine, datasourceInventDim, datasourceAll;
            SysTableLookup sysTableLookup;
            query = new Query();

            super();

            datasourceSalesLine = query.addDataSource(tableNum(EBGrainItemMapping));
            datasourceAll = datasourceSalesLine.addDataSource(tableNum(SalesLine));
            datasourceAll.joinMode(JoinMode::InnerJoin);
            //datasourceAll.fetchMode(QueryFetchMode::One2One);
            datasourceAll.relations(true);
            datasourceAll.addLink(fieldnum(EBGrainItemMapping, OffbalanceItem), (fieldNum(SalesLine, ItemId)));
            datasourceSalesTable = datasourceAll.addDataSource(tableNum(SalesTable));
            datasourceSalesTable.joinMode(JoinMode::InnerJoin);
            //datasourceSalesTable.fetchMode(QueryFetchMode::One2One);
            datasourceSalesTable.relations(true);
            datasourceSalesTable.addLink(fieldNum(SalesLine, SalesId), (fieldnum(SalesTable, SalesId)));

            //datasourceSalesTable.addRange(fieldnum(SalesTable, CustAccount)).value(CustomerAccount.valueStr());
            //datasourceSalesTable.addRange(fieldnum(SalesTable, InventSiteId)).value(InventSiteId.valueStr());

            datasourceInventDim = datasourceSalesTable.addDataSource(tableNum(InventDim));
            datasourceInventDim.joinMode(JoinMode::InnerJoin);
            //datasourceInventDim.fetchMode(QueryFetchMode::One2One);
            datasourceInventDim.relations(true);
            datasourceInventDim.addLink(fieldNum(SalesTable, InventSiteId),fieldnum(InventDim, InventSiteId));
            
            sysTableLookup = SysTableLookup::newParameters(tableNum(SalesTable), this);
            sysTableLookup.parmQuery(query);

            sysTableLookup.addLookupfield(fieldNum(SalesTable, SalesId));
            //sysTableLookup.addLookupfield(fieldNum(SalesLine, ItemId));
            //sysTableLookup.addLookupfield(fieldNum(SalesLine, Name));

			//sysTableLookup.addLookupfield(fieldNum(InventDim, configId));
            //sysTableLookup.addLookupfield(fieldNum(InventDim, InventBatchId));

            
            sysTableLookup.performFormLookup();
            //super();

Best regards,

Roberts

2

Your code supports a single table only.

For example, this line:

sysTableLookup.addLookupfield(fieldNum(InventDim, configId));

means this: Add a field from SalesTable that has the same ID as ConfigId field has in InventDim table. It’s clearly not what you intended.

One of the possible solutions is creating a view with those four tables and using this view as the source for SysTableLookup.

3

Thanks, Martin

At the end I created two additional display methods in SalesLines table which return configId and InventBatchId and added them through sysTableLookup.addLookupMethod.

Best regards,

Roberts