Portal Categories
Articles in this folder
You may like to read

SI.QUERY

Created by Harry Lewis, Modified on Mon, 15 Jul at 4:42 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

 


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

Filter

Optional

 

 

This parameter is
Case Sensitive and must be enclosed within quote marks

SQL-like query based on fields on the object. The following operators are supported: <, >, >=, <=, =, like, not like, in, not in, IS NOT NULL, IS NULL, AND, OR. 

 

Illegal XML characters must be properly encoded, and single quotes must be escaped with backslashes ('Jane\'s Deli'). 

 

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:

  • the object itself
  • A related object (refer to Platform Services > Object for information about relationships and column names)


Notes:

  • Columns from a related object must specify both the related object and the Column name, e.g. CUSTOMER.NAME 
  • It is also possible to return columns from multiple levels deep,
    e.g. MANAGER.EMPLOYEEPOSITION.NAME

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.

OutputColumn1OptionalSpecify a cell of your Worksheet to return the data of the first column specified in the Select parameter as a data table.
OutputColumn2OptionalSpecify a cell of your Worksheet to return the data of the second column specified in the Select parameter as a data table.
Must be on the same row as OutputColumn1
...

OutputColumnNOptionalSpecify a cell of your Worksheet to return the data of the Nth column specified in the Select parameter as a data table.

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 - Mirrored in Excel data table



=SI.QUERY("Sage","Project",,"RECORDNO,PROJECTID,PROJECTTYPE,NAME",TRUE,,A5,B5,D5,C5)


Description: 

The SI.QUERY in Cell A2 returns the specified fields (in the order specified by the OutputColumns) from the Project object in an Excel data table.








Example 5 - Columns from related objects


=SI.QUERY("Sage", "Project", (PROJECTID='22-002'), 
"PROJECTID,NAME,MANAGER.EMPLOYEEPOSITION.NAME")


Description: 

This SI.QUERY example queries the PROJECT object and returns 3 columns for a single, specified ProjectId. 

The third column (NAME) originates from the EMPLOYEEPOSITION object that is related to MANAGER and then PROJECT, demonstrating multi-level "lookups".



Notes: 

  • the above formula is equivalent to the formula in this screenshot, demonstrating the use of cell references for function arguments.
  • The query automatically understands which relationships to use when specifying prefixing column names with related object names.


Here are the relationships of the related objects:

  • From the PROJECT object, the Employee relationship relates to object: MANAGER


  • From the MANAGER object: the Employee position relationship goes to: EMPLOYEEPOSITION object

  • The column NAME is then used from the EMPLOYEEPOSITION Object to return the name of the Employee Position







Was this article helpful?

That’s Great!

Thank you for your feedback

Sorry! We couldn't be helpful

Thank you for your feedback

Let us know how can we improve this article!

Select at least one of the reasons
CAPTCHA verification is required.

Feedback sent

We appreciate your effort and will try to fix the article

Preview
0 of 0