How to add data sources/ranges to query in D365Fo using x++.

Hi guys, Today we see how to add data sources/ranges to query in D365Fo using x++.

Range creation:

SysQuery::findOrCreateRange('this.queryBuildDataSource()',
                            fieldNum(Mytable, Field)).value('Value');

Example:

    Query       query = new Query();

    query.addDataSource(tableNum(CustTable));

    SysQuery::findOrCreateRange(query.dataSourceNo(1),
                                fieldNum(CustTable, CustGroup)).value('1001');

    QueryRun    qr = new QueryRun(query);

    Info(qr.toString());
    //Output : SELECT * FROM CustTable(CustTable_1) WHERE ((CustGroup = N'1001'))

    info (qr.query().dataSourceNo(1).toString());
    //Output : Query object db91850: SELECT * FROM CustTable(CustTable_1)
    //WHERE ((CustGroup = N'1001'))

----------------------------------------------------------------------------------

Adding to Datasource to query:

    SysQuery::findOrCreateDataSource('Query', tableNum('Table'));
    
    QueryBuildDataSource batchbQbds = SysQuery::findOrCreateDataSource('Query',
                                    tableNum('Table'),//New table Id
    tableNum('parent table'));//existing parent table Id
    batchbQbds.relations(true);
    batchbQbds.joinMode(JoinMode::ExistsJoin);

Example:

    Query                   query = new Query();
    QueryBuildDataSource    batchbQbds;
    query.addDataSource(tableNum(CustTable));

    batchbQbds = SysQuery::findOrCreateDataSource(query,
                            tableNum(CustGroup),//New table Id
    tableNum(CustTable));//existing parent table Id
    batchbQbds.relations(true);
    batchbQbds.joinMode(JoinMode::ExistsJoin);

    QueryRun    qr = new QueryRun(query);

    Info(qr.toString());
    // Query object 19c57b50: SELECT * FROM CustTable(CustTable_1)
    //  EXISTS JOIN * FROM CustGroup(CustGroup_1)
    //  WHERE CustTable.CustGroup = CustGroup.CustGroup

---------------------------------------------------------------------- Adding to Datasource name to query:

    QueryBuildDataSource    qbd = SysQuery::findOrCreateDataSourceByName(
                                'query',
                                'Data source name',// New table name
                                tableNum(Table),//Table Id
                                workerQbds.name(),// Parent datasource name
                                workerQbds.table());// Parent datasource Table Id
    qbd.relations(true);
    qbd.joinMode(JoinMode::InnerJoin);

Example:

    Query                   query = new Query();
    QueryBuildDataSource    batchbQbds = query.addDataSource(tableNum(CustTable));
    QueryBuildDataSource    qbd = SysQuery::findOrCreateDataSourceByName(
                                query,
                                'Test1',// New table name
    tableNum(CustGroup),//Table Id
    batchbQbds.name(),// Parent datasource name
    batchbQbds.table());// Parent datasource Table Id
    qbd.relations(true);
    qbd.joinMode(JoinMode::InnerJoin);

    QueryRun    qr = new QueryRun(query);

    // info (qr.query().dataSourceNo(1).toString());
    Info (qr.toString());
    //SELECT * FROM CustTable(CustTable_1)
    //JOIN * FROM CustGroup(Test1) ON
    //CustTable.CustGroup = CustGroup.CustGroup

------------------------------------------------------------------

Return query as string:

    Info(qr.toString());
    //Output : SELECT * FROM CustTable(CustTable_1) WHERE ((CustGroup = N'1001'))

    info (qr.query().dataSourceNo(1).toString());
    //Output : Query object db91850: SELECT * FROM CustTable(CustTable_1)
    //WHERE ((CustGroup = N'1001'))

------------------------------------------------------------------------------

Total number of records available in query:

    info(strFmt('Records - %1',SysQuery::countTotal(qr)));

Total number of loops in query:

    info(strFmt('Datasource - %1',SysQuery::countLoops(qr)));

Keep Daxing!!