ACU.QUERY

Introduction

To get data from an ERP instance into your workbook, use the ACU.QUERY function, which returns the contents of a specified ERP object (as either an Excel array or - optionally - as an Excel table).

Syntax

CODE

=ACU.QUERY(ConnectionName, Object, Filter, Select, IncludeHeader, Settings, TableOutputCell)

Arguments

Argument	Required/Optional	Description
`ConnectionName`	Required	The name of the connection, as specified in the Connection Manager
`Object`	Required	Object name. For instance, `accountingPeriods` or `generalLedgerEntries` You can use ACU.EXPANDOBJECTRANGE to explore available objects.
`Filter`	Optional	OData4 query based on the fields in the object. OData4 operators are supported, including: `eq, ne, gt, ge, lt, le, has, in, and, or, not, add, sub, mul, div, divby, mod, startswith, endswith`.
`Select`	Optional	Comma-separated list of columns to be included in the resulting dataset. Use the ACU.OBJECTDEFINITION function to get the list of the object fields. If you omit this argument, all the columns from the object will be returned.
`IncludeHeader`	Optional	If `TRUE`, the column headers will be included in the result set array. If the `OutputTableAddress` argument is provided, the header will always be included in the resulting Excel table and this argument will be ignored. Default value: `TRUE`
`Settings`	Optional	You can either enter a number (e.g. `200`) to set how many rows you want or use a list of settings (key-value pairs) to specify advanced query settings. 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, `accountNo:DESC`). Columns without `:DESC` are sorted ascending. Limit: The maximum number of rows. For example, 200. If other 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, `entryNumber`) 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”` and `“sales”` all match the same data). Default value: `FALSE`
`TableOutputCell`	Optional	If the argument is specified, the function output is represented as an Excel table, and the first column in the `Select` argument is populated by this address. See the Table Mirroring article for details. If the argument is omitted, the result is returned as an array.

Output

The function returns a spill range (if OutputTableAddres is omitted) or an Excel table (if OutputTableAddress is specified) with the columns specified in the Select argument or with all columns if the Select argument is omitted.

Excel Online

Loading large datasets with the GI() function 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

First 10 records

CODE

ACU.QUERY("Acumatica","GLBatch",,,,10)

Description: Returns the top 10 records returned by the GLBatch object.

Result:

Filter

CODE

=ACU.QUERY("Acumatica","Contact","CLASSID eq 'LEADBUS'","CONTACTID,FULLNAME,EMAIL")

Description:

Returns the CONTACTID, FULLNAME and EMAIL fields from the CONTACT object where the CLASSID field is set to LEADBUS:

Result:

Send output to an Excel table

Other examples of creating Excel tables can be found in Table Mirroring.

CODE

=ACU.QUERY("Acumatica","PMLaborCostRate",,"RECORDID,EMPLOYEEID,RATE,CURYID,REGULARHOURS",,,A2)

Description:

Instead of displaying the results of the query starting in the cell containing the ACU.QUERY function, the function displays the specified fields from the Project object in an Excel data table with its origin in the cell specified by the OutputTableAddress argument (cell A2).

Result: