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 ConnectionName argument 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 argument 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 argument 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.

Editing and switching between queries

The Query dropdown at the top of the Query Builder panel lists the queries on the active worksheet. Use it to open any of them for editing, or to start a new query, without closing Query Builder.

When you build a new query, the dropdown shows New query. When you edit an existing query, it shows the query's cell address.

The panel lists only the queries on the active worksheet. Query Builder reads the worksheet again each time you open the panel, so the list includes queries you've just added and drops the ones you've deleted. On a large worksheet, the list can take several seconds to load. If the worksheet has no queries, the panel shows There are no queries on this sheet.

Query Builder loads the selected query into the form in Edit Query mode. Make your changes, then select Update to rewrite the formula in its cell.

Queries written by hand

Query Builder can reliably load only the queries it created. A warning icon next to an entry marks a query that may have been written or edited by hand. Hover over the icon to see the tooltip: This formula may not have been created with the query builder and may not load correctly.

If Query Builder can't load a query, it shows the error Could not load the function. Editing is only supported for functions built using 'Insert Query' (not edited manually). The form keeps the query you were working on. To change such a query, edit the formula directly in its cell.

Switching with unsaved changes

If the form has unsaved changes when you select another query or Create a new query, Query Builder asks you to confirm:

• When you select another query: the dialog reads You're about to discard the current query and load the selected one. Select Continue to discard your changes and load the query, or Cancel to keep editing.

• When you select Create a new query: the dialog reads You're about to clear the query builder and start anew. Select Reset to clear the form, or Cancel to keep editing.

If there are no unsaved changes, Query Builder switches without asking.