Skip to main content
Skip table of contents

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,
  Column1,
  Criteria1,
  …,
  ColumnN,
  CriteriaN
)`

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

Column1

Required

Name of the object field you wish to filter. Use BC.OBJECTDEFINITION to explore available fields.

Criteria1

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.

ColumnN

Optional

Another name of an object field you wish to filter

CriteriaN

Optional

Another criterion or set of criteria that you want your filtered results to meet

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:

image-20250926-142118.png

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

image-20250926-142153.png

JavaScript errors detected

Please note, these errors can depend on your browser setup.

If this problem persists, please contact our support.