SI.QUERYFILTER Function

Created by Harry Lewis, Modified on Mon, 16 Oct 2023 at 06:49 PM by Harry Lewis

Applies to:

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

TABLE OF CONTENTS

Syntax

SI.QUERYFILTER(Connection, Object, Column, Criteria, Column, Criteria, ..., Column, Criteria)

The SI.QUERYFILTER function uses the following parameters:

Parameter	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	A single value, an array of values, or 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 parameter 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 N^th column.

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

Result

(Name IS NULL)

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

Result

(Name IS NOT NULL)

Sage supports two wildcard caracters:

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

(Name LIKE 'M_k%')

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

(Name NOT LIKE 'Mi%')

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

In the same vein, when working with date columns, specifying an empty cell will result in:

(BIRTHDATE = '12/30/1899')

Which equates to 0 in the Excel date system.

Percentages must be passed as a value from 0 to 1. An Excel cell formatted as % should returns values like this.

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

Support is provided for the following formats:

For example:

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

Boolean values can be specified as:

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

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

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

All the examples above should result in:

(POSTACTUALCOST = true)

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

(POSTACTUALCOST = false)