SI.XQUERY

Applies to

Velixo NX 2025.4 or higher
Sage Intacct

^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

CODE

=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. You can also use the SI.XQUERYFILTER function to build filters for SI.XQUERY.
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 sorted by the selected fields in the order they appear. Limit: The maximum number of rows to return. For example, 200. By default, the size of the result set is not limited. Please use Limit along with Sort to ensure your result set is ordered correctly before slicing. Offset: The number of rows to skip from the beginning. This is useful for paging through extensive results. We recommend sorting by a unique column (for example, `id`) so the data appears in a predictable order. Default value: `0` Please use Offset along with Sort to ensure your result set is ordered correctly before slicing. 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: CODE `=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.}

Снимок экрана 2025-04-24 161949.png — ^{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:}

CODE

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

^{Now, the entire query will look this way:}

CODE

=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})}

CODE

=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}))