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. You can filter on fields from related tables (see the `Select` description). Rows that don't match a related-field filter are excluded from the results. To filter on a related field, include at least one field from that related table in the `Select` argument. Excluding non-matching rows depends on the Velixo Extension for Business Central - see the requirements page for the minimum version. With older extension versions, all rows are returned instead, and the related field returns an empty value for non-matching rows. Filters on aggregated columns (for example, `SUM(Amount)`) are not supported. 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 depends on the Velixo Extension for Business Central. See the requirements page for the minimum version. See the dedicated article for update instructions. Use the `unpivot(leadField, valueField)` expression to turn values from a related table into separate columns. Provide two fields from the same related table: the lead field comes first, and each of its distinct values becomes a column name; the value field comes second, and its values populate those columns. Both plain column names and joined fields are accepted - for example, `unpivot(Attribute Name, Value)` or `unpivot(Dimension Set ID->Dimension Name, Dimension Set ID->Dimension Value Name)`. By default, every distinct value of the lead field becomes a column. To return only specific values, list them in parentheses after the lead field. Because the `Select` argument is itself a text string, each value must be wrapped in doubled double quotes: `unpivot(Dimension Set ID->Dimension Code(""businessgroup"",""customergroup""), Dimension Set ID->Dimension Value Code)` Only the listed values produce columns. The values must match those of the lead field you chose - dimension codes for `Dimension Code`, dimension names for `Dimension Name`. This form works with any lead field, not only dimensions, and has no extension requirement beyond the join support described above. List each value separately. Combining them into one quoted value - for example, `Dimension Code(""businessgroup,customergroup"")` - drops the entire `unpivot` expression from the query without reporting an error, and the expected columns are silently missing from the results. Aggregate a column by wrapping it in an aggregate function: `SUM()`, `COUNT()`, `AVERAGE()`, `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. Filtering expressions such as `filter()` are not supported in the `Select` argument - place all filter conditions in the `Filter` argument.
`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 cannot be applied to aggregated columns or joined fields `Limit` - defines a limit of records returned provide the max. number of records to be returned default limit: 20000 records provide the number directly as the `Settings` argument when `Limit` is the only setting (for example, `10`) `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:

Return only selected dimensions as columns

=BC.QUERYTABLE("bc","Sales Line",,"Document No.,Description,unpivot(Dimension Set ID->Dimension Code(""businessgroup"",""customergroup""),Dimension Set ID->Dimension Value Code)")

Description: Returns Sales Line records with only the BusinessGroup and CustomerGroup dimensions as columns, instead of every dimension in the line's dimension set.