BC.QUERY

Overview

Use the BC.QUERY function to return a list of objects of a specified Business Central object type (as either an Excel array or an Excel table), filtered to match provided criteria.

Syntax

CODE

=BC.QUERY(
    ConnectionName,
    Object,
    Filter,
    Select,
    IncludeHeader,
    Settings,
    Api
    TableOutputCell
)

Arguments

The BC.QUERY function uses the following arguments:

Argument	Required/Optional	Description
`ConnectionName`	Required	The name of the connection, as specified in the Connection Manager
`Object`	Required	Business Central object name (e.g., `generalLedgerEntries`, `accountingPeriods`). Use the BC.EXPANDOBJECTRANGE function to retrieve the list of Business Central objects. Certain objects (for example, `journalLines`) may require additional filtering to be queried - see example below.
`Filter`	Optional	OData4 query based on the fields in the object. OData4 operators are supported, including: `eq, ne, gt, ge, lt, le, has, in, and, or, not, add, sub, mul, div, divby, mod, startswith, endswith`.
`Select`	Optional	Comma-separated list of object fields to be included in the resulting dataset. If this argument is empty, all the Fields from the Object will be returned.
`IncludeHeader`	Optional	`TRUE` or `FALSE`, indicating whether column headers will be included in the dataset. `TRUE` by default
`Settings`	Optional	Two-dimensional array Pass one or more keys with their values. Each key controls a different setting: Sort: A comma-separated list of columns to sort by. Add `:DESC` after a column name to sort it in descending order (for example, `accountNo:DESC`). Columns without `:DESC` are sorted ascending. Limit: The maximum number of rows. For example, 200. Offset: The number of rows to skip from the start. This is useful for paging extensive results. We recommend sorting by a unique column (for example, `entryNumber`) so the data appears in a predictable order. CaseInsensitive: If set to `FALSE`, filters ignore uppercase or lowercase differences in the Filter (for example, `“Sales”`, `“SALES”` and `“sales”` all match the same data). Default value: `FALSE` API: API to be referenced for Object names. The accepted predefined values are: `common` - Common API v2 (default) `web-service` - OData endpoints exposed per tenant `Velixo` - endpoints exposed via the Velixo AL extension Use the following syntax to use a published AL Extension: {publisher}/{group}/{version} - a fragment of the extension's URL `Velixo` is an alias for the most recent version of the Velixo AL extension
`Api`	Optional	API to be referenced for Object names. The accepted predefined values are: `common` - Common API v2 (default) `web-service` - OData endpoints exposed per tenant `Velixo` - endpoints exposed via the Velixo AL extension Use the following syntax to use a published AL Extension: {publisher}/{group}/{version} - a fragment of the extension's URL `Velixo` is an alias for the most recent version of the Velixo AL extension
`TableOutputCell`	Optional	If the argument is specified, the function output is represented as an Excel table, and the first column in the `Select` argument is populated by this address. See the Table Mirroring article for details. If the argument is omitted, the result is returned as an array.

Excel Online

Important: Loading large datasets with BC.QUERY is not performant in Excel Online due to the limitations of the Excel platform in the browser. If your dataset contains more than approximately 100,000 records, we strongly recommend using a desktop version of Excel 365 for Windows or Mac OS.

Examples

First ten records

CODE

=BC.QUERY(
    "BC",
    "generalLedgerEntries",
    ,
    ,
    ,
    10
)

Description: Returns the first 10 records found for the Object generalLedgerEntries.

Filters and column selection

CODE

=BC.QUERY(
    "BC",
    "salesinvoices",
    "sellToState eq 'ON' and status eq 'Paid'",
    "number,customerNumber,invoiceDate,totalAmountExcludingTax"
)

Description: Returns the number, customerNumber, invoiceDate and totalAmountExcludingTax fields for all salesInvoices object with the status Paid, issued to customers in the province of Ontario.

Querying objects that require filtering

Certain Business Central objects require additional filtering to be queried. For instance, when you try to query the journalLines object without filters, you will receive an error:

This object requires you to provide a filter for the batch ID or journal ID field, for instance:

CODE

=BC.QUERY(
  "BC",
  "journallines",
  "journalid eq 42009fff-9023-ef11-840f-6045bde9c820"
)

Description: Returns all entries for the journalLine object with journal ID equal to 42009fff-9023-ef11-840f-6045bde9c820 for the connection BC

99afbc75-1844-45c5-921c-cddfcf37a58e-20251017-130556.png