Query objects in Business Central offer a simple and effective way to retrieve and explore data across multiple tables. They don’t require report layouts or complex page setups, making them ideal for quick validation or easy integrations. This guide explains how queries work, where they can be useful, and includes an example that you can adapt and use in your own solution.

1. What Are Query Objects in Business Central?

Query objects are AL-based definitions that let you pull data from one or more tables. You can use filters, sorting, and joins to shape the data you need.

Queries are designed for read-only tasks and are useful for reporting, troubleshooting, or preparing data for tools like Excel or Power BI via OData. You can also create a query page to view the results directly in Business Central.

2. Technical Overview of Query Objects in Business Central

Query objects streamline data retrieval in Business Central by allowing you to define a hierarchical structure of dataitems that mimic table joins. Each dataitem in a query can be linked to its parent with a DataItemLink, which defines how fields in one table relate to fields in another.

A brief review of the available join types:

• InnerJoin – returns only records with matching values between linked fields in both data items.
• LeftOuterJoin – returns all records from the upper (parent) data item and matching records from the lower (child) item.
• RightOuterJoin – the reverse: returns all records from the lower data item and matching ones from the upper item.
• FullOuterJoin – returns all records from both data items, including unmatched records on either side.
• CrossJoin – returns all possible combinations between two data sets (Cartesian product), used rarely and with caution. This property does not work together with the DataItemLink property for combining records from tables into a dataset.

In addition to joins, queries can restrict result sets using the Filter property and define how data should be sorted with OrderBy. These features are particularly helpful when building datasets for validation or tracking data flow (for example, checking if journal lines have generated ledger entries).

Queries can also be published as OData Web Services, enabling real-time data access through Excel, Power BI, or custom apps. This makes them ideal for easy integrations where full APIs are unnecessary. Overall, Query objects offer a balance between simplicity and flexibility. They are faster to create than reports, require no layouts, and provide just enough power to handle a wide range of read-only data retrieval needs.

3. When Queries Are Useful and Usable

• For quick data checks without building a full report
• For data validation and troubleshooting, where specific records need to be traced. See which journal lines have not generated ledger entries, for example.
• As easy data sources for Excel, Power BI, or third-party applications without complex API setups.
• In cloud environments (e.g., BC25), where SQL access is not available

4. Example Scenario: Tracking Orders’ Posting

Imagine you need to follow the full lifecycle of a sales order—from creation, through shipment and invoicing, all the way to financial posting. While this information exists in different parts of Business Central, there’s no single place where you can see it all together. With a query object, however, you can combine these stages into one structure and get a complete view of the sales flow in one dataset.

The query joins the following tables:

1. Sales Header – the original sales order or invoice header
2. Sales Shipment Header – to confirm whether the goods were shipped
3. Sales Invoice Header – to check that an invoice was generated
4. G/L Entry – to trace the financial registration of the invoice

To ensure visibility across all document types, the Sales Header includes both Order and Invoice types via DataItemTableFilter. The use of LeftOuterJoin ensures that records appear even when subsequent steps are missing — for example, orders that have not been invoiced, or invoices not yet posted to the general ledger.

This query helps answer questions like:

• Which orders have been invoiced but not posted?
• Are there any shipments that haven’t resulted in invoices?
• Have all invoices generated proper G/L entries?

Below is a working AL query example you can adjust to your own process flow needs:

The illustration below shows how the query captures the status of each sales document across its lifecycle—from order to posting. Each row represents either an order or an invoice, and using LeftOuterJoin, the query brings together related data even if some parts are still missing.

Here’s what each entry shows:

• 1002 – A sales order that has not yet been shipped or invoiced. This helps identify stalled or blocked processes.
• 1003 – A sales order that has been shipped but has not been invoiced yet. This might require action if invoicing is delayed.
• 1005 – A complete process flow: this order has been shipped, invoiced, and posted to the general ledger. Both positive and negative G/L entries are shown, reflecting the accounting side of the transaction.

This view provides immediate clarity on which sales orders are fully processed and which are still in progress.

If, for example, you chose to use the FullOuterJoin instead of the LeftOuterJoin, you would get the same 4 rows from above, but it’s because that’s all the demo entries that were created.

However, if you chose the InnerJoin, you would get only the orders that have entries on both sides – in the Sales Header as an order and in the General Ledger:

The same output as above the list would have, if you used RightOuterJoin, but just because, in this scenario, the entries cannot appear in the General Ledger without first being orders and invoices. If that weren’t the case, the entries that do not have data in any of the Sales Headers would appear in this list. All the joins of this scenario are put in the graph to better understand it:

5. Conclusion

In summary, query objects are a simple yet powerful addition to your Business Central AL toolkit. They provide a balance between simplicity and flexibility, allowing you to build fast, read-optimized queries that can significantly reduce manual checks and enhance integration with external tools. By incorporating query objects into your workflow, you can streamline troubleshooting processes, improve traceability, and ultimately deliver more robust solutions for your customers.