ACU.QUERY

Created by Velixo Team, Modified on Thu, 31 Jul at 9:06 AM by Velixo Team

Applies to

Velixo NX 2025.7 or higher
Acumatica, MYOB Acumatica, Cegid XRP Flex, Haufe X360, Jamis Prime

TABLE OF CONTENTS

Introduction
Syntax
- Parameters
Output
Examples

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

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

Parameters

Parameter	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 parameter, 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 parameter is provided, the header will always be included in the resulting Excel table and this parameter 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
OutputTableAddress	Optional	If the parameter is specified, the function output is represented as an Excel table, and the first column in the Select parameter is populated by this address. See the Table Mirroring article for details. If the parameter 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 parameter or with all columns if the Select parameter is omitted.

Examples

First 10 records

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

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

Result:

Filter

=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.

=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 parameter (cell A2).

Result: