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 your ERP.

The resulting queries are translated to an 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).

Alternatively, right-click the target cell for your query and choose Query Builder from the Velixo 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.

Click the Filter button to choose to search by UI names, API names or both.

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.

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 depending on 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.

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

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

You can then drag the selected fields to achieve the desired column order.

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.

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 query result - choose fields used for sorting the results in ascending (ASC) or descending (DESC) order

• Show column headers - toggle to include column headers. Returns user-friendly field/property names.

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

• Split Column headers - Writes the list of selected columns into the cell you specify and makes the main QUERY formula use that cell instead of inlining the full columns expression. Use this when your selected fields make the query formula very long, or you prefer to keep the columns list in a separate “helper” cell for easier maintenance.

• Split Filter expression - Writes a filter formula into the cell you specify and makes the main formula use that cell as its Filter argument. Use this when your filter is complex/long, or you want to manage filter logic separately from the main query formula.

Examples

Return the first 10 records

• The GL Batch object is selected

• All fields for the object are included

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

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

Filter by field value

• AP Tax Detail is selected as the Object

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

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

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

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.

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

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

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

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)