Finance | Project Operations, Human Resources, ...

Multi table custom lookup

(0) Share

Report

Posted on by Community Member

Hi Experts,

I need your help, I will need to create custom lookup - Contact person id and Name.

I am able to create "Contact person id" lookup but for "Name" field we need another table "DirPartyTable".

How we use two table for creating lookup?

I gone through some blog, they have mentioned "SysMultiTableLookup" but I am not able to get it "SysMultiTableLookup" class.

Can you please suggest clue.

query = new Query();
contactPersonQBDS = query.addDataSource(tableNum(ContactPerson));
contactPersonQBDS.addRange(fieldNum(ContactPerson, ContactForParty)).value(strFmt("%1",vendTable.Party));

QbdsDirPartyTable = contactPersonQBDS.addDataSource(tablenum(DirPartyTable));

--QbdsDirPartyTable.joinMode(JoinMode::InnerJoin);
--QbdsDirPartyTable.relations(true);
--QbdsDirPartyTable.addLink(fieldNum(ContactPerson,party), fieldNum(DirPartyTable, RecId));

sysTableLookup = SysTableLookup::newParameters(tableNum(ContactPerson), sender);
sysTableLookup.addLookupField(fieldNum(ContactPerson, ContactPersonId));
--sysTableLookup.addLookupField(fieldNum(ContactPerson, Name));
sysTableLookup.parmQuery(query);
sysTableLookup.performFormLookup();

I have the same question (0)

All responses (6)

Answers (0)

Adria Ariste User Group Leader on at

Like (0)

Report

If you want to add a field from another table to the lookup just change sysTableLookup.addLookupField(fieldNum(ContactPerson, Name)); to sysTableLookup.addLookupField(fieldNum(DirPartyTable, Name)); which is the field you need. The rest of your code should be fine (are you using -- to comment the code? In X++ we use // for single line and /* */ for multiline comments)

Regards,

Adrià Ariste - Senior technical consultant @ Axazure - Read my blog!

Was this reply helpful? Yes No
Community Member on at

Like (0)

Report

Thanks Adrià.

I tried with below code but second field in lookup (that is Name) display null value:

query = new Query();
contactPersonQBDS = query.addDataSource(tableNum(ContactPerson));
contactPersonQBDS.addRange(fieldNum(ContactPerson, ContactForParty)).value(strFmt("%1",vendTable.Party));

QbdsDirPartyTable = contactPersonQBDS.addDataSource(tablenum(DirPartyTable));

QbdsDirPartyTable.joinMode(JoinMode::InnerJoin);
QbdsDirPartyTable.relations(true);
QbdsDirPartyTable.addLink(fieldNum(ContactPerson,party), fieldNum(DirPartyTable, RecId));

sysTableLookup = SysTableLookup::newParameters(tableNum(ContactPerson), sender);
sysTableLookup.addLookupField(fieldNum(ContactPerson, ContactPersonId));
sysTableLookup.addLookupField(fieldNum(DirPartyTable, Name));
sysTableLookup.parmQuery(query);
sysTableLookup.performFormLookup();

Was this reply helpful? Yes No
Suggested answer

Martin Dráb 237,980 Most Valuable Professional on at

Like (0)

Report

The answer above is wrong - sysTableLookup.addLookupField(fieldNum(DirPartyTable, Name)) will select a field from ContactPerson table which has the same ID as Name field has in DirPartyTable, which doesn't make a good sense.

The easiest way may be using a display method - take a look where ContantPerson already doesn't have one. Then you'd use addLookupMethod() instead of addLookField().

SysMultiTableLoookup isn't a standard class. You can get it here.

Yet another solution is building a view and using the view with all fields you need and then using this view in your lookup, as if all fields were in a single table.

Was this reply helpful? Yes No
Community Member on at

Like (0)

Report

Have you a problem solved?

If not then I have a solution

Step -1: Create a query then create a view

Step - 2: If there is a reference control then you need to create a form lookup then add some piece of code

Reply to me then I have a code to use for multi-table customer lookup.

Was this reply helpful? Yes No
ANPavel 5 on at

Like (0)

Report

Hello, can you help me with the solution using a view in lookup? I belive this is the only solution in d365 for multiple table lookup. Thank you.

Was this reply helpful? Yes No
Martin Dráb 237,980 Most Valuable Professional on at

Like (0)

Report

ANPavel, we can't help you unless you explain your problem.

Also, you're wrong in thinking that the other two solutions can't be used in F&O. Display methods works the same as before (at least since Axapta 3.0) and SysMultiTableLoookup can be used too, you just need to install a version for F&O (e.g. from here).

Was this reply helpful? Yes No