BC.QUERYFILTER

Overview

The BC.QUERYFILTER function returns a string that can be used as input for the Filter argument in the BC.QUERY function.

Syntax

CODE

=BC.QUERYFILTER(
  ConnectionName,
  Object,
  CaseInsensitive,
  Api,
  Column,
  Criteria,
  AnotherColumnOrCriteria1,
  AnotherColumnOrCriteria2,
  …
)

Arguments

Argument	Required/Optional	Description
`ConnectionName`	Required	The name of the connection, as specified in the Connection Manager
`Object`	Required	Business Central object name. For instance, `balanceSheets`. Please use BC.EXPANDOBJECTRANGE to explore available objects.
`CaseInsensitive`	Optional	Produce a filter insensitive to the case of text values. Possible values: `TRUE` and `FALSE` Default value: `FALSE`
`Api`	Optional	API to be referenced for Object names. The accepted predefined values are: `common` - Common API v2 (default) `web-service` - OData endpoints exposed per tenant `Velixo` - endpoints exposed via the Velixo AL extension Use the following syntax to use a published AL Extension: {publisher}/{group}/{version} - a fragment of the extension's URL `Velixo` is an alias for the most recent version of the Velixo AL extension
`Column`	Required	Name of the object field you wish to filter. Use BC.OBJECTDEFINITION to explore available fields.
`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 object used for filtering.
`AnotherColumnOrCriteria2`	Optional	Criterion for the second filter.
…

Example

You need a filter that returns all debit entries over $4000 posted after 1 Jan 2024.

First, find the object name. You can use the BC.EXPANDOBJECTRANGE function to find the name of the WIP period object. It is generalLedgerEntries.

Then, you can use the BC.OBJECTDEFINITION function to retrieve all properties available for the object. In this case, you are interested in the debitAmount and postingDate fields.

Providing this information, you can arrive at the following formula:

CODE

=BC.QUERYFILTER(
  "BC",
  "generalLedgerEntries",
  ,
  ,
  "debitAmount",
  ">= 4000",
  "postingDate",
  ">= 2024-01-01"
)

When executed, it returns the following filter, ready to use with the BC.QUERY function:

Below, you can see the filter used with the BC.QUERY function: