Skip to main content

All about queries

The query object model contains classes to define and run a query. These objects are used to define the query data source, the fields returned, record ranges and relations to child data sources. The following illustration shows the object model.
The query components shown in the previous figure are system classes. The query classes are more visible when you create a dynamic query in code, but they are also used behind the scenes when you create a static query in the AOT.







System class
Description

QueryRun
Executes the query and fetches the data.
Query
The top level of the query definition. This class holds some properties itself and has one or more related data sources.
QueryBuildDataSource
Defines access to a single data source in the query. If more than one data source exists at the same level in a query, they result in separate SQL statements that are executed sequentially. If one data source exists as a child of another data source, a join is created between the two data sources.
QueryBuildFieldList
Defines which fields are returned from the database. The default is that the field list is dynamic, which returns all fields from the data source table, map, or view. Each data source has only one QueryBuildFieldList object, which contains information on all selected fields. It’s possible to specify aggregate functions like SUM, COUNT, and AVG on the field list object.
QueryBuildRange
Defines a subset of records returned based on a single field. A range is translated into a WHERE clause in the query SQL statement. If more than one field is used to limit the query (WHERE clause), the data source will contain more than one range.
QueryBuildDynalink
Contains information regarding a relation (limitation) to an external record. When the query is run, this information is converted to additional entries in the WHERE clause of the query SQL statement. Can only exist on the parent data source of a query. The function is used by forms, when two data sources are synchronized. Then the child data source will contain a dynalink or dynalinks to the parent data source. The function is used even if the two data sources are placed in two different forms but are still synchronized.
QueryBuildLink
Specifies the relation between the two data sources in the join. Can only exist on a child data source.

[Source for above text: Microsoft Dyanmics AX 4.0 Developer Guide]
Below is a job that can illustrate usage different query framework classes
static void queryCreation(Args _args)
{
Query query = new Query(‘MyQuery’);
QueryBuildDataSource custTableQBDS, custTransQBDS;
QueryBuildRange qbr1, qbr2;
QueryBuildFieldList qbFl, qbF2, qbF3;
QueryBuildLink qbl;
QueryBuildDynalink qbdl;
QueryRun qr;
CustTable custTable;
CustTrans custTrans;
LedgerTrans ledgerTrans;
;
custTableQBDS = query.addDataSource(tablenum(custTable));
//This will enable you to add the fields you like
//If dynamics is yes all the fields will be automatically added

custTableQBDS.fields().dynamic(NoYes::No);
qbFl = custTableQBDS.fields().addField(fieldNum(CustTable, AccountNum));
qbF2 = custTableQBDS.fields().addField(fieldNum(CustTable, InvoiceAccount));
qbF3 = custTableQBDS.fields().addField(fieldNum(CustTable, CustGroup));
//Adding Ranges
qbr1 = query.dataSourceTable(tableNum(CustTable)).addRange(fieldNum(CustTable, AccountNum));
qbr2 = query.dataSourceTable(tableNum(CustTable)).addRange(fieldNum(CustTable, InvoiceAccount));
qbr1.value(SysQuery::value(’4011′));
qbr2.value(SysQuery::value(’4010′));
//Add child data source to main datasource
custTransQBDS = custTableQBDS.addDataSource(tableNum(CustTrans));
//Two ways to add relation
//First – This may not work in all the scenarios
custTransQBDS.relations(true);
//Second – QueryBuildLink
qbl = custTransQBDS.addLink(fieldNum(CustTable, AccountNum), fieldNum(CustTrans, AccountNum));
SetPrefix (‘Queries’);
setPrefix (‘Dynalinks’);
qr = new QueryRun(query);
setPrefix (‘Without’);
while (qr.next())
{
if (qr.changed(tableNum(CustTable)))
{
custTable = qr.get(tableNum(CustTable));
}
setPrefix (‘Customer – ‘ + custTable.AccountNum);
if (qr.changed(tableNum(CustTrans)))
{
custTrans = qr.get(tableNum(CustTrans));
info(custTrans.Voucher);
}
}
//Dynalink test so run the loop after dynamically linking with a table select firstonly ledgerTrans where ledgerTrans.Voucher == ‘US_800006′ &&
ledgerTrans.AccountNum == ’130100′;
custTransQBDS.addDynalink(fieldNum(CustTrans, Voucher), ledgerTrans, fieldNum(LedgerTrans, Voucher));
custTransQBDS.addDynalink(fieldNum(CustTrans, TransDate), ledgerTrans, fieldNum(LedgerTrans, TransDate));
qr = new QueryRun(query);
setPrefix(‘Dynalinks’);
setPrefix (‘With’);
while (qr.next())
{
if (qr.changed(tableNum(CustTable)))
{
custTable = qr.get(tableNum(CustTable));
}
setPrefix (‘Customer – ‘ + custTable.AccountNum);
if (qr.changed(tableNum(CustTrans)))
{
custTrans = qr.get(tableNum(CustTrans));
info(custTrans.Voucher);
}
}
}

Popular posts from this blog

What does this mean: "The form datasource query object does not support changing its AllowCrossCompany property after the form has executed the query."?

I have made a form with datasources vendtable and vendtrans. Inside vendtable_ds.executequery() looks like this: QueryBuildDataSource queryBuildDatasource ,queryBDS_VendTrans_Invoice; ; queryBuildDatasource = this.query().dataSourceTable(tablenum(vendtable)); queryBDS_VendTrans_Invoice = this.query().dataSourceTable(tablenum(vendtrans)); if (curext() == "MASTERCOMP") { this.query().allowCrossCompany(true); } else { this.query().allowCrossCompany(false); } //FilterVendorName = stringedit control on form if (FilterVendorName.text()) { queryBuildDatasource.addRange(fieldNum(VendTable,Name)).value(strfmt("*%1*", FilterVendorName.text())); } else { queryBuildDatasource.clearRange(fieldNum(VendTable,Name)); } //FilterInvoiceNumber = stringedit control on form if (FilterInvoiceNumber.valueStr() == "") { queryBDS_VendTrans_Invoice.enabled(false); } else { queryBDS_VendTrans_Invoice.enabled(true); queryBDS_VendTrans_In...

Credit Note [Dynamics AX] using X++

This post will help to create credit note for a sales order based on the invent lot id. All the invoices raised for a particular sales line – Lot Id will be raised back as a credit note. Information on Credit Note: A credit note or credit memorandum (memo) is a commercial document issued by a seller to a buyer. The seller usually issues a Credit Memo for the same or lower amount than the invoice, and then repays the money to the buyer or sets it off against a balance due from other transactions Below Code will help to create credit note for all the invoices raised against the sales line -lot id. Please note: This code can be customized as per your requirements. This is just a template to help creating credit note using X++ code. Please test the code before use. static void SR_CreateCreditNote_Sales(Args _args) { // Coded by Sreenath Reddy CustInvoiceTrans custInvoiceTrans; Dialog dialog = new Dialog(“Create credit note – for sales.”); DialogField dfInv...