Acumatica Business Central Sage Intacct STACK Takeoff & Estimate Velixo Classic Velixo CLI
Acumatica Business Central Sage Intacct STACK Takeoff & Estimate Velixo Classic Velixo CLI

BC.QUERYTABLE

Overview

Use the BC.QUERYTABLE function to return data directly from any table of the Business Central data model.

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.

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, including paths through several tables (see the Select description). The related field doesn't need to be in the Select argument. Rows that don't match a related-field filter are excluded from the results, whichever step of the path the filter applies to. For example, Document No.->Sell-to Customer No.->Name = filter(Adatum Corporation) on the Sales Invoice Line table returns the lines of all invoices whose sell-to customer is Adatum Corporation.

A filter through a related table can return different rows than a similar filter on the main table. In the example above, text lines that have no Sell-to Customer No. of their own are also returned, because the filter follows the invoice header.

To filter by a dimension value, use the DIMENSION() expression rather than a filter on the dimension set. DIMENSION(""AREA"") = filter(40) returns only the records whose AREA dimension value is 40; DIMENSION() = filter(40) returns records where any dimension has that value. The dimension code is enclosed in doubled double quotes because the Filter argument is itself a text string. Clauses on different dimensions combine with AND, and clauses on the same dimension with OR. See DIMENSION filter for the full syntax, the combination rules, and the extension version requirement.

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. If more than one column points to that table, the formula returns an error that lists the columns to use instead, for example: Multiple foreign-key fields in 'Sales Invoice Header' point to table 'Customer'. Use one of them explicitly: Sell-to Customer No., Bill-to Customer No.

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

A path can continue from the related table to further related tables, up to 20 -> steps. Each step uses one of the two forms above, and you can mix them in one path. For example, on the Sales Invoice Line table, Document No.->Sell-to Customer No.->Salesperson Code->Name returns, for each line, the name of the salesperson assigned to the customer of the line's invoice.

Field and table names in a path are not case-sensitive, and spaces around -> are ignored. The column header always shows the path with column names as Business Central defines them. For example, Document No.->Sell-to Customer No.->Salesperson/Purchaser->Name is shown as Document No.->Sell-to Customer No.->Salesperson Code->Name.

If a link in the path is empty, or points to a record that doesn't exist, the column returns an empty value for that row. The formula doesn't return an error.

A path with more than 20 steps returns the error '...' links through more than 20 tables. Use at most 20 linking fields in one column reference.

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.

To return dimension values as columns, use the DIMENSION() expression instead of writing the unpivot() expression yourself. DIMENSION(""AREA"") adds a single column for that dimension, and DIMENSION() adds one column for every dimension found in the results. As with unpivot(), the dimension code is wrapped in doubled double quotes. See DIMENSION filter.

When a link matches several records in the related table (a one-to-many relationship), at any step of the path, all matching values are returned in a single cell, separated by commas. Values are not deduplicated. The number of rows in the result always matches the main table: rows are not duplicated.

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

Each field in an unpivot() expression can follow one link only. A path through several tables returns the error '...' links through more than one table. Extra join keys and unpivot operands support a single linking field only.

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.

A grouping column can be a path through several tables. Rows whose path returns an empty value are grouped together in a row with an empty value.

Aggregation, including sorting by aggregated columns, depends on the Velixo Extension for Business Central version 2026.4 or later. See the dedicated article for update instructions.

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

    • when Select contains aggregated columns, you can sort only by the grouping columns and the aggregated columns in Select. Write an aggregated column the same way as in Select, for example SUM(Amount):DESC. Function and field names are not case-sensitive.

    • when Select aggregates a field, sorting by the field name alone (for example, Amount) uses the first aggregated column of that field in Select

    • cannot be applied to joined fields, at any depth: the formula returns Field(s) '...' was not found in the table '...'. You can sort by an aggregate of a joined field if the same aggregate is in Select. In the sort key, you can write the path with column names or table names.

    • cannot be applied to DIMENSION() or unpivot() columns

    • with a Velixo Extension for Business Central earlier than 2026.6, a query without joins, aggregated columns, unpivot() or DIMENSION() applies Offset and Limit before sorting, so the sorted result may not contain the top rows. Update the extension to version 2026.6 or later to avoid this.

  • Limit - defines a limit of records returned

    • provide the max. number of records to be returned

    • no default limit: if omitted, all matching records are returned

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

    ExtraJoinKeys applies to the first link of a path only. An entry that goes through more than one link returns the error '...' links through more than one table. Extra join keys and unpivot operands support a single linking field only. When a later link matches several records, all of them are returned, separated by commas.

When Select contains aggregated columns, Velixo groups the rows first, then sorts them, then applies Offset and Limit. For example, sorting by SUM(Amount):DESC with a Limit of 5 returns the five groups with the largest totals.

If a Sort column can't be used, the formula returns #VALUE!, and the Status pane lists the full error message under Errors to review. For example:

  • Cannot sort by 'MAX(Amount)' because it is not in the Select argument. Add 'MAX(Amount)' to the Select argument to sort by it

  • Cannot sort by 'Posting Date' because the query is aggregated. Add the field to the Select argument with an aggregate function, or remove it from the Sort setting

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 Invoice Line",,"Document No.,Line No.,Description,Document No.->Sell-to Customer No.->Salesperson Code->Name")

Description: Returns Sales Invoice Line records with the name of the salesperson assigned to the customer of each line's invoice. The path goes from the line to the invoice header, then to the customer, then to the salesperson.

Result:

image-20261106-103941.png
=BC.QUERYTABLE("bc","Sales Invoice Line","Document No.->Sell-to Customer No.->Name = filter(Adatum Corporation)","Document No.,Line No.,Description")

Description: Returns the lines of all posted invoices whose sell-to customer is Adatum Corporation. The customer name is read through the invoice header, so the related field doesn't need to be in the Select argument.

Result:

image-20261106-104037.png
=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.

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

=BC.QUERYTABLE(,"Item Attribute Value",,"unpivot(Attribute Name, Value)")
image-20260604-082837.png
image-20260604-082917.png

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:

image-20260707-123220.png

Return the customers with the largest sales

=BC.QUERYTABLE("bc","Sales Header",,"Sell-to Customer No., SUM(Amount)",TRUE,{"Sort","SUM(Amount):DESC";"Limit",3})

Description: Groups Sales Header records by customer and returns the three customers with the largest total Amount, largest first. Limit applies after sorting, so the result shows the top three customers with their full totals.

Result:

image-20261029-132923.png

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.

Result:

image-20260806-090646.png