Breadcrumbs

Query Builder

Overview

The Query Builder feature is an intuitive, step-by-step visual tool for constructing query formulas to extract data from an instance of Sage Intacct.

The resulting queries are translated to the ACU.QUERY formula which returns the contents of a specified object - either as an Excel array or an Excel table.

Layout and usage

To open the Query side panel, select a cell where you want to place the created formula and click the Insert button in the Velixo NX ribbon and select Query (you can also select a different cell once the panel is open).

image-20250913-121543.png

You can also use Query Builder to edit existing queries created with the tool. Select the cell containing the query you’d like to edit and click Insert → Query from the Velixo NX Excel menu.

You can also choose the connection you want to use if multiple are available. The first active connection on the Connection Manager list will be selected by default. The drop-down field will be inactive if your workbook is only connected to a single connection. This functionality corresponds to the Connection parameter in the ACU.QUERY function.

Next, choose an object from which you want to retrieve data from the list in the pop-up panel. You can also use the search bar to look for available objects.

image-20260205-112603.png
image-20260205-112910.png


Then, you can insert the query into your workbook using the Insert button or customize your query using the adjustments in the Fields, Filters, and Advanced tabs described below.

image-20260205-125733.png


Fields

The Fields tab enables you to include individual fields in the results of your query. This functionality corresponds to the Select parameter in the ACU.QUERY function.

The available fields differ by the selected object. All available fields are selected by default. To select individual fields, uncheck the Select all checkbox and check the fields you wish to include from the list in the pop-out panel. The list contains available fields for the selected object as well as related objects.

image-20260205-130259.png
image-20260205-132033.png


When choosing fields, use the chevron icon to expand the list of related objects and their available fields.

image-20260204-121215.png

Objects that have more than one clear navigation path to exactly one related object are available at the bottom of the list.

image-20260204-121706.png


Filters

The Filters tab lets you create filters for your selected fields, arrange filters into groups, and determine AND/OR relationships between filters and filter groups. This functionality corresponds to the Filter parameter in the ACU.QUERY function. You can use values, cell and array references, as well as named items for filtering.

https://s3.ca-central-1.amazonaws.com/cdn.velixo.com/helpdesk/mBcRli_cUtuuXKtdAfUzdZRGYVzGwaAZDg.png


Advanced


This tab contains the following options related to the layout of the query results:

  • Return query result - choose whether the results are returned As a spill range or In an Excel Table. The latter also lets you select the target address for your Excel table

  • Excel table address - select the address where the top left cell of the Excel table will be placed

  • Order columns - toggle to enable reordering Excel table columns. Drag and drop tiles with field names to the desired order

  • Order query result - choose fields used for sorting the results in ascending (ASC) or descending (DESC) order

  • Show column headers toggle

  • Limit - declare the number of records to be displayed

  • Offset - declare the number of records to be skipped, starting with the first record. This is useful for paging with extensive results or skipping a number of top results.

image-20260205-132650.png


Examples

Return the first 10 records


  • The GL Batch object is selected

  • All fields for the object are included

image-20260209-144543.png



  • The record Limit in the Advanced tab is set to “10”

image-20260209-145055.png



Description:

Returns the first 10 records with all fields for the GL Batch object.

Result:

The query above results in the following ACU.QUERY formula:

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


image-20260209-145712.png



Filter by field value


  • AP Tax Detail is selected as the Object

  • TaxRate, TaxID, RefNbr, LineNbr, and TaxAmt are selected as Fields

image-20260209-150545.png



  • The results are filtered to include only the records where TaxRate is equal to Purchase

image-20260209-150910.png



Description:

Returns records for the selected fields for the object AP Tax Detail, where the TaxRate field is set to 5.


Result:

The query above results in the following ACU.QUERY formula:

=ACU.QUERY("Acumatica","PX.Objects.AP.APTax",ACU.QUERYFILTER("Acumatica","PX.Objects.AP.APTax",,"TaxRate",5),"TaxRate,TaxID,RefNbr,LineNbr,TaxAmt")


image-20260209-151813.png



Send output to an Excel table, set result order


  • Cost code is selected as the Object

  • IsActive, CostCodeCD, CostCodeID, CreatedByID, Description, NoteText, and tstamp are selected as Fields.

image-20260209-153639.png



  • The results are filtered to include only the records where the IsActive object is set to true

image-20260209-152050.png



  • The Return query result setting in the Advanced tab is set to Into an Excel table, with cell A2 selected as the target address

image-20260209-152652.png



  • The results are set to be displayed in descending order, based on the values in the LastModifiedDateTime field.

image-20260209-152636.png


Description:

Returns records for the selected fields for the object Cost code, where the IsActive field is set to true. The results are returned as an Excel table in cell A2 with results arranged in descending order based on the values in the field LastModifiedDateTime.

Result:

The query above results in the following ACU.QUERY formula:

=ACU.QUERY("Acumatica","PX.Objects.PM.PMCostCode",ACU.QUERYFILTER("Acumatica","PX.Objects.PM.PMCostCode",,"IsActive","true"),"IsActive,CostCodeCD,CostCodeID,CreatedByID,Description,NoteText,tstamp",,{"Sort","LastModifiedDateTime:DESC"},Sheet3!A2)


image-20260209-154220.png