BC.QUERYTABLEFILTER

Overview

Use the BC.QUERYTABLEFILTER function to create a string that is ready to use as input for the Filter argument in the BC.QUERYTABLE function.

Syntax

CODE

=BC.QUERYTABLEFILTER(
  ConnectionName, 
  Table,
  Column,
  Criteria, 
  AnotherColumnOrCriteria1,
  AnotherColumnOrCriteria2,
  ...,
  )

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.
`Column`	Required	Name of the column to be filtered. Use the BC.TABLEDEFINITION function to retrieve available column names.
`Criteria`	Required	A criterion or set of criteria that you want your filtered results to meet. Criteria can be one of the following: a value – the argument should be equal to the value an Excel comparison operator (`<>`, `>`, `<`, `=`, `>=`, `<=`), followed by a value The argument can be a single value, an array of values, or a range of cells. If the argument is an array or a range of cells, each value will be considered an alternative and joined via a logical `OR` as part of a bracketed group. Due to a limitation in Business Central, filters that utilize the `contains` operator combined with other operators are not usable with the BC.QUERY function. While a fix is underway, we recommend not using BC.QUERYFILTER with the `CaseInsensitive` argument set to`TRUE` and not-exact comparsions, which produces filters with `contains` operator combined with `tolower`. As a workaround, you can replace the `contain` operator in your filter with the `eq` operator and wrap the value in ``. For instance, in place of `contains(tolower(fieldName), 'something')`, use `tolower(fieldName) eq 'something*'`.
`AnotherColumnOrCriteria1`	Optional	Second column used for filtering.
`AnotherColumnOrCriteria2`	Optional (Required if previous argument is specified)	Criterion for the second filter.
`...`

Example

CODE

=BC.QUERYTABLEFILTER(,"Contact","City", "Vancouver")

Description: Returns a filter for the BC.QUERYTABLE function. Only returns results from the table Contact where the value in the column City is Vancouver. The filter is then used with a BC.QUERYTABLE formula.