BC.QUERYTABLE | Velixo

Overview

Use the BC.QUERYTABLE function to return data directly from queryable Business Central tables.

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

Syntax

=BC.QUERYTABLE(
  ConnectionName,
  Table,
  Filter,
  Select,
  IncludeHeader,
  Settings,
  TableOutputCell
)

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.
`Table`	Required	Table name. Use the BC.EXPANDTABLERANGE function to retrieve a list of tables available for querying.
`Filter`	Optional	Filter expression. Use the BC.QUERYTABLEFILTER function to create ready-to-use filters. Flow fields are scoped with a flow filter rather than an ordinary column filter - see the dedicated guide for details. Filtering on fields from related tables (see `Select` description) does not reduce the number of rows returned (flow fields are an exception). Rows that don't match the filter condition are still included, and the joined field is left blank for those rows. Filtering based on the `SystemModifiedAt` field combined with Smart refresh is not recommended, as it may lead to unexpected results.
`Select`	Optional	Comma-separated list of table columns to be included in the resulting dataset. If omitted, all table columns will be returned. To include a field from a related table, use the `->` operator in one of the following forms: `RelatedTable->RelatedTableField` - navigates by table name. Use when the main table has exactly one column pointing to the related table. Related table names are listed in BC.TABLEDEFINITION results. `ColumnName->RelatedTableField` - navigates from a specific column in the main table to a field in the related table. Use this form when the main table has more than one column pointing to the same related table. Related fields are listed in BC.TABLEDEFINITION results. Multiple joined fields can be included in the same `Select` argument alongside regular columns. When more than one field uniquely identifies each record in the related tables, provide additional field names using the `ExtraJoinKeys` setting in the `Settings` argument. When the related table contains multiple matching records (a one-to-many relationship), all matching values are returned in a single cell by default. The number of rows in the result always matches the main table - rows are not duplicated. Only one level of join is supported. You can join from a main table to a related table, but you cannot chain joins further (e.g., `Sales Line → Sales Header → Customer` is not supported). The join functionality requires Velixo Extension for Business Central 2026.5 or later. Formulas that do not use joins continue to work with older extension versions. See the dedicated article for update instructions. Use the `unpivot(key, value)` expression to turn a related object's attribute values into separate columns. One attribute is designated as the key (its distinct values become column names) and another as the value (its values populate those columns). Syntax: `unpivot(nestedObject.keyAttribute, nestedObject.valueAttribute)` Aggregate a column by wrapping it in an aggregate function: `SUM()`, `COUNT()`, `MIN()`, or `MAX()`. Any columns in the `Select` list that are not wrapped in an aggregate function act as grouping keys — the result returns one row per distinct combination of those columns, with the aggregate calculated across each group. Aggregation works together with joins and `unpivot()` in the same `Select` argument. You can group by a joined field, aggregate a column, and unpivot a related object's attributes in a single formula. Use aggregation when using a Flow Field is not an option, or when you need a more advanced query (e.g., one that combines multiple dimensions or joins). Flow fields offer superior performance to aggregation. When an aggregated column takes its values from a joined field that returns several values per row, those values are combined into a single number before the aggregate is applied.
`IncludeHeader`	Optional	Indicates whether column headers are included in the dataset. Accepted values: `TRUE`, `FALSE` Default value: `TRUE`
`Settings`	Optional	Two-column array, containing one or more of the following keys: `Sort` - defines the sort order provide a list of columns to be sorted as a comma-separated list sorts in ascending order by default add the `:DESC` suffix to sort in descending order no sorting if omitted `Limit` - defines a limit of records returned provide the max. number of records to be returned default limit: 2000 records `Offset` - defines a number to be skipped at the beginning of the result set default offset: no offset `ExtraJoinKeys` - required when more than one field uniquely identifies each record in the related tables. Declares the additional columns needed to identify the correct row in the related table. Available fields are listed in an error message when too few key fields are provided. Syntax: `"PrimaryColumn->JoinTableField = MainTableField"` Example: Joining `Sales Line` to `Sales Header` requires both `Document No.` and `Document Type` to match correctly, as, e.g. an Invoice and an Order might have common document numbers: `{"ExtraJoinKeys", "Document No.->Document Type = Document Type"}` Multiple `ExtraJoinKeys` entries can be provided for relationships requiring more than two matching columns. You can use the VX.SETTINGS function to construct the array for the argument. Using the `Limit` and/or `Offset` settings will trigger a full refresh of the formula, even if Smart refresh is selected.
`TableOutputCell`	Optional	Specify the target cell address to return results in an Excel table. See the Table Mirroring article for details.

Examples

Select fields from a table, output in Excel table

=BC.QUERYTABLE(,"Contact",,"First Name, Surname, Job Title, Company Name, Address, City",,,I6)

Description: Returns the contents of the table Contact found in the default connection. Columns First Name, Surname, Job Title, Company Name, Address, and City are selected. The results are output in an Excel table in cell I6.

=BC.QUERYTABLE("bc","Sales Line",,"Document No.,Bill-to Customer No.,Bill-to Customer No.->Name,Gen. Bus. Posting Group,Gen. Bus. Posting Group->Description")

Description: Returns Sales Line records with customer names and posting group descriptions looked up from their respective related tables.

=BC.QUERYTABLE("bc","Sales Line",,"Document Type,Document No.,Document No.->Amount",,{"ExtraJoinKeys","Document No.->Document Type= Document Type"})

Description: Joins Sales Line to Sales Header using both Document No. and Document Type, ensuring records of different types with the same document number are not mixed up.

Use unpivot in the Select argument to use nested object attributes as columns

=BC.QUERYTABLE(,"Item Attribute Value",,"unpivot(Attribute Name, Value)")

Description: Turns each distinct value of Attribute Name into its own column header, with the corresponding Value populating the cells, so that instead of multiple rows per item, you get one row with all attributes spread across columns.

Use aggregated values in columns

=BC.QUERYTABLE("bc","Sales Line",,"Sell-to Customer No.->Address, Sum(Amount), Count(Amount), unpivot(Dimension Set ID->Dimension Name, Dimension Set ID->Dimension Value Name)")

Description: Groups Sales Line records by the customer address joined from the Customer table, and for each address returns the total (SUM) and number (COUNT) of Amount values. The dimension set is unpivoted so each dimension (Area, Customer group, and so on) appears as its own column.

Result: