July 17, 2012

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

4 comments:

  1. Im using above example for getting an union output. I have use salesTable for both querybuilddatasources (with different ranges). Problem is there are some duplicate Sales orders. I want to get a grouped set of sales order. plz help. my example is as follows.

    static void UnionTestJob(Args _args)
    {

    Query query;
    QueryBuildDataSource qbdsSalesTable1;
    QueryBuildDataSource qbdsSalesTable2;
    QueryRun queryRun;
    SalesTable SalesTable;
    ;

    query = new Query();
    query.queryType(QueryType::Union);
    qbdsSalesTable1 = query.addDataSource(tableNum(SalesTable));
    qbdsSalesTable1.addRange(fieldnum(Salestable, SalesId)).value('SO_0000001');
    qbdsSalesTable1.unionType(UnionType::UnionAll);

    qbdsSalesTable2 = query.addDataSource(tableNum(SalesTable));
    qbdsSalesTable2.addRange(fieldnum(Salestable, SalesTaker)).value('Admin');
    qbdsSalesTable2.unionType(UnionType::UnionAll);


    queryRun = new QueryRun(query);

    while (queryRun.next())
    {
    SalesTable = queryRun.get(tablenum(SalesTable));
    info(SalesTable.SalesId);
    }
    }

    out put :
    SO_0000001
    SO_0000001
    SO_0000002

    ReplyDelete
    Replies
    1. Hi Prabath,
      try this after adding range in query data source

      qbdsSalesTable1.addGroupByField(fieldnum(Salestable, SalesId));

      It will group ur datasource table by SalesID field.

      Delete
  2. I'm truly enjoying the design and layout of your website.
    It's a very easy on the eyes which makes it much more enjoyable for me to come
    here and visit more often. Did you hire out a designer to create your theme?
    Outstanding work!

    Feel free to surf to my web blog: Replica Louis Vuitton

    ReplyDelete
  3. wonderful points altogether, you simply gained a brand new reader.

    What would you suggest about your submit that you just
    made a few days in the past? Any sure?

    My webpage ... Beats By Dre Studio

    ReplyDelete

Thanks