Multi Table Lookup fields

(0) Share

Report

Posted on by Prabakaran.R

313

I want lookup for Item ID with Item Id and NameAlias coming from Invent table and Name from EcoResProductTranslation in lookup.

Here is my lookup method but Name is not coming.

Query query = new Query();
QueryBuildDataSource qbds;
QueryBuildDataSource QbdsJoin;

SysTableLookup sysTableLookup = sysTableLookup::newParameters(tableNum(InventTable), this);
;

qbds = query.addDataSource(tableNum(InventTable));
QbdsJoin = qbds.addDataSource(tableNum(EcoResProductTranslation));
QbdsJoin.joinMode(JoinMode::InnerJoin);
QbdsJoin.fetchMode(QueryFetchMode::One2Many);
QbdsJoin.relations(true);
QbdsJoin.addLink(fieldNum(InventTable, Product), fieldNum(EcoResProductTranslation, Product));
sysTableLookup.parmQuery(query);
sysTableLookup.addLookupfield(fieldNum(InventTable, ItemId));
sysTableLookup.addLookupfield(fieldNum(EcoResProductTranslation,Name));
sysTableLookup.addLookupfield(fieldNum(InventTable, NameAlias));
sysTableLookup.performFormLookup();

*This post is locked for comments

I have the same question (0)

All responses (12)

Answers (0)

Sohaib Cheema 49,438 User Group Leader on at

Like (0)

Report

it may be because InventTable has got no direct relationship with EcoResProductTranslation.

you may need to include EcoResProduct in between two tables.

please note that EcoResProductTranslation can have multiple records for same product based on different languages, so be careful or you might add a range to filter for a specific language such as EN-US or EN-UK or whatever language is of your region. also you can take language from user options of current user.

Was this reply helpful? Yes No
Suggested answer

B K Sharma 737 on at

Like (0)

Report

Dear Prabakaran,

There is not possible to use multi table field in custom lookup method, but you can use these fields in lookup using view, just create a view and use it as datasource in the lookup method.

Also request you to please complete the thread in this group started by you, either you got solution by your own or you got solution from anywhere, pls mark all this in your thread which was initialized by you, it will become helpful for other user who got same type of problems in there development.

Thanks

Was this reply helpful? Yes No
Suggested answer

B K Sharma 737 on at

Like (0)

Report

follow link to get exact solution you want step by step.

https://community.dynamics.com/ax/b/daxbeginners/archive/2014/05/20/how-to-create-form-lookup-lookup-multiple-tables

Thanks

Was this reply helpful? Yes No
Prabakaran.R 313 on at

Like (0)

Report

Hi Crispin John Augustine ,

I gave the inner join between InventTable--EcoResProduct-EcoResProductTranslation but still the Name from EcoResProductTranslation is not coming lookup.

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

Qbds1 = qbds.addDataSource(tableNum(EcoResProduct));

Qbds2 = qbds1.addDataSource(tableNum(EcoResProductTranslation));

Qbds1.joinMode(JoinMode::InnerJoin);

//Qbds1.fetchMode(QueryFetchMode::One2Many);

Qbds1.relations(true);

Qbds2.joinMode(JoinMode::InnerJoin);

Qbds2.relations(true);

Qbds1.addLink(fieldNum(InventTable, Product), fieldNum(EcoResProduct, RecId));

Qbds2.addLink(fieldNum(EcoResProduct, RecId), fieldNum(EcoResProductTranslation, Product));

sysTableLookup.parmQuery(query);

sysTableLookup.addLookupfield(fieldNum(InventTable, ItemId));

sysTableLookup.addSelectionField(fieldNum(EcoResProductTranslation,Name));

sysTableLookup.addLookupfield(fieldNum(InventTable, NameAlias));

sysTableLookup.performFormLookup();

Was this reply helpful? Yes No
Suggested answer

Chaitanya Golla 17,225 on at

Like (0)

Report
Please use the following code

qbds = query.addDataSource(tableNum(InventTable)); Qbds1 = qbds.addDataSource(tableNum(EcoResProduct)); Qbds1.addLink(fieldNum(InventTable, Product), fieldNum(EcoResProduct, RecId)); Qbds1.joinMode(JoinMode::InnerJoin); Qbds1.relations(true); Qbds2 = qbds1.addDataSource(tableNum(EcoResProductTranslation)); Qbds2.addLink(fieldNum(EcoResProduct, RecId), fieldNum(EcoResProductTranslation, Product)); //Qbds1.fetchMode(QueryFetchMode::One2Many); Qbds2.joinMode(JoinMode::InnerJoin); Qbds2.relations(true); sysTableLookup.parmQuery(query); sysTableLookup.addLookupfield(fieldNum(InventTable, ItemId)); sysTableLookup.addSelectionField(fieldNum(EcoResProductTranslation,Name)); sysTableLookup.addLookupfield(fieldNum(InventTable, NameAlias)); sysTableLookup.performFormLookup();

Was this reply helpful? Yes No
Prabakaran.R 313 on at

Like (0)

Report

Hi Chaitanya Golla,

I tried with this code, but still the Name from EcoResProductTranslation is not coming in lookup.

Was this reply helpful? Yes No
Suggested answer

Chaitanya Golla 17,225 on at

Like (0)

Report
Hi,

I tested your code in a job.Let me check it on a form lookup. Please replace your code with below statements and try once.

sysTableLookup.addLookupfield(fieldNum(InventTable, ItemId)); sysTableLookup.addLookupfield(fieldNum(EcoResProductTranslation,Name), true); //sysTableLookup.addSelectionField(fieldNum(EcoResProductTranslation,Name)); sysTableLookup.addLookupfield(fieldNum(InventTable, NameAlias));
sysTableLookup.parmQuery(query);
sysTableLookup.performFormLookup();

Was this reply helpful? Yes No
Prabakaran.R 313 on at

Like (0)

Report

Hi Chaitanya Golla ,

I tried with your code, instead of getting item name, I 'm getting item type in look up.

NOTE: I didn't mapped item type in lookup.

Was this reply helpful? Yes No
Suggested answer

Chaitanya Golla 17,225 on at

Like (0)

Report

Hi,

I could see a similar requirement at the below link and there is a suggestion to use SysMultiTableLookupClass

https://dynamicsuser.net/ax/f/developers/66365/ax2012-r2-runtime-lookup-with-multiple-table

Was this reply helpful? Yes No