Multiple Tables In Query
Axapta query on multiple tables
Hey friends try this code to add multiple tables in query.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));
Here is some more code related to query in axapta
1. Sample union query from AX 2009
2. Building a query object
3. How to create a Query(dynamically)
4. Join Two Tables at Run Time