Breadcrumbs

BC.QUERYTABLE

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:

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.

Filtering on fields from related tables (see Select description) does not reduce the number of rows returned. 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. 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.

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.

image-20260105-150640.png 
=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")
image-20260520-131946.png

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"})
image-20260520-132217.png

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.