Building a query Object through Code.

 Building a query object:

Query objects in Dynamics 365 for Finance and Operations are used to build SQL statements for reports, views, forms, and so on. They are normally created in the AOT using the drag-and-drop functionality and by defining various properties. Query objects can also be created from the code at runtime. This is normally done when AOT tools cannot handle complex and/or dynamic queries.

  • Build Query in X++: Queries can also be built dynamically with X++ code. Both approaches are used in standard applications. One advantage of making the query dynamics is that it can not be public in the AOT, and is protected against unintentional AOT changes. 
  • X++ Query Component
    • QueryRun
    • Query
    • QueryBuildDataSource
    • QueryBuildFieldList
    • QueryBuildRange
    • QueryFilter
    • QueryBuildDynaLink
    • QueryBuildLink
  • QueryRun: use query Run objects to execute the query and fetch data.
  • Query: The query object is the definition master. its own properties and has one or more data sources added.
  • QueryBuildDataSource: using QueryBuildDataSource you add all the tables. You want to join also. This is also where you defined how to result set is to be sorted. The order mode () method lets you define OrderBy() and GroupBy().
  • QueryBuildFieldList: The QueryBuildfieldList object defines which field to fetch from the database. The default is a dynamics field list that is equal to a "select*from......" each data source has only one QueryBuildFieldList object which contains information about all selected fields. You also specific aggregate functions like sum, count, and avg with field list object.
  • QueryBuildRange: The queryBuildRange object contains a limitation of the query on a single field.
  • QueryFilter: The queryFilter object is used to filter the result set of an outer join. It filters the data at a later stage than queryBuildRange objects and filters the parents table based on the child table results.
  • QueryBuildLink: Specifies the relation between the two data sources in the join. Can only exist on a child data source.   


 Step1: Open VS create a new solution, create a runnable class named class      CustTableQuery, and enter the following code:

      class Lok_CustTableQuery

 {   

    public static void main(Args _args)

        {

            Query query;

            QueryBuildDataSource qbds1;

            QueryBuildDataSource qbds2;

            QueryBuildRange qbr1;

            QueryBuildRange qbr2;

            QueryRun queryRun;

            CustTable custTable;

            query = new Query();

            qbds1 = query.addDataSource(tableNum(CustTable));

            qbds1.addSortField(

                fieldNum(CustTable, AccountNum),

                SortOrder::Ascending);

            qbr1 = qbds1.addRange(fieldNum(CustTable, AccountStatement));

            qbr1.value(queryValue(CustAccountStatement::Yearly));

            qbr2 = qbds1.addRange(fieldNum(CustTable, CustGroup));

            qbr2.value(

                SysQuery::valueLike(queryValue('12345')));

            qbds2 = qbds1.addDataSource(tableNum(CustInvoiceTrans));

            qbds2.relations(true);

            qbds2.joinMode(JoinMode::ExistsJoin);

          queryRun = new QueryRun(query);

            while (queryRun.next())

           {

             custTable= queryRun.get(tableNum(CustTable));

             info(strFmt("%1, %2, %3", custTable.CustAccount, custTable.CustGroup, custTable.AccountStatement));

 

           }

         }

    }



First, we create a new query object, Next, we add a new table CustTable data sources to the query objects by calling its addDataSource. Here also call the addSortField method to enable sorting by the Customer Accounts. 



Location: CFCQ+PF Noida, Uttar Pradesh, India

Comments

Post a Comment

Popular posts from this blog

Configuring and using the Excel Data Connector add-in in D365 F&O

Image
    Excel Data Connector add-in in D365 F&O In this recipe, we will show you how to configure an Excel Data Connector add-in and use it to export data from Dynamics 365 for Finance and Operations and publish the updated data back in Dynamics 365 for Finance and Operations.  The Excel Data Connector app interacts with Excel workbooks and OData services created for publicly exposed data entities. Steps 1. Open a new workbook in Excel and navigate to the Insert tab. Under Add-ins, select My Add-ins, as shown in the following screenshot:            2. Under Office Add-ins, navigate to STORE, search for Microsoft Dynamics Office Add-in, and click on Add. This is shown in the following screenshot: 3. Once you've clicked, a new window will open within Excel as follows. Click on Add server information and the system will ask for account credentials. You have to sign in as the user that has access in your Dynamics 365 for Finance and Op...
Read more

Configuring Power BI in D365 Finance and Operation

Image
 Configuring Power BI In this blog, we can connect to Microsoft Dynamics 365 for Finance and Operations with Power BI Desktop to create custom Dynamics 365 reports and dashboards for use with the Power BI service.To enable Power BI the very first thing you have to do is configure your Power BI with Dynamics 365 for first use. Step1:  Log in to your Power BI account using https://powerbi.microsoft.com/en-us/. Step2: Give details about the application and Dynamics 365 for Finance and Operations environment, you can refer to the following for more clarity: App Name: Give an application name App Type: Choose a server-side web app Redirect URL: Add OAuth at the end of the home page URL Home page URL: Application URL Step3:  Choose APIs to access: Step4: Register your app: In this step, click on Register App and the system will create client ID and client secret for your new app. Keep a backup of both values, you need to put these values in the next step...
Read more

How to Change report title in SSRS Report in D365 F&O

Image
  How to Change Report Title Based on Condition in SSRS Report in D365 F&O: Step1: Insert a new Text Box in Report Design and write Expression is the given Below: Step2: Add a DP class in Project Write a code:                                                              if (Condition)                 tmpTable.ReportTitle = "Label of title1";                 else                 tmpTable.ReportTitle = "Label of title2";      Note: this condition writes in the process report method or where your tmp table is inserted.
Read more