Custom Lookup by joining InventSum and InventDim

(0) Share

Report

Posted on by martinarul

223

Dear Friends,

I am trying to create custom lookup in ax 2009 by joining InventSum and InventDim Tables to show up InventBatchId in Cross Company. The code that I used is given below. It is not showing the lookup at all. When I executed the same query in job I am getting the expected results. Please suggest me where I have made mistake. Your help is much appreciated.

The same query works fine for a single table, but while joining tables something goes wrong.

public void lookup()
{

Query query = new Query();
QueryBuildDataSource qbds1,qbds2;
SysTableLookup sysTableLookup;
QueryBuildRange qbr1,qbr2,qbr3;
//super();
;

query.allowCrossCompany(true);
query.addCompanyRange(StringEditCompany.valueStr());

qbds1   = query.addDataSource(tableNum(InventSum));
qbr1    = qbds1.addRange(fieldNum(InventSum,Itemid));
qbr1.value("1234");

qbds2   = qbds1.addDataSource(tableNum(InventDim));
qbds2.relations(true);
qbds2.fields().dynamic(NoYes::Yes);
qbr2    = qbds2.addRange(fieldNum(InventDim,InventLocationId));
qbr2.value("WH1");
qbr3   = qbds2.addRange(fieldNum(InventDim,WMSLocationId));
qbr3.value("PICK");

sysTableLookup = SysTableLookup::newParameters(tablenum(InventDim), this);
sysTableLookup.parmQuery(query);
sysTableLookup.addLookupfield(fieldnum(InventDim, InventBatchid), true);
sysTableLookup.parmUseLookupValue(true);
sysTableLookup.performFormLookup();
}

*This post is locked for comments

I have the same question (0)

All responses (9)

Answers (1)

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

Like (0)

Report

The first check - did you verify in debugger that the method is called at all?

Was this reply helpful? Yes No
martinarul 223 on at

Like (0)

Report

Hi Martin,

I checked, the method gets called in the debugger.

Regards

Martin. A

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

Like (0)

Report

Can you describe the exact behavior, please? Are you saying that no drop-down gets open at all, or that it's empty?

I suspect the problem is in the fact that InventDim isn't the root datasource of your query.

Was this reply helpful? Yes No
martinarul 223 on at

Like (0)

Report

Hi Martin,

You are exactly correct. The drop-down is empty. I also suspect that the InventDim is not the root datasource and that could be the reason.

Can you suggest me how to keep InventDim as root datasource and build the query.

Was this reply helpful? Yes No
Verified answer

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

Like (0)

Report
Simply change the order of datasources in your query. Like this:

Query query = new Query(); QueryBuildDataSource dimDs, sumDs; dimDs = query.addDataSource(tableNum(InventDim)); dimDs.addRange(fieldNum(InventDim, InventLocationId)).value("WH1"); dimDs.addRange(fieldNum(InventDim,WMSLocationId)).value("PICK"); sumDs = dimDs.addDataSource(tableNum(InventSum)); sumDs.relations(true); sumDs.joinMode(JoinMode::ExistJoin); sumDs.addRange(fieldNum(InventSum, ItemId)).value("1234");

Was this reply helpful? Yes No
martinarul 223 on at

Like (0)

Report

Thanks a lot Martin. Now it works fine.

Was this reply helpful? Yes No
martinarul 223 on at

Like (0)

Report

Hi Martin,

I have another doubt, from the query you suggested. I want to add two more lookup fields ( one is field and the other is display method) from the joined table (InventSum)?

Regards

Martin. A

Was this reply helpful? Yes No
Suggested answer

Shah Keyur 365 on at

Like (0)

Report

You should create a custome view joining those tables. You can add your display method as field in view and use that view for your lookup method.

This will also improve your performance of lookup query.

Was this reply helpful? Yes No
Suggested answer

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

Like (0)

Report

A view is one option, SysMultiTableLookup is another.

Was this reply helpful? Yes No

Community site session details

Custom Lookup by joining InventSum and InventDim

Helpful resources

Quick Links

Responsible AI policies

Neeraj Kumar – Community Spotlight

Congratulations to the November Top 10 Community Leaders!

Leaderboard > 🔒一 Microsoft Dynamics AX (Archived)

Featured topics

Product updates

Community site session details

Custom Lookup by joining InventSum and InventDim

Helpful resources

Quick Links

Responsible AI policies

Neeraj Kumar – Community Spotlight

Congratulations to the November Top 10 Community Leaders!

Subscribe to this forum!

Select categories

Leaderboard > 🔒一 Microsoft Dynamics AX (Archived)

Featured topics

Product updates