How to create a Query(dynamically)

How to create a Query(dynamically) and add a link in Axapta

Hi friends,

     Today we are trying to join two tables dynamically and adding a  link type between the two tables. Open the Aot and in jobs write the following code

static void CustTableSales1(Args _args)
{
    Query       query;
    QueryRun    queryrun;
    QueryBuildDataSource    qbds1;
    QueryBuildDataSource    qbds2;
    QueryBuildRange         qbr1;
    QueryBuildRange         qbr2;
    CustTable               custTable;
    ;
    query   = new query();
    qbds1   =   query.addDataSource(tablenum(CustTable));
    qbds1.addSortField(fieldnum(custTable,AccountNum),Sortorder::Descending);
    qbr1    = qbds1.addRange(fieldnum(custTable,custGroup));
    qbr1.value(queryvalue('10'));
    qbr2    =  qbds1.addRange(fieldnum(custTable,Blocked));
    qbr2.value(queryvalue(CustVendorBlocked::No));
    qbds2   = qbds1.addDataSource(tablenum(SalesTable));
    qbds2.relations(false);
    qbds2.joinMode(joinmode::ExistsJoin);
    qbds2.addLink(fieldnum(CustTable,AccountNum),fieldnum(SalesTable,CustAccount));
    queryrun    = new queryrun(query);
    while(queryrun.next())
    {
    custTable   = queryrun.get(tablenum(custTable));
    info(strfmt("%1 - %2",custtable.AccountNum,custTable.Name)); // to check your result
    }
}

Sample union query from AX 2009

Sample union query from AX 2009

Hi friends;

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()));
    }
}

- Harry