BC.QUERYTABLE

Overview

Use the BC.QUERYTABLE function to return data that matches a filter expression directly from queryable Business Central tables.

Syntax

CODE

=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.
`Select`	Optional	Comma-separated list of table columns to be included in the resulting dataset. If omitted, all table columns will be returned.
`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
`TableOutputCell`	Optional	Specify the target cell address to return results in an Excel table. See the Table Mirroring article for details.

Example

CODE

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