Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

axapta query on multiple tables

Posted on 2012-03-30
1
Medium Priority
?
3,732 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
1 Comment
 
LVL 4

Accepted Solution

by:
agusacil earned 2000 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

Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

This is a walkthrough guide I wrote whilst upgrading my on-premise MS Dynamics CRM 3.0 deployment to 4.0. This covers the actual installation of the product to a working level for my system, I ran into a lot of issues that the steps below fixed so h…
Desired Skill Set for Microsoft Dynamics CRM Technical Resources – Part II
In this brief tutorial Pawel from AdRem Software explains how you can quickly find out which services are running on your network, or what are the IP addresses of servers responsible for each service. Software used is freeware NetCrunch Tools (https…
In response to a need for security and privacy, and to continue fostering an environment members can turn to for support, solutions, and education, Experts Exchange has created anonymous question capabilities. This new feature is available to our Pr…

688 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