SI.QUERYFILTER Function
Created by Harry Lewis, Modified on Mon, 16 Oct 2023 at 06:49 PM by Harry Lewis
Applies to:
- Velixo NX (version 2023.10 and higher)
- Sage Intacct
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)
Parameters
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 Nth 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
Example 1 - null
=SI.QUERYFILTER("Sage","Employee","Name","null")
Result
(Name IS NULL)
Example 2 - not null
=SI.QUERYFILTER("Sage","Employee","Name","not null")
Result
(Name IS NOT NULL)
Wildcards
- * - Matches anything up to, or after the characters
- ? - Matches a single character
(Name LIKE 'M_k%')
We can also use "Name", "not like Mi*", to get:
(Name NOT LIKE 'Mi%')
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=''
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
Percentages must be passed as a value from 0 to 1. An Excel cell formatted as % should returns 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
=SI.QUERYFILTER(A1,"GLENTRY","WHENCREATED","> 2020-04-27 23:19:10")
Boolean values
Boolean values can be specified as:
- Excel native boolean:
=SI.QUERYFILTER($E$4,"Employee","POSTACTUALCOST",TRUE)
- A string:
=SI.QUERYFILTER($E$4,"Employee","POSTACTUALCOST","true")
- A zero or one:
=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)
Was this article helpful?
That’s Great!
Thank you for your feedback
Sorry! We couldn't be helpful
Thank you for your feedback
Feedback sent
We appreciate your effort and will try to fix the article