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

The resulting queries are translated to a BC.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 BC.QUERY function.

Then choose the API you want to use to communicate with Business Central. You can choose between Common, Web Services, and Velixo, like in the case of BC.QUERY formulas.

image-20260319-183728.png


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-20260319-183500.png
image-20260319-183421.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-20260319-183916.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 BC.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-20260319-184129.png
image-20260319-184231.png


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

image-20260319-184534.png

Related objects and their fields are available at the bottom of the list.

image-20260319-184715.png

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

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

  • Show column headers - toggle to include column headers.

  • Case insensitive toggle - checked by default

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