ACU.QUERY | Velixo

Overview

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

You can use the Query Builder functionality to construct formulas visually.

Syntax

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

Arguments

Argument	Required/Optional	Description
`ConnectionName`	Optional	Provide one of the following values: Connection name as defined in Connection Manager. Velixo range expression defining a set of connection names. An array of settings as described in the Multiple connections per formula article. We recommend using the VX.SETTINGS function to construct the array. OR Omit the argument to return results for all compatible connections with default aggregation settings.
`Object`	Required	Name of the Acumatica DAC object to query. You can enter either the full name, including the namespace (for example, `PX.Objects.CR.Contact`), or the short name (for example, `Contact`). The name is not case-sensitive. If a short name matches objects in more than one namespace, the function returns an error listing the matching objects, for example: The DAC reference 'ARAdjust' is ambiguous between 'PX.Objects.AR.ARAdjust' and 'PX.Objects.CA.Light.ARAdjust'. Please specify a fully qualified name. Use the full name instead. To find the full names of objects that share a short name, use ACU.EXPANDOBJECTRANGE with a `like %.ShortName` expression. If no object matches the name, the function returns an error such as The object 'GLBatch' is not found at 'Acumatica'. Use ACU.EXPANDOBJECTRANGE to check the object name. For querying related objects, use the `navigationProperty.relatedFieldName` syntax. For example, the query `=ACU.QUERY(,"PX.Objects.AR.ARAdjust",,"InvoiceID,CustomerByCustomerID.AcctName")` will return the `InvoiceID` field from the queried object, and the `AcctName` field from the navigation property `CustomerByCustomerID`. You can use ACU.EXPANDOBJECTRANGE to explore available objects, and ACU.OBJECTDEFINITION to check their fields and navigation properties. Some objects return results only when you specify the `Filter` argument. See Objects that require a filter.
`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`. Use the ACU.QUERYFILTER function to create filter formulas ready to use with ACU.QUERY. Required for some objects. See Objects that require a filter.
`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. Rows where the selected columns have no value are included in the results - see Rows with no value in selected columns.
`IncludeHeader`	Optional	Accepted values: `TRUE`, `FALSE`, `CAPTION` Default value: `TRUE` If set to`TRUE`, the column headers will be included in the result set array, and display object names. If set to `CAPTION`, the headers will be included and display a user-friendly name. For example, the nested object `TermsByTermsID.CreatedByID` will be displayed as `Terms → Created By`. If the `OutputTableAddress` argument is provided, the header will always be included in the resulting Excel table, and this argument will be ignored.
`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. When you enter a number, only the row limit is set – all other settings keep their default values. 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`. 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. To make a filter case-insensitive, build it with the ACU.QUERYFILTER function and set its `CaseInsensitive` argument to `TRUE`. ACU.QUERY has no case-sensitivity setting of its own.
`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.

Rows with no value in selected columns

The function returns all matching rows, including rows where the selected columns have no value. Blank cells in the results are expected and are not an error. A row appears entirely blank only when none of the selected columns has a value for that record.

Because every matching record produces a row, the spilled range sizes to the number of matching records, not to the number of populated values. Account for this in any downstream formula or conditional formatting that depends on the size of the result.

To exclude rows with no value in a column, add a condition to the Filter argument.

=ACU.QUERY( 
    "Acumatica", 
    "Contact", 
    ACU.QUERYFILTER("Acumatica","Contact",,"EMAIL","<>null"), 
    "CONTACTID,EMAIL" 
)

Description: Returns CONTACTID and EMAIL from the Contact object, excluding contacts with no email address. ACU.QUERYFILTER renders the <>null criteria as the OData clause EMAIL ne null.

Objects that require a filter

The following Acumatica objects return one row per document for each date. Querying them without a filter can generate an extremely large result that Acumatica can't return in time. To prevent this, ACU.QUERY returns #N/A with the following message if you query these objects without the Filter argument:

A filter on 'SubmissionDate' is required for this object

Object	Required filter field
`PX.Objects.AP.APAdjustedBalanceAtDate`	`SubmissionDate`
`PX.Objects.AP.APAdjustingBalanceAtDate`	`SubmissionDate`
`PX.Objects.AP.APInvoiceRetainageBalanceAtDate`	`SubmissionDate`
`PX.Objects.AR.ARAdjustedBalanceAtDate`	`SubmissionDate`
`PX.Objects.AR.ARAdjustingBalanceAtDate`	`SubmissionDate`
`PX.Objects.AR.ARInvoiceRetainageBalanceAtDate`	`SubmissionDate`

The check applies whether you enter the full or the short object name. Query Builder shows the same message when you select one of these objects and leave the filter empty.

To query these objects, add a condition on the SubmissionDate field to the Filter argument. A condition on a single date returns one row per document. A date range returns one row per document for each date in the range.

Even with a date condition, a query that matches many documents can return #N/A with the message The query returned no results. To avoid this, narrow the filter further, for example with a condition on the RefNbr field.

To check whether an object requires a filter, use ACU.EXPANDOBJECTRANGE with the IncludeDetails argument set to TRUE.

Query a single document on a date

=ACU.QUERY(
    "Acumatica",
    "ARAdjustedBalanceAtDate",
    "SubmissionDate eq 2026-06-30 and RefNbr eq 'AR007527'",
    "DocType,RefNbr,SubmissionDate,LineTotal,CuryLineTotal"
)

Description: Returns the row for the document with reference number AR007527 on 30 June 2026.

Result:

Query a date range

=ACU.QUERY(
    "Acumatica",
    "ARAdjustedBalanceAtDate",
    "SubmissionDate ge 2026-06-01 and SubmissionDate le 2026-06-03 and RefNbr eq '002266'",
    "DocType,RefNbr,SubmissionDate,LineTotal,CuryLineTotal"
)

Description: Returns one row for each date from 1 June to 3 June 2026 for every document with the reference number 002266. In this example, two documents share that reference number (types CRM and PMT), so the result has six rows.

Result:

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

=ACU.QUERY(
    "Acumatica",
    "PX.Objects.GL.Batch",
    ,
    ,
    ,
    10
)

Description: Returns the first 10 records from the PX.Objects.GL.Batch object. The full name is required here, because the short name Batch also matches PX.Objects.GL.ADL.Batch.

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:

ACU.QUERY returning CONTACTID, FULLNAME, and EMAIL for contacts with CLASSID equal to LEADBUS

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

Result:

Check available fields for the primary and related objects using the ACU.OBJECTDEFINITION function.
Create a formula that contains fields from the primary as well as a related object, for example:
```
=ACU.QUERY(
  ,
  "PX.Objects.AR.ARAdjust",
  ,
  "InvoiceID,CustomerByCustomerID.AcctName"
)
```

Result:

ACU.QUERY returning InvoiceID and AcctName from PX.Objects.AR.ARAdjust with CustomerByCustomerID navigation property

Description:

Returns values from the field InvoiceID of the object PX.Objects.AR.ARAdjust, and values from the field AcctName of the object PX.Objects.AR.Customer identified by the navigation property CustomerByCustomerID.

Navigation properties for related objects are listed in the Type column, at the bottom of the list returned by ACU.OBJECTDEFINITION.

Overview

Syntax

Arguments

Output

Rows with no value in selected columns

Objects that require a filter

Query a single document on a date

Query a date range

Excel Online

Examples

First 10 records

Filter

Send output to an Excel table

Return fields from a related object