Skip to main content

Code Examples

Practical X++ code patterns for building and executing queries at runtime using the Query and QueryRun classes.

Building from an AOT Query​

Query    query    = new Query(queryStr(SAMOCustTableQuery));
QueryRun queryRun = new QueryRun(query);

// Modify a range at runtime
QueryBuildDataSource qbds = query.dataSourceTable(tableNum(CustTable));
qbds.addRange(fieldNum(CustTable, CustGroup)).value('10');

while (queryRun.next())
{
    CustTable custTable = queryRun.get(tableNum(CustTable));
    info(strFmt("Customer: %1 — %2", custTable.AccountNum, custTable.name()));
}

Building a Query Entirely in Code​

Query                query = new Query();
QueryBuildDataSource qbds  = query.addDataSource(tableNum(SalesTable));

// Add a range
qbds.addRange(fieldNum(SalesTable, SalesStatus)).value(queryValue(SalesStatus::Invoiced));

// Add a join
QueryBuildDataSource qbdsLine = qbds.addDataSource(tableNum(SalesLine));
qbdsLine.joinMode(JoinMode::InnerJoin);
qbdsLine.relations(true);  // use AOT relations

// Add ordering
qbds.addOrderByField(fieldNum(SalesTable, CreatedDateTime), SortOrder::Descending);

QueryRun queryRun = new QueryRun(query);
while (queryRun.next())
{
    SalesTable salesTable = queryRun.get(tableNum(SalesTable));
    SalesLine  salesLine  = queryRun.get(tableNum(SalesLine));
    info(strFmt("%1 / %2: %3", salesTable.SalesId, salesLine.ItemId, salesLine.SalesQty));
}

Cross-Company Queries​

When AllowCrossCompany is Yes on the AOT query, or when called programmatically, the query retrieves data across all legal entities the user has access to.

Query query = new Query(queryStr(SAMOCrossCompanyQuery));
query.allowCrossCompany(true);

// Optionally restrict to specific companies
container companies = ['DAT', 'USMF'];
query.addCompanyRange(companies);

Dynamic Range Modification​

Query    query    = new Query(queryStr(SAMOSalesQuery));
QueryRun queryRun;

QueryBuildDataSource qbds = query.dataSourceTable(tableNum(SalesTable));

// Clear existing ranges and add new ones
qbds.clearRanges();
qbds.addRange(fieldNum(SalesTable, SalesStatus)).value(queryValue(SalesStatus::Open));
qbds.addRange(fieldNum(SalesTable, CreatedDateTime)).value(
    SysQuery::range(today() - 30, today()));

queryRun = new QueryRun(query);
while (queryRun.next())
{
    SalesTable salesTable = queryRun.get(tableNum(SalesTable));
    info(salesTable.SalesId);
}