Queries in D365FnO

 

Queries

Views have limitation, they can be used only for inner joins. You can create queries for other joins and use them in your views. Queries are faster, compiled and reusable.

There are two Types of Queries:

·       AOT/Static queries

·       Dynamics queries

Static Queries:

Create a new query in your project >> Give the name as "ATTCustTable"

Go to datasources >> drag and drop ATTCustTable to the datasource

In field’s property: Set the dynamic property to "Yes" if you want all the fields. If you make it "No", only user selected fields will come.

Go to ranges node in the datasource and add new ranges on - AccountNumber, Name

On the AccountNumber range - set the value to 805..810.

In the order by - select Name field - descending order

How to run this query, Create a new runnable as shown below

static void RunAOTQuery(Args _args)
{
    Query q = new Query(querystr(ATTCustTable)); //Intrinsic/ Inbuild function

    QueryRun queryRun;

    ATTCustTable attCusttable;

    queryRun = new QueryRun(q);

    if (queryRun.prompt())
    {
        while (queryrun.next()) // 805 t0 810
        {
            g = queryRun.getNo(1);
            //g = queryRun.get(tablenum(attCusttable));

            info(g.AccountNumber +',' + g.Name);

        }
    }
}

Run time Queries:

Query Framework classes have four classes

Query, QueryRun, QueryBuildDataSource, QueryBuildRange

Example:
static void RunTimeQueries(Args _args)
{

    Query q = new Query();

    QueryBuildDataSource     qbds;

    QueryBuildRange     qbrAccNo, qbrName;

    QueryRun     queryRun;

    ATTCustTable attCustTable;

   

    qbds = q.addDataSource(tablenum(ATTCustTable));

    qbrAccNo = qbds.addRange(fieldnum(ATTCustTable, AccountNumber));   

    qbrAccNo.value('805..810'); // queryvalue will convert anytype datatype

   

  queryRun = new QueryRun(q);

    if (queryRun.prompt()) // This will open user interactive form – sysqueryForm
    {
        while (queryrun.next()) // 803 t0 806
        {
            g = queryRun.getNo(1);

            //g = queryRun.get(tablenum(ATTCustTable));

            info(g.AccountNumber +',' + g.Name);

        }
    }
    else
        throw error("User has cancelled the operation");
}

Qbds2 = qbds.addDatsaource – To add another datasource

Qbds.addlink to add relation with another database