Display lookup field from 2 tables in a form

Finance and Operations/AX Developer Forum
1

I am working with AX 2009 version, and while designing the form wanted to display a lookup with 2 fields(1 from Employee table and 1 from DirPartyTable). SysMultiTableLookup doesn’t work in AX 2009, so any leads on different ways I can do that?

Output should be like:

When i click on the field on form, the lookup must display Employee Id(EmplTable) and its name(from DirPartyTable).

Also on selecting a particular record in lookup it must return the name and not the Employee Id.

Thanks,

RockKato

2

You’ll have to tell us what problem you have with SysMultiTableLookup - there is no universal solution for “it doesn’t work”. Where did you download from? AX 2009 is supported and I use SysMultiTableLookup there quite a few times (although it’s years age when I saw AX 2009 for the last time).

3

I hope you can join those two tables using SystableLookup only.
And if you want to return the name, you need to write

systablelookup.addselectionfield(fieldnum(DirPartytable,Name));

4

Lalit, your own code demonstrates that the standard SysTableLookup doesn’t allow you to define the table to use. If systablelookup is based on EmplTable, your code will add a field from EmplTable that has the same field ID as the Name field in DirParty table, which clearly isn’t what you intended.

A workaround would be using a view, but SysMultiTableLookup definitely work in AX 2009, so the workaround isn’t necessary.

5

Hi Martin,

When I am using the below code and compiling it , I am getting the issue:
“Variable SysMultiTableLookup” has not been declared".

static void RockyJob93(Args _args)
{
Query query;
QueryBuildDataSource queryBuildDataSourceTable;
QueryBuildDataSource queryBuildDataSourceTableDescriptions;
QueryBuildRange queryBuildRange;
SysMultiTableLookup sysTableLookup;

;
query = new Query();

queryBuildDataSourceTable = query.addDataSource(tableNum(UnitOfMeasure));
queryBuildDataSourceTableDescriptions = queryBuildDataSourceTable.addDataSource(tableNum(UnitOfMeasureTranslation));
queryBuildDataSourceTableDescriptions.joinMode(JoinMode::InnerJoin);
queryBuildDataSourceTableDescriptions.relations(false);
queryBuildDataSourceTableDescriptions.addLink(fieldNum(UnitOfMeasure,RecId),fieldNum(UnitOfMeasureTranslation,UnitOfMeasure));

queryBuildRange = queryBuildDataSourceTable.addRange(fieldNum(UnitOfMeasure, UnitOfMeasureClass));
queryBuildRange.value(queryValue(unitClass));
sysTableLookup = SysMultiTableLookup::newParameters(ctrl, query);

sysTableLookup.addLookupfield(fieldNum(UnitOfMeasure, Symbol), true);
sysTableLookup.addLookupfield(fieldNum(UnitOfMeasureTranslation, Description), 2);

sysTableLookup.performFormLookup();
}

Could you suggest me if there is any issue with the code?

Thanks

6

SysMultiTableLookup class isn’t a part of the standard application; it was developed by a developer outside Microsoft and published as open source. Download it, import, compile and try your code again.

7

By the way, your code looks like copied from AX 2012 and won’t work in AX 2009. If I remember it correctly, AX 2009 uses Unit table instead of UnitOfMeasure.

8

Yes, the above code i have implemented in 2012 R3

9

Hi rockkato,

Here is another way to combine multiple data sources in a sysTableLookup.

First of all you create view that contains your all data sources. Then set relation of that data sources and finally add the required fields to the view from these data sources.

And then perform sysTableLookup on the view. Refer blow code,

public void lookup()
{
Query query = new Query();
QueryBuildDataSource qbds;
QueryBuildDataSource qbdsJoin;
SysTableLookup sysTableLookup = sysTableLookup::newParameters( tableNum(ViewName), this);
;

qbds= query.addDataSource( tableNum(ViewName));

sysTableLookup.parmQuery(query);
sysTableLookup.addLookupfield( fieldNum(ViewName, FieldName), true);
sysTableLookup.addLookupfield( fieldNum(ViewName, FieldName), true);
sysTableLookup.performFormLookup();

}