Pages

Friday, July 11, 2008

Sample union query from AX 2009

Queries build with the Query classes now supports unions, meaning that you can combine the result from several tables into one result set. The results you want to combine from the different tables must be structured the same way for all tables.

You could for example create a query combining CustTable and VendTable. This would be particularly useful if you need to present for example a lookup form showing both customers and vendors in the same grid. In earlier version you’d have to push customer and vendor data to a temporary table before being able to present the combined data in one grid.

Here is an example on how to build and use a union query from X++:

static void union(Args _args)
{
Query query;
QueryBuildDataSource qbdsCustTable;
QueryBuildDataSource qbdsVendTable;
QueryRun queryRun;
CustTable custVendTable;
Map mapTableBranches = new Map(types::Integer, typeId2Type(typeId(TableId)));
SysDictTable dictTable;
;



// The map is used to match the UnionBranchID with a table id
mapTableBranches.insert(1, tableNum(CustTable));
mapTableBranches.insert(2, tableNum(VendTable));


query = new Query();
query.queryType(QueryType::Union);


qbdsCustTable = query.addDataSource(tableNum(CustTable));
qbdsCustTable.unionType(UnionType::UnionAll); // Include duplicate records
qbdsCustTable.fields().dynamic(false);
qbdsCustTable.fields().clearFieldList();
qbdsCustTable.fields().addField(fieldNum(CustTable, AccountNum));
qbdsCustTable.fields().addField(fieldNum(CustTable, Name));


qbdsVendTable = query.addDataSource(tableNum(Vendtable));
qbdsVendTable.unionType(UnionType::UnionAll); // Include duplicate records
qbdsVendTable.fields().dynamic(false);
qbdsVendTable.fields().clearFieldList();
qbdsVendTable.fields().addField(fieldNum(VendTable, AccountNum));
qbdsVendTable.fields().addField(fieldNum(VendTable, Name));


queryRun = new QueryRun(query);
queryRun.prompt();

while (queryRun.next())
{
custVendTable = queryRun.getNo(1);
dictTable = SysDictTable::newTableId(mapTableBranches.lookup(custVendTable.unionAllBranchId));

info (strFmt("%1 %2 (%3)", custVendTable.AccountNum,
custVendTable.Name,
dictTable.name()));
}
}