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,
    OutputTableAddress
)

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.
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
OutputTableAddress	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.