Solved

axapta query on multiple tables

Posted on 2012-03-30
1
3,665 Views
Last Modified: 2012-04-02
I'm trying to do a query using 3 tables in axapta.  I do the following:
QueryRun selectReportQuery()
{
    Query                   query           = new Query();
    QueryRun                localQueryRun;
    QueryBuildDataSource    qbds1;
    QueryBuildDataSource    qbds2;
    QueryBuildDataSource    qbds3;
    ;

    qbds1 = query.addDataSource(tableNum(salesTable));
    qbds1.addRange(fieldNum(salesTable, affVendorAdjust)).value('1');

    qbds2 = qbds1.addDataSource(tableNum(custInvoiceJour));
    qbds2.fetchMode(JoinMode::InnerJoin);
    qbds2.relations(false);
    qbds2.addLink(fieldNum(salesTable, salesId),fieldNum(custInvoiceJour, salesId));

    qbds2.addSortField(fieldNum(CustInvoiceJour, OrderAccount), SortOrder::Ascending);
    qbds2.addSortField(fieldNum(CustInvoiceJour, InvoiceId), SortOrder::Ascending);
    qbds2.addRange(fieldNum(CustInvoiceJour, InvoiceDate)).value(queryRange(invBeginDate, invEndDate));

    qbds3 = qbds1.addDataSource(tableNum(salesLine));
    qbds3.relations(true);
    qbds3.joinMode(JoinMode::InnerJoin);
    qbds3.fetchMode(queryFetchMode::One2Many);
qbds3.addRange(fieldNum(salesLine, mbsLineVendor)).value(queryValue(vendAccount));

when i run the query, it will use the search results from the 1st 2 tables, but will ignore the 3rd table.  What am i doing wrong, or is there another way to do this?
0
Comment
Question by:Cherylvanl
1 Comment
 
LVL 4

Accepted Solution

by:
agusacil earned 500 total points
ID: 37790629
You may need to restructure your query like this :
CustInvoiceJour
SalesTable
SalesLine

The reason is that AX may gives unpredicted query plan when one main table (SalesTable) is joined to more than one child data sources.
Rearrange that way will allow you fetch all Custinvoicejour linked to SalesTable that is linked to SalesLine with given criteria.

Hope that helps.

Agus
0

Featured Post

Revamp Your Training Process

Drastically shorten your training time with WalkMe's advanced online training solution that Guides your trainees to action.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

For cloud, the “train has left the station” and in the Microsoft ERP & CRM world, that means the next generation of enterprise software from Microsoft is here: Dynamics 365 is Microsoft’s new integrated business solution that unifies CRM and ERP fun…
Having trouble getting your hands on Dynamics 365 Field Service or Project Service trial? Worry No More!!!
Email security requires an ever evolving service that stays up to date with counter-evolving threats. The Email Laundry perform Research and Development to ensure their email security service evolves faster than cyber criminals. We apply our Threat…
Attackers love to prey on accounts that have privileges. Reducing privileged accounts and protecting privileged accounts therefore is paramount. Users, groups, and service accounts need to be protected to help protect the entire Active Directory …

756 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question