Skip to main content

Notifications

Announcements

No record found.

Microsoft Dynamics AX (Archived)

Joining multi tables in a query

Posted on by 11,873

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

  • Suggested answer
    Hossein.K Profile Picture
    Hossein.K 6,642 on at
    RE: Joining multi tables in a query

    Hi,

    look at this:

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

  • Denis Macchinetti Profile Picture
    Denis Macchinetti 16,444 on at
    RE: Joining multi tables in a query

    Hi Maram

    "Super" would call the standard lookup method.

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

  • Suggested answer
    Community Member Profile Picture
    Community Member Microsoft Employee on at
    RE: Joining multi tables in a query

    As we are calling

    sysTableLookup.performFormLookup();

    so super() is not required...

    please comment it out and verify...

  • maram fraij Profile Picture
    maram fraij 11,873 on at
    RE: Joining multi tables in a query

    Why should the super() method be commented out ?

  • Suggested answer
    Community Member Profile Picture
    Community Member Microsoft Employee on at
    RE: Joining multi tables in a query

    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

  • Suggested answer
    Denis Macchinetti Profile Picture
    Denis Macchinetti 16,444 on at
    RE: Joining multi tables in a query

    Hi Maram

    In my opinion the query is correct.

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

Under review

Thank you for your reply! To ensure a great experience for everyone, your content is awaiting approval by our Community Managers. Please check back later.

Helpful resources

Quick Links

December Spotlight Star - Muhammad Affan

Congratulations to a top community star!

Top 10 leaders for November!

Congratulations to our November super stars!

Tips for Writing Effective Suggested Answers

Best practices for providing successful forum answers ✍️

Leaderboard

#1
André Arnaud de Calavon Profile Picture

André Arnaud de Cal... 291,269 Super User 2024 Season 2

#2
Martin Dráb Profile Picture

Martin Dráb 230,198 Most Valuable Professional

#3
nmaenpaa Profile Picture

nmaenpaa 101,156

Leaderboard

Featured topics

Product updates

Dynamics 365 release plans