SI.QUERYFILTER

Overview

The SI.QUERYFILTER function returns a STRING that can be used as input for the FILTER argument in the SI.QUERY function.

Syntax

CODE

=SI.QUERYFILTER(
    Connection,
    Object,
    Column1, 
    Criteria1,
    Column2, 
    Criteria2,
    ...
    ColumnN, 
    CriteriaN
)

Arguments

The SI.QUERYFILTER function uses the following arguments:

Argument	Required/Optional	Description
`Connection`	Required	The name of the connection as configured in the Connection Manager
`Object`	Required	The name of the Sage Intacct object to filter
`Column1`	Required	The name of the first column to which criteria is to be applied
`Criteria1`	Required	This can be: a single value an array of values a range of cells containing the criteria for the first column. Each value should contain an Excel comparison operator (<>, >, <, =, >=, <=) followed by a criteria value for the column, for example, ">=42". If the operator is not explicitly specified, the equality operator "=" is assumed. In case this argument is an array or a range of cells, each of those will be considered an alternative, and will be joined via a logical "OR"
`Column2`	Optional	The name of the first column to which criteria is to be applied
`Criteria2`	Optional	A single value, an array of values, or a range of cells containing the criteria for the second column.
...
`ColumnN`	Optional	The name of the N^th column to which criteria is to be applied
`CriteriaN`	Optional	A single value, an array of values, or a range of cells containing the criteria for the Nth column.

Examples

Null values

CODE

=SI.QUERYFILTER(
    "Sage",
    "Employee",
    "Title",
    "null"
)

(example using cell references)

Result

CODE

(TITLE IS NULL)

Not null values

CODE

=SI.QUERYFILTER(
    "Sage",
    "Employee",
    "Title",
    "not null"
)

(example using cell references)

Result

CODE

(TITLE IS NOT NULL)

Multiple Values

When we want the data from an object where a field can be set to one of multiple values, we can use a reference to a range of cells containing those values:

or, if the values are returned by an array function, we can use array referencing:

Wildcards

Sage supports two wildcard characters:

* - Matches anything up to, or after the characters
? - Matches a single character

If a value contains one of these characters, eg. "Name", "M?k*", we get:

CODE

(Name LIKE 'M_k%')

We can also use "Name", "not like Mi*", to get:

CODE

(Name NOT LIKE 'Mi%')

All Values

When used by itself, an asterisk (*) is interpreted to mean the same thing as "is not null".

For example:

CODE

=SI.QUERYFILTER(
    A1,
    "Location",
    "LocationID",
    "*"
)

Using the asterisk and question mark (*?) can be used to match and any and all values.

For example:

CODE

=SI.QUERYFILTER(
    A1,
    "Location",
    "LocationID",
    "*?"
)

In practice, this results in a non-filter (since nothing is really being filtered).

e.g.,

STATUS is not included in the resulting filter

Empty values

Excel treats empty cells as the value 0. As such:

If the user specifies for column name and criteria, respectively, "ColumnName", A1
and A1 is an empty cell
The function will return ColumnName='0' (or ColumnName =0, depending on the data type)

In order to obtain a truly empty value, an apostrophe (') should be used in the criteria cell. Thus:

If the user specifies for column name and criteria, respectively, "ColumnName", A1
And A1 contains the value '
The function will return ColumnName=''

Similarly, when working with date columns, specifying an empty cell will result in:

CODE

(BIRTHDATE = '12/30/1899')

Which equates to 0 in the Excel date system.

Less Than or Greater Than comparisons

CODE

=SI.QUERYFILTER(
    "Sage",
    "Employee",
    "Birthdate",
    ">=01/01/2000"
)

(example using cell references)

Result

CODE

(BIRTHDATE >="1/1/2000")

Specific Data Types

Percentages

Percentages must be passed as a decimal value between 0 and 1. An Excel cell formatted as % should return values like this.

Currency

Currency values should be passed as a number only, without any monetary symbols

Date and Timestamp

Support is provided for the following formats:

yyyy-MM-dd HH:mm:ss
yyyy-MM-dd
MM/dd/yyyy HH:mm:ss
MM/dd/yyyy
Excel dates
ISO format yyyy-MM-ddTHH:mm:ss

For example:

CODE

=SI.QUERYFILTER(
    A1,
    "GLENTRY",
    "WHENCREATED",
    "> 2020-04-27 23:19:10"
)

(example using a cell reference)

Results

CODE

(WHENCREATED > '04/27/2020 23:19:10')

Boolean values

Boolean (true/false) values can be specified as:

Excel native Booleans:

CODE

=SI.QUERYFILTER(
    $E$4,
    "Employee",
    "POSTACTUALCOST",
    TRUE
)

A string:

CODE

=SI.QUERYFILTER(
    $E$4,
    "Employee",
    "POSTACTUALCOST",
    "true"
)

A zero (false) or one (true):

CODE

=SI.QUERYFILTER(
    $E$4,
    "Employee",
    "POSTACTUALCOST",
    1
)

All the examples above should result in the same filter:

CODE

(POSTACTUALCOST = true)

Due to Excel treating empty cells as the value zero, specifying an empty cell will result in:

CODE

(POSTACTUALCOST = false)