Joining multi tables in a query

(0) Share

Report

Posted on by maram fraij

I have the below X++ query and I need to convert it to query to be used in Lookup method for ProjID field , does the code in the method lookup below precisely correspond to the X++ query ?

while select ProjId from projTable join projGroup join ProjStatusTypeRule

where projTable.Header==false

&& projTable.ProjGroupId== projGroup.ProjGroupId

&& projGroup.AllowCreateCourse==true

&& projTable.Type== ProjStatusTypeRule.ProjType

&& projTable.Status== ProjStatusTypeRule.ProjStatus

&& projStatusTypeRule.ProjStatusRule==ProjStatusRule::CreateCourse

{

info(strFmt("%1",projTable.ProjId));

}

public void lookup()

{

SysTableLookup sysTableLookup = SysTableLookup::newParameters(tableNum(ProjTable), This);

Query query = new Query();

QueryBuildDataSource queryBuildDataSource,Qbds1, Qbds2;

QueryBuildRange queryBuildRange;

super();

queryBuildDataSource = query.addDataSource(tableNum(ProjTable));

queryBuildDataSource.addRange(fieldNum( ProjTable, Header)).value("=0");

Qbds1 = queryBuildDataSource.addDataSource(tableNum(ProjGroup));//

Qbds1.joinMode(JoinMode::InnerJoin);//

Qbds1.addLink(fieldNum(ProjTable, ProjGroupId), fieldNum(ProjGroup, ProjGroupId));//

Qbds1.addRange( fieldNum( ProjGroup, AllowCreateCourse)).value("=1");

Qbds2 = queryBuildDataSource.addDataSource(tableNum(projStatusTypeRule));//

Qbds2.joinMode(JoinMode::InnerJoin);//

Qbds2.addLink(fieldNum(ProjTable, Type), fieldNum(projStatusTypeRule, ProjType));//

Qbds2.addLink(fieldNum(ProjTable, Status), fieldNum(projStatusTypeRule, ProjStatus));//

Qbds2.addRange( fieldNum( projStatusTypeRule, ProjStatusRule)).value(queryValue(ProjStatusRule::CreateCourse));

sysTableLookup.parmQuery(query);

sysTableLookup.addLookupfield(fieldNum(ProjTable,ProjId));

sysTableLookup.addLookupfield(fieldNum(ProjTable,Name));

//sysTableLookup.addLookupfield(fieldNum(ProjTable,Status));

//sysTableLookup.addLookupfield(fieldNum(ProjTable,Header));

//sysTableLookup.addLookupfield(fieldNum(ProjGroup,ProjGroupId));

sysTableLookup.performFormLookup();

}

*This post is locked for comments

I have the same question (0)

All responses (6)

Answers (0)

Suggested answer

Denis Macchinetti 16,444 on at

Like (0)

Report

Hi Maram

In my opinion the query is correct.

In the Range condition, you can use QueryValue(X) instead "=0".

Was this reply helpful? Yes No
Suggested answer

Community Member on at

Like (0)

Report

As per my understanding Query is good enough,

as per coding best practice you should remove range value "=0" and "=1"

to QueryValue(NoYes::No) and QueryValue(NoYes::Yes) which is equivalent as per your requirement

One thing what i think is the issue is you should not have statement

super();

Line that calls super need to be commented out.

one of the sample code which works fine is as below

public void lookup()

{

SysTableLookup sysTableLookup = SysTableLookup::newParameters(tablenum(AssetDepBookJournalName), this);

Query query = new Query();

QueryBuildDataSource queryBuildDataSource = query.addDataSource(tablenum(AssetDepBookJournalName));

;

sysTableLookup.addLookupfield(fieldnum(AssetDepBookJournalName, JournalNameId));

sysTableLookup.addLookupfield(fieldnum(AssetDepBookJournalName, Description));

sysTableLookup.parmQuery(query);

sysTableLookup.performFormLookup();

}

Please verify. and update with your findings

Was this reply helpful? Yes No
maram fraij on at

Like (0)

Report

Why should the super() method be commented out ?

Was this reply helpful? Yes No
Suggested answer

Community Member on at

Like (0)

Report

As we are calling

sysTableLookup.performFormLookup();

so super() is not required...

please comment it out and verify...

Was this reply helpful? Yes No
Denis Macchinetti 16,444 on at

Like (0)

Report

Hi Maram

"Super" would call the standard lookup method.

However, you can leave without issue because your custom lookup will have priority.

Was this reply helpful? Yes No
Suggested answer

Hossein.K 6,648 on at

Like (0)

Report

Hi,

look at this:

community.dynamics.com/.../multi-table-lookups-sysmultitablelookup

Was this reply helpful? Yes No