BC.QUERY | Velixo

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.

You can use the Query Builder functionality to construct formulas visually.

Syntax

=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 with information on their insertability, updatability, and deletability. Objects without an ID field cannot be queried. 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. Rows where the selected columns have no value are included in the results - see Rows with no value in selected columns. Rows where the selected columns have no value are included in the results - see Rows with no value in selected columns.
`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. If `Limit` is the only setting you need, provide the number directly as the `Settings` argument (for example, `10`). 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. 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
`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.

Rows with no value in selected columns

The function returns all matching rows, including rows where the selected columns have no value. Blank cells in the results are expected and are not an error. A row appears entirely blank only when none of the selected columns has a value for that record.

Because every matching record produces a row, the spilled range sizes to the number of matching records, not to the number of populated values. Account for this in any downstream formula or conditional formatting that depends on the size of the result.

To exclude rows with no value in a column, add a condition to the Filter argument.

=BC.QUERY(
    "bc",
    "agedAccountsPayables",
    BC.QUERYFILTER("bc","agedAccountsPayables",,,"vendorNumber","<> null"),
    "vendorNumber"
)

Description: Returns the vendorNumber column from agedAccountsPayables, excluding rows where vendorNumber has no value.

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

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

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

BC.QUERY returning the first 10 generalLedgerEntries records as a spilled array in Excel

Filters and column selection

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

BC.QUERY returning number, customerNumber, invoiceDate, and totalAmountExcludingTax for Paid salesInvoices in 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:

BC.QUERY error message when querying the journalLines object without a required journalId filter

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

=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

BC.QUERY returning all journal line entries filtered by a specific journalId value

Using the ‘unpivot’ selection method for nested objects

BC.QUERY using the unpivot selection method to return accountNumber and dimensionSetLines values as dynamic columns

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

Query web-services API objects

=BC.QUERY(
  "BC",
  "Vendors",
  ,
  "id, number, displayName, email",
  ,
  {"api";"web-service"}
)

Description: Queries the id, number, displayName fields in the web-services API object Vendors for the connection BC.