Creating a simple lookup which shows data of 2 different tables

(2) Share

Report

Posted on by Superbunny

329

I wish to create a simple lookup which shows data from the tables EcoResCategory and EngChgProductCategoryDetails.

When I extract data from the dropdown with X++ after it value has been selected, I would like to get the RecId of the EngChgProductCategoryDetails.

To do this, I created the following code (based on multiple examples):

Query query = new Query();
QueryBuildDataSource qbds;
QueryBuildDataSource qbdsJoin;
SysTableLookup sysTableLookup = sysTableLookup::newParameters( tableNum(EngChgProductCategoryDetails), this);

qbds = query.addDataSource( tableNum(EngChgProductCategoryDetails));
qbdsJoin = qbds.addDataSource( tableNum(EcoResCategory));
qbdsJoin.relations( false);
qbdsJoin.fields().dynamic(NoYes::Yes);
qbdsJoin.addLink( fieldNum(EngChgProductCategoryDetails, Category), fieldNum(EcoResCategory, RecId));
qbdsJoin.joinMode(JoinMode::InnerJoin);

sysTableLookup.parmQuery(query);
sysTableLookup.addLookupfield( fieldNum(EngChgProductCategoryDetails, RecId), true);
sysTableLookup.addLookupfield( fieldNum(EcoResCategory, Name));
sysTableLookup.performFormLookup();

I now do get a dropdown with 2 columns. 1 has label 'RecId', showing the recids of the table EngChgProductCategoryDetails, which is correct.

It also returns me this value when I select a value, which is also what I want.

The other columns however has as label 'UNKNOWN' and does not show data.

So my question is: Why can't I see the name field of the EcoResCategory table in my dropdown fields?

P.S:

-I also tried using relations(true), but still no success

-I also tried removing 'qbdsJoin.fields().dynamic(NoYes::Yes);', but also no success

Categories:

Dynamics 365 Finance

I have the same question (0)

All responses (5)

Answers (1)

Suggested answer

Martin Dráb 239,029 Most Valuable Professional on at

Like (1)

Report
Copy link

Link copied!
Your code is wrong indeed.

You believe that when you use fieldNum(EcoResCategory, Name), the system knows that you want to select data from EcoResCategory table, but it's not the case. What fieldNum(EcoResCategory, Name) returns is just a number - the ID of Name field in EcoResCategory table.

Then you pass the value to sysTableLookup.addLookupField(). You created sysTableLookup object for EngChgProductCategoryDetails, therefore you're telling the system to add a field from EngChgProductCategoryDetails table that has the same ID as Name has in EcoResCategory table. That's clearly wrong. If such a field existed in EngChgProductCategoryDetails, you'd get a wrong field in the lookup, and you get UNKNOWN if the table doesn't have any field with this ID at all.

In your case, you can fix it easily by utilizing the existing name() display method of EngChgProductCategoryDetails table. For example:

sysTableLookup.addLookupMethod(tableMethodStr(EngChgProductCategoryDetails, name));

Was this reply helpful? Yes No
Superbunny 329 on at

Like (0)

Report
Copy link

Link copied!

Hello Martin,

Thank you for your quick response.

I have two questions/notes regarding your solution.

1. I tried it and, despite the label now showing correctly, the rows in the dropdown are still empty for this field

2. Im a bit confused about the answer. Are there not countless examples which work with fields of multiple tables?

Are these wrong then?

EXAMPLE:

https://community.dynamics.com/forums/thread/details/?threadid=558d5585-bfe8-4a01-8f16-52f8879410c0

Was this reply helpful? Yes No
Verified answer

Martin Dráb 239,029 Most Valuable Professional on at

Like (1)

Report
Copy link

Link copied!

1. You should use the debugger when code doesn't work as expected. I think you'll find that name() method can't find the category because you didn't include Category field in your query.

2. Yes, there are examples of other people making the same mistake as yourself. The thread there shows code that doesn't work and the suggested solution is using a view combining the two tables. That's a workaround of SysTableLookup not supporting data returned from multiple tables. Instread of returning fields from two tables, you return fields from a single view. But as I mentioned, you can simply use the method in your particular case.

Was this reply helpful? Yes No
Superbunny 329 on at

Like (0)

Report
Copy link

Link copied!

Hey Martin,

Thanks for the thorough explanation.

I could make it working by adding BOTH the field and the function to my lookup:

1. WORKING:

sysTableLookup.addLookupfield( fieldNum(EngChgProductCategoryDetails, RecId), true);
sysTableLookup.addLookupfield( fieldNum(EngChgProductCategoryDetails, Category));

sysTableLookup.addLookupMethod(tableMethodStr(EngChgProductCategoryDetails, name));

2. NOT WORKING: (name is empty)

sysTableLookup.addLookupfield( fieldNum(EngChgProductCategoryDetails, RecId), true);

sysTableLookup.addLookupMethod(tableMethodStr(EngChgProductCategoryDetails, name));

For both situations I did include the category table in my query. I literally copied the form controls and this is the only difference to get it working. In the WORKING case all 3 fields show data. In the not working case only the recid column shows data.

Now lets try removing the lookupMethod then since I see the same data twice:

3. ALSO WORKING:

sysTableLookup.addLookupfield( fieldNum(EngChgProductCategoryDetails, RecId), true);
sysTableLookup.addLookupfield( fieldNum(EngChgProductCategoryDetails, Category));

This is actually working, despite the category field being of type refrec pointing to the category...

So the following code is correctly showing me the category value, while the category table is NOT in the query and NOT in the lookup:

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

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

qbds = query.addDataSource( tableNum(EngChgProductCategoryDetails));
sysTableLookup.parmQuery(query);
sysTableLookup.addLookupfield( fieldNum(EngChgProductCategoryDetails, RecId), true);
sysTableLookup.addLookupfield( fieldNum(EngChgProductCategoryDetails, Category));
sysTableLookup.performFormLookup();

Is this simply because the compiler understands that when I mention a field of refrec type that he understands I want the data from the table it points to?

Either way, I will mark your answer as solution Martin, because I got it working. (and otherwise would via a view as well)

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

Like (1)

Report
Copy link

Link copied!

All right, you can return the Category field because the system creates a reference group control to replace RecId with a natural key and the replacement key of the table happen to be what you need. If it wasn't the case, you could use addSelectionField() to add the Category field to the query (needed for the display method).

Was this reply helpful? Yes No