Created by Harry Lewis, Modified on Mon, 16 Oct 2023 at 06:49 PM by Harry Lewis
- Velixo NX (version 2023.10 and higher)
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
SI.QUERYFILTER(Connection, Object, Column, Criteria, Column, Criteria, ..., Column, Criteria)
The SI.QUERYFILTER function uses the following parameters:
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.|
Example 1 - null
(Name IS NULL)
Example 2 - not null
(Name IS NOT NULL)
(Name LIKE 'M_k%')
(Name NOT LIKE 'Mi%')
Excel treats empty cells as the value 0. As such:
In order to obtain a truly empty value, an apostrophe (') should be used in the criteria cell. Thus:
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
Date and Timestamp
Support is provided for the following formats:
- yyyy-MM-dd HH:mm:ss
- MM/dd/yyyy HH:mm:ss
- Excel dates
- ISO format yyyy-MM-ddTHH:mm:ss
=SI.QUERYFILTER(A1,"GLENTRY","WHENCREATED","> 2020-04-27 23:19:10")
Boolean values can be specified as:
- Excel native boolean:
- A string:
- A zero or one:
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?
Thank you for your feedback
Sorry! We couldn't be helpful
Thank you for your feedback
We appreciate your effort and will try to fix the article