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

Arguments

The BC.QUERY function uses the following arguments:

Argument	Required / Optional	Description
`ConnectionName`	Optional	Provide one of the following values: Connection name as defined in Connection Manager. Velixo range expression defining a set of connection names. An array of settings as described in the Multiple connections per formula article. We recommend using the VX.SETTINGS function to construct the array. OR Omit the argument to return results for all compatible connections with default aggregation settings.
`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`. You can use the BC.QUERYFILTER function to generate filters ready to use with BC.QUERY.
`Select`	Optional	Comma-separated list of object fields to be included in the resulting dataset; also for related objects (for example, `contactsInformation.contactId`). If this argument is empty, all the Fields from the Object will be returned. In case of objects nested within the queried object, you can also use the `unpivot(key, value)` syntax to return the values of the `key` parameter as columns, and populate the columns with values of the `value` parameter. See example. You can only use `unpivot` expressions for objects with at least one property. You can add several `unpivot` expression to a single formula. You can use an object property as both a selected field and a part of an `unpivot` expression.
`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
`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

Using the ‘unpivot’ selection method for nested objects

CODE

=BC.QUERY(
  "BC",
  "journalLines",
  "journalId eq 375762c3-e6d7-ef11-9344-6045bdc8a19b",
  "accountNumber, unpivot(dimensionSetLines.code, dimensionSetLines.valueCode)"
)

Description: Queries the journalLines object filtered for the journalId field to equal 375762c3-e6d7-ef11-9344-6045bdc8a19b. Returns values from the accountNumber property in the first column.

unpivot is used to create columns based on the key value dimensionSetLines.code, and the columns are populated with data retrieved from the value field dimensionSetLines.valueCode where appropriate values are available.