SI.QUERY
Created by Harry Lewis, Modified on Thu, 5 Sep at 1:54 PM by Harry Lewis
Applies to:
- Velixo NX
- Sage Intacct
The Velixo NX SI.QUERY function returns the contents of the specified Sage object.
The available objects are listed in the Sage API Reference: API Reference | Sage Intacct Developer. You can also retrieve a list of all available objects using the Velixo NX SI.EXPANDOBJECTRANGE function. |
TABLE OF CONTENTS
Syntax
This is the syntax for this function:
=SI.QUERY(ConnectionName, Object, Filter, Select, IncludeHeader, Top, OutputColumns1, OutputColumns2, ... OutputColumnsN)
Parameters
The SI.Query function has these Parameters:
Parameter | Required/Optional | Description |
Connection | Required | The name of the connection, as specified in the Connection Manager |
Object | Required | The Query and List Legacy entities which can be accessed are listed in API Reference | Sage Intacct Developer Note: Although listed as a valid object, Sage Intact does not support querying the GLRESTRICTIONRELEASE object. |
Filter | Optional
This parameter is | SQL-like query based on fields on the object. The following operators are supported:
Including characters that a not allowed within XML must be properly encoded, and single quotes must be proceeded by a backslash: (
Joins are not supported. |
Select | Optional | Comma-separated list of columns to be included in the resulting dataset. If this parameter is empty, all the columns from the object will be returned. A column may come from:
Notes:
|
IncludeHeader | Optional | TRUE or FALSE, indicating if should column headers be included in the dataset. |
Top | Optional | Returns the number of records specified. If a value is specified, the results are sorted by Record Number (RECORDNO). Otherwise, they are not sorted. |
OutputColumn1 | Optional | Optionally, specifies the cell on the worksheet where the data of the first column specified in the Select parameter is to be displayed as part of an Excel table. |
OutputColumn2 | Optional | Specifies the cell of the worksheet where the data of the second column specified in the Select parameter is to be displayed as part of an Excel table. Note: Must be on the same row as OutputColumn1 |
... | ||
OutputColumnN | Optional | Specifies the cell of the worksheet where the data of the Nth column specified in the Select parameter is to be displayed as part of an Excel table. Note: Must be on the same row as OutputColumn1 and every other OutputColumn. |
Excel Online
Important: Loading large datasets with SI.QUERY() is not performant in Excel Online due to the limitations of the Excel platform in the browser. If your dataset contains more than approximately 100,000 records, we strongly recommend using a desktop version of Excel 365 for Windows or Mac OS.
Examples
Example 1 - Top 10 records
SI.QUERY("Sage","GLBatch",,,,10) Description: Returns the top 10 records returned by the GLBatch object.
Result: ![]()
|
Example 2 - Filter example 1
SI.QUERY("Sage","TaxSolution", "TaxMethod='VAT or GST'", "SolutionID,TaxMethod")
Description: Returns the SolutionID and TaxMethod fields from the TaxSolution object where the TaxMethod field is set to VAT or GST:
Result: ![]()
|
Example 3 - Filter example 2
=SI.QUERY("Sage","TaxDetail","DESCRIPTION LIKE('%Sales%')", "RecordNo,DetailID,Description,TaxType,Value,Include, GLAccount,TaxAuthority,Status")
Description: Returns the specified fields (in the order specified) from the TaxDetail object where the Description field contains the case-sensitive substring Sales
Result:
![]()
|
Example 4 - Send output to an Excel table
=SI.QUERY("Sage","Project",,"RECORDNO,PROJECTID,PROJECTTYPE,NAME",TRUE,,A5,B5,D5,C5) Description: Instead of displaying the results of the query starting in the cell containing the SI.QUERY function, the function Cell A2 displays the specified fields from the Project object in an Excel data table located in the cells specified by the OutputColumns parameters (cells A5, B5, D5, and C5) |
Example 5 - Include columns from related objects
=SI.QUERY("Sage", "Project", (PROJECTID='22-002'), Description: This SI.QUERY example queries the PROJECT object and returns 3 columns for a single, specified ProjectID. The third specified field (NAME) originates from the EMPLOYEEPOSITION object that is related to the MANAGER object which is then related to the PROJECT object (demonstrating multi-level "lookups"). Notes:
Here are the relationships of the related objects for the above example:
![]()
![]()
|
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