Solved

axapta query on multiple tables

Posted on 2012-03-30
1
3,643 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

ScreenConnect 6.0 Free Trial

Explore all the enhancements in one game-changing release, ScreenConnect 6.0, based on partner feedback. New features include a redesigned UI, app configurations and chat acknowledgement to improve customer engagement!

Question has a verified solution.

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

Automatically creating a Trello card using data from a Microsoft Dynamics CRM record turned out to be an easy project that yielded great results.  Here's how I did this for an internal team at General Code.
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…
This Micro Tutorial will teach you how to censor certain areas of your screen. The example in this video will show a little boy's face being blurred. This will be demonstrated using Adobe Premiere Pro CS6.
This Micro Tutorial demonstrates using Microsoft Excel pivot tables, how to reverse engineer competitors' marketing strategies through backlinks.

770 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