Using Query objects

Path: Development > Programming in the AL language > Tables and data > Using Query objects · tier official · system reporting · narrative reviewed (checked by Opus)

Overview

Query objects retrieve and combine records from one or more tables into a single dataset. They can calculate totals, group rows, and be exposed as OData web services. Queries can be normal or API queries, and they can be opened in data analysis mode.

The pages follow the path of building a query. The overview and Query object pages explain what a query is and its discoverability properties. Data Item Links covers joining tables with DataItemLink and SqlJoinType. Filtering, aggregation, and date methods shape the dataset. Accessing Columns and Using Queries Instead of Record Variables show how to open, read, and use the results in AL code. The data analysis page covers interactive analysis of list page and query data without running reports.

Start with Query Overview, then read Data Item Links and Filtering in Query objects. Move to aggregation, date data, and code access as needed.

Key points

• Queries combine records from one or more tables into one dataset, can calculate totals and group rows, and can be exposed as OData web services.
• Data Item Links use DataItemLink and SqlJoinType, with InnerJoin, LeftOuterJoin, RightOuterJoin and FullOuterJoin, plus cross joins.
• Filtering options: DataItemTableFilter property, ColumnFilter property, filter rows, and SetFilter/SetRange methods.
• Aggregation uses the column Method property (Sum, Average, Min, Max, Count) across grouped records.
• Day, Month and Year methods in query columns extract date parts from Date and DateTime fields, with UTC conversion relevant for DateTime.
• In code, use OPEN, Read and TopNumberOfRows to read the dataset, then get column values from the current row.
• Queries give better performance and simpler code than record variables when joining multiple tables in codeunits.
• Data analysis mode offers columns, filters, row groups, pivot mode, date hierarchies, fields from related tables, and bookmarks to the Role Center.

Learn pages

• Accessing Columns of a Query Dataset
• Aggregating data in query objects: Do calculations on the fields of a column and return the calculated value in the dataset. Know the Dynamics NAV Total methods for running queries.
• Analyze list page and query data using data analysis: Learn how to use the analysis mode in Business Central to analyze data.
• Data Item Links: Using queries, retrieve records from one or more tables and combine the records into rows in a single dataset.
• Filtering in Query objects: Specify filters in a query to restrict the data in the resulting dataset. A filter applies conditions on fields in a table associated with the query.
• Query object: Description of the AL query object.
• Query Overview: Description of the query object.
• Retrieving Date Data in Queries: Learn how to retrieve year, month, or day from date fields in query results. Discover setup steps, examples, and best practices.
• Using Queries Instead of Record Variables

Videos and posts

Linked by a Haiku matcher with a grounding quote from the item’s summary (link/topics.ts); machine-generated.

• Business Central Queries: Simplifying Complex Data (community post): “Query objects let AL developers combine and filter data from several tables”
• Step-by-Step Guide to Business Central API Queries (community post): “Query objects configured with API settings automatically expose data to external systems”

Business Central pages and reports

The pages, reports and queries this hub’s Learn pages name (ms.search.form, API reference), joined to the code by exact object id (data/index/docs-objects.json); a page shows the table it runs on.

• Page 16 “Chart of Accounts” · on Table 15 “G/L Account”
• Page 22 “Customer List” · captioned “Customers” · on Table 18 “Customer”
• Page 25 “Customer Ledger Entries” · on Table 21 “Cust. Ledger Entry”
• Page 26 “Vendor Card” · on Table 23 “Vendor”
• Page 27 “Vendor List” · captioned “Vendors” · on Table 23 “Vendor”
• Page 31 “Item List” · captioned “Items” · on Table 27 “Item”
• Page 143 “Posted Sales Invoices” · on Table 112 “Sales Invoice Header”
• Page 144 “Posted Sales Credit Memos” · on Table 114 “Sales Cr.Memo Header”
• Page 456 “No. Series” · on Table 308 “No. Series”
• Page 457 “No. Series Lines” · on Table 309 “No. Series Line”
• Page 458 “No. Series Relationships” · on Table 310 “No. Series Relationship”
• Page 459 “Sales & Receivables Setup” · on Table 311 “Sales & Receivables Setup”
• Page 460 “Purchases & Payables Setup” · on Table 312 “Purchases & Payables Setup”
• Page 461 “Inventory Setup” · on Table 313 “Inventory Setup”
• Page 9300 “Sales Quotes” · on Table 36 “Sales Header”
• Page 9301 “Sales Invoice List” · captioned “Sales Invoices” · on Table 36 “Sales Header”
• Page 9303 “Blanket Sales Orders” · on Table 36 “Sales Header”
• Page 9304 “Sales Return Order List” · captioned “Sales Return Orders” · on Table 36 “Sales Header”
• Page 9305 “Sales Order List” · captioned “Sales Orders” · on Table 36 “Sales Header”
• Page 9306 “Purchase Quotes” · on Table 38 “Purchase Header”
• Page 9307 “Purchase Order List” · captioned “Purchase Orders” · on Table 38 “Purchase Header”
• Page 9309 “Purchase Credit Memos” · on Table 38 “Purchase Header”
• Page 9310 “Blanket Purchase Orders” · on Table 38 “Purchase Header”
• Page 9311 “Purchase Return Order List” · captioned “Purchase Return Orders” · on Table 38 “Purchase Header”

Source: Microsoft Learn (CC BY 4.0). Descriptions are Learn’s own.