SI.XQUERY

Created by Grigorii Kosianenko, Modified on Thu, 24 Apr at 12:19 PM by Grigorii Kosianenko

Applies to

Velixo NX 2025.4 or higher
Sage Intacct

TABLE OF CONTENTS

Introduction
Syntax
Parameters
Output
Example

Introduction

If you want to get data from your ERP, you can use SI.XQUERY function, which is a powerful tool for querying the most recently developed Sage objects that are unavailable for querying via SI.QUERY.

The function uses the familiar syntax of SI.QUERY with slight changes.

Syntax

=SI.XQUERY(ConnectionName, Object, Filter, Select, IncludeHeader, Settings, OutputColumn1, OutputColumn2, ... OutputColumnN)

Parameters

Parameter	Required/Optional	Description
ConnectionName	Required	The name of the connection, as specified in the Connection Manager
Object	Required	Sage Intacct REST object name. For instance, accounts-payable/adjustment Please use SI.XEXPANDOBJECTRANGE to explore available objects.
Filter	Optional	SQL-like query based on the fields of the object. The following operators are supported: <, >, >=, <=, =, in, contains, startswith, endswith, not, and, or. The not operator cannot be used as a negation for expressions (e.g., not (A and B) is not supported), but you can combine it with predicates from the above (e.g., not contains, not startswith are valid expressions). Use backslashes before characters other than letters, numbers, and whitespace. For example, Jane\'s Deli Joins are not supported. Use the SI.XOBJECTDEFINITION function to get the list of the object fields.
Select	Optional	Comma-separated list of columns to be included in the resulting dataset. Use the SI.XOBJECTDEFINITION function to get the list of the object fields. If you omit this parameter, all the columns from the object will be returned.
IncludeHeader	Optional	If TRUE, the column headers will be included in the result set. Default value: TRUE
Settings	Optional	The list of settings (key-value pairs) to specify advanced query settings. You can 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, id:DESC). Columns without :DESC are sorted ascending. Use the SI.XOBJECTDEFINITION function to get the list of the object fields. By default, the result set is not sorted. Limit: The maximum number of rows to return. For example, 200. By default, the size of the result set is not limited. Offset: The number of rows to skip from the beginning. This is useful for paging extensive results. We recommend sorting by a unique column (for example, id) so the data appears in a predictable order. Default value: 0 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.XQUERY("Sage","accounts-payable/adjustment","description not contains('Sales')", "id,adjustmentNumber,referenceNumber,description",, HSTACK({"Sort";"Limit";"CaseInsensitive"}, {"id:DESC";200;TRUE}))` Sorts rows by id descending. Returns up to 200 rows. Ignores uppercase and lowercase differences in the filter.
OutputColumn1	Optional	The address of the header of the first column (top left corner of the Excel table) of the result set. If the parameter is omitted, the result is returned as an array.
OutputColumn2	Optional	The address of the header of the second column of the result set. It must be on the same row as OutputColumn1.
...
OutputColumnN	Optional	The address of the header of the Nth column of the result set. It must be on the same row as OutputColumn1.

Output

The function returns a spill range (if OutputColumns are omitted) or an Excel table (if OutputColumns are specified) with the columns specified in the Select parameter or all columns if the Select parameter is omitted.

Please read about table mirroring to leverage Excel tables produced by query functions more efficiently.

Example

Say you want to get all posted WIP periods ending in 2025 Q1.

First of all, you need to find the object name.

Use the SI.XEXPANDOBJECTRANGE function to find the name of the WIP period object. It is construction-forecasting/wip-period.

If, for some reason, you have doubts regarding what object to use, please refer to the Sage Intacct documentation.

Then, you might want to look at the object definition to decide what columns you want to see in the query result. Use the SI.XOBJECTDEFINITION function to get all the information.

This is the SI.XOBJECTDEFINITION output for construction-forecasting/wip-period

fiscalYear, id, and isHistoricalImport are the values you can use in the Select, Filter, and Sort parameters, as well as other values in the ID column.

You may select key, periodName, isHistoricalImport, notes, state, and periodEndDate.

The last two will also be used for filtering. The filter will be the following:

periodEndDate >= '2025-01-01' and periodEndDate <= '2025-03-31' and state = 'posted'

Now, the entire query will look this way:

=SI.XQUERY("Sage","construction-forecasting/wip-period","periodEndDate >= '2025-01-01' and periodEndDate <= '2025-03-31' and state = 'posted'", "key, periodName, isHistoricalImport, notes, state, periodEndDate")

Finally, you can sort your query to make it prettier:
HSTACK({"Sort";"Limit"}, {"periodEndDate:DESC";3})

=SI.XQUERY("Sage","construction-forecasting/wip-period","periodEndDate >= '2025-01-01' and periodEndDate <= '2025-03-31' and state = 'posted'", "key, periodName, isHistoricalImport, notes, state, periodEndDate",,HSTACK({"Sort";"Limit"}, {"periodEndDate:DESC";3}))