SI.QUERY

Parameter

Required/Optional

Description

ConnectionName

Required

The name of the connection, as specified in the Connection Manager

Object

Required

Sage Intacct object name (e.g., GLACCOUNT, INVDOCUMENT).
Use "Object (Subtype)" to retrieve column definitions for a specific subtype.

Example

SI.OBJECTDEFINITION("Sage", "INVDOCUMENT (Adjustment Decrease Value)")

Use the SI.EXPANDOBJECTRANGE function to get the list of Sage Intacct objects.

Note

Although listed as a valid object, Sage Intact does not support querying the GLRESTRICTIONRELEASE object.

Filter

Optional

This parameter is
case-sensitive by default 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. 

Including characters that are not allowed within XML must be properly encoded, and single quotes must be proceeded by a backslash:  ('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 Sage Intacct: Platform Services > Object for information about relationships)

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.

Settings

Optional

You can either enter a number to set how many rows you want or use a list of settings (key-value pairs) to specify advanced query settings.

• Number. For example, 200. The rows are sorted by RECORDNO.
• Two-dimensional array
  Pass one or more keys with their values. Each key controls a different setting:
  • Sort: A comma-separated list of columns to sort by. Add :DESC after a column name to sort it in descending order (for example, RecordNo:DESC).
    Columns without :DESC are sorted ascending.
  • Limit: The maximum number of rows. For example, 200.
  • Offset: The number of rows to skip from the start. This is useful for paging extensive results. We recommend sorting by a unique column (for example, RECORDNO) so the data appears in a predictable order.
  • CaseInsensitive: If set to FALSE, filters ignore uppercase or lowercase differences in the Filter (for example, “Sales,” “SALES,” or “sales” all match the same data). Default value: TRUE
  • ShowPrivate: If set to FALSE, excludes objects of private entities. Default value: TRUE

Example:
=SI.QUERY("Sage","TaxDetail","DESCRIPTION LIKE('%Sales%')", "RecordNo,DetailID,Description,TaxType,Value,Include,GLAccount,TaxAuthority,Status", HSTACK({"Sort";"Limit";"CaseInsensitive"}, {"RecordNo:DESC";200;TRUE}))

• Sorts rows by RecordNo descending.
• Returns up to 200 rows.
• Ignores uppercase and lowercase differences in the filter.