Queries can be accessed from C/AL code. It is a good idea to use queries instead of writing C/AL code involving record variables when you want to read records from multiple tables at the same time.

Executing a query from C/AL code is as simple as defining a variable of type Query and typing the following sentences:

MyFirstQuery.OPEN;
WHILE MyFirstQuery.READ DO
  OutputData(MyFirstQuery.Item_No,
    MyFirstQuery.Month_Posting_Date,
    MyFirstQuery.Sum_Quantity,
    MyFirstQuery.Source_No,
    MyFirstQuery.Description,
    MyFirstQuery.Name,
    MyFirstQuery.Customer_Posting_Group);

In this example, we have defined a query variable called MyFirstQuery that points to the query created earlier on in this chapter.

We first have to open the query using OPEN and then read the results (READ) using a loop statement. On every READ, we will get a different result record.

We can access any of the columns defined in the query by typing the following:

 <QueryVariableName>.<ColumnName>

In the example, we pass all the columns as parameters of a function called OutputData() that will handle the results.

Just a few sentences are needed to execute a query. Isn't that cool?

Designing the query has been easy. Executing the query has been even easier. How much effort do you think is needed to get the exact same result as in the query but using pure C/AL code? Examine the following code and evaluate it yourself:

ItemLedgEntry.SETRANGE("Entry Type",ItemLedgEntry."Entry Type"::Sale);
IF ItemLedgEntry.FINDSET THEN
REPEAT
  tmpItem.SETRANGE("No. 2",ItemLedgEntry."Item No.");
  tmpItem.SETRANGE(tmpItem."Vendor No.",ItemLedgEntry."Source No.");
  tmpItem.SETRANGE("Price Unit Conversion",DATE2DMY(ItemLedgEntry."Posting Date",2));
  IF tmpItem.FINDSET THEN
  BEGIN
    tmpItem."Unit Price" := tmpItem."Unit Price" + ItemLedgEntry.Quantity;
    tmpItem.MODIFY;
  END
  ELSE
  BEGIN
    IF Item.GET(ItemLedgEntry."Item No.") THEN;
    IF Customer.GET(ItemLedgEntry."Source No.") THEN;

    tmpItem.INIT;
    tmpItem."No." := FORMAT(tmpItemNo);
    tmpItemNo := tmpItemNo + 1;
    tmpItem."No. 2" := ItemLedgEntry."Item No.";
    tmpItem."Vendor No." := ItemLedgEntry."Source No.";
    tmpItem."Price Unit Conversion" := DATE2DMY(ItemLedgEntry."Posting Date",2);
    tmpItem.Description := Item.Description;
    tmpItem."Description 2" := Customer.Name;
    tmpItem."Inventory Posting Group" := Customer."Customer Posting Group";
    tmpItem."Unit Price" := ItemLedgEntry.Quantity;
    tmpItem.INSERT;
  END;
UNTIL ItemLedgEntry.NEXT = 0;

tmpItem.RESET;
IF tmpItem.FINDSET THEN
REPEAT
  OutputData(
    tmpItem."No. 2",
    tmpItem."Price Unit Conversion",
    tmpItem."Unit Price",
    tmpItem."Vendor No.",
    tmpItem.Description,
    tmpItem."Description 2",
    tmpItem."Inventory Posting Group");
UNTIL tmpItem.NEXT = 0;

We first have to define all the variables used in the code, namely, ItemLedgerEntry, Item, Customer as record variables, tmpItem as a temporary record variable, and tmpItemNo as an integer.

We filter ItemLedgerEntry to only retrieve sales entry types. We loop through all the ItemLedgerEntry records and, in every loop, we check whether a temporary record exists or not for the grouping fields. If it does not exist, we create the temporary record with the appropriate values. If it exists, we just sum up quantities in the existing temporary record. Finally, we loop through all the temporary records, which have the same results as the query we defined earlier.

The code is confusing. There are no sentences in C/AL to group. We have to use some tricks to actually get that behavior. In this case, we use temporary records. There is no table in Dynamics NAV with the exact fields we need, so we just picked one-Item. We've used the fields in that table that had the type and length we needed, without really looking at the name of the fields.

Believe me, when I say; it was much easier to develop the query than this C/AL code.

The Windows client can display a set of predefined charts that use Dynamics NAV data. With Microsoft Dynamics NAV 2013, queries can be used as data sources for those charts.

We will use the query defined earlier on in this chapter as the data source of a chart and we will display it on the home page of the Dynamics NAV Windows client.

To define a query as the data source of a chart, perform the following steps:

To display the chart on the home page of the Windows client, perform the following steps:

In Microsoft Dynamics NAV 2009, it was possible to publish page and codeunit objects as web services to allow external applications to access Dynamics NAV data and business logic. In Microsoft Dynamics NAV 2013, it is also possible to publish query objects as web services.

Page and codeunit objects can be accessed through SOAP web services. Queries can only be accessed through the new web services protocol introduced in Microsoft Dynamics NAV 2013: OData web services .

To publish a query as a web service, perform the following steps:

At this point, the query is already published as a web service. You can check to see if it is accessible using your browser. To do so, perform the following steps:

To access a concrete web service, enter its name at the end of the URL. In the case of the previous example, that would be http://localhost:7048/DynamicsNAV70/Odata/MyQueryWS.

Because Dynamics NAV 2013 queries can be published as web services, they can be accessed by absolutely any application that can consume OData web services. There is no limit. It can be an external application developed by you for the only purpose of reading Dynamics NAV data or it can be a commonly-used application that supports OData web services.

In this section, we will see how to use Dynamics NAV 2013 queries in Microsoft Office Excel.