Query Object

Business Central query objects enable you to retrieve records from one or more tables and then combine the data into rows and columns in a single dataset. Query objects can also perform calculations on data, such finding the sum or average of all values in a column of the dataset.

There are two types of query objects: normal and API. This article describes normal query objects, which can be used to display data in the user interface. API query objects are used to generate web service endpoints and cannot be displayed in the user interface. For information about creating a query of the type API, see API Query Type.

Creating a query object

A query object is comprised mainly of two different types of elements: dataitems and columns. A dataitem specifies the table to retrieve records from. A column specifies a field of the table to include in the resulting dataset of a query. The basic steps to create a query object are as follows:

1. Add the query keyword, followed by the elements control.

2. Build the dataset by adding dataitem controls and column controls within the elements control.

   The hierarchy of the dataitem and column controls is important because it will determine the sequence in which data items are linked, which in turn will control the results. Working from top-to-bottom, you start by adding the dataitem control for first table that you want in the dataset, then add column controls for each table field that you want to include in the dataset. For the next table, you add another dataitem control that is embedded within the first dataitem control, then add column controls as needed. You continue this pattern for additional tables and fields.

3. When you have specified the dataitem and column elements, create links and joins between the dataitem elements.

   Dataitem links and joins determine which records to include in the dataset based on the values of a common field between dataitems. You set a link between one or more fields of the dataitem tables with the DataItemLink Property and you define the type of the link using the SQLJoinType Property. Both properties must be set on the lower dataitem of the query object. For more information, see Linking and Joining Data Items.

The following shows the basic structure of a query object.

query ID Name
{

    elements
    {
        dataitem(DataItem1; Table1)
        {
            column(Column1; Field1)
            {
            }
            column(Column2; Field2)
            {
            }
            dataitem(DataItem2; Table2)
            {
                // Sets a link between FieldY of Table2 and FieldX of Table1.
                DataItemLink = FieldY = DataItem1.FieldX;
                //The dataset contains records from Table1 and Table2 where a match is found between FieldY and FieldX.
                SqlJoinType = InnerJoin;

                column(Column1; Field1)
                {
                }
                dataitem(DataItem3; Table3)
                {
                    DataItemLink = FieldZ = DataItem2.FieldY;
                    SqlJoinType = InnerJoin;
                    column(Column1; Field1)
                    {
                    }
                }
            }
        }
    }
}

Note

Extension objects can have a name with a maximum length of 30 characters.

Snippet support

Typing the shortcut tquery will create the basic layout for a Query object when using the AL Language extension for Microsoft Dynamics 365 Business Central in Visual Studio Code.

Tip

Use Ctrl+Space to trigger IntelliSense and get assistance on code completion, parameter info, quick info, and member lists.

Query example

The following example shows a query that displays a list of customers with sales and profit figures. The query primarily retrieves fields from the Customer table, but also displays fields from the Salesperson Purchaser and Country Region tables.

The query also uses the DataItemLink property to create a link between the Customer table, Salesperson Code field and the Salesperson Purchaser table, Code fields and a link between the Customer table, Country/Region Code field and the Country/Region table, Code field.

query 50102 "Top Customer Overview"
{
    QueryType = Normal;
    Caption = 'Top Customer Overview';

    DataAccessIntent = ReadOnly; // use this to read data from the secondary database replica to speed up performance

    elements
    {
        dataitem(Customer; Customer)
        {
            column(Name; Name)
            {
            }
            column(No; "No.")
            {
            }
            column(Sales_LCY; "Sales (LCY)")
            {
            }
            column(Profit_LCY; "Profit (LCY)")
            {
            }
            column(Country_Region_Code; "Country/Region Code")
            {
            }
            column(City; City)
            {
            }
            column(Global_Dimension_1_Code; "Global Dimension 1 Code")
            {
            }
            column(Global_Dimension_2_Code; "Global Dimension 2 Code")
            {
            }
            column(Salesperson_Code; "Salesperson Code")
            {
            }
            dataitem(Salesperson_Purchaser; "Salesperson/Purchaser")
            {
                DataItemLink = Code = Customer."Salesperson Code";
                column(SalesPersonName; Name)
                {
                }
                dataitem(Country_Region; "Country/Region")
                {
                    DataItemLink = Code = Customer."Country/Region Code";
                    column(CountryRegionName; Name)
                    {
                    }
                }
            }
        }
    }
}

Important

You cannot run a query that gets data from both the application database and the business data database. This also applies to single-tenant deployments so that you do not have to rewrite queries if you decide to export the application. For a description of which tables are considered part of the application database, see Separating Application Data from Business Data.

See Also

Linking and Joining Data Items
Aggregating Data in Query Objects
Query Objects and Performance
Query Properties
Query DataAccessIntent
Developing Extensions
AL Development Environment
API Query Type