Applies to:

• Velixo NX
• Sage Intacct

TABLE OF CONTENTS

• Overview
• Layout and usage
  • Fields
  • Filters
  • Advanced
• Examples
  • Return the first 10 records
  • Filter by field value
  • Send output to an Excel table, set column order

Overview

The Query 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 SI.QUERY formula which returns the contents of a specified Sage 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).

You can also choose the Sage Intacct connection you want to use if multiple are available. The first active Sage Intacct 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 SI.QUERY function. 

Next, choose a Sage Intacct 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.

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

When selecting individual fields, you can choose to fetch raw values from the ERP (NONE) or aggregate the results of a given field using one of the following Excel functions: SUM, AVERAGE, COUNT, MIN, MAX.

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 SI.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 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
• Case insensitive toggle - checked by default
• Return private objects toggle - returns objects contained in private Sage Intacct entities (organizations)
• 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.

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 SI.QUERY formula:

=SI.QUERY("Sage","GLBATCH",,,,{"CaseInsensitive",TRUE;"Limit",10})

Filter by field value

• Tax detail is selected as the Object
• TAXAUTHORITY, VALUE, RECORD_URL, GLACCOUNT, DESCRIPTION, and STATUS are selected as Fields
  
• The results are filtered to include only the records where TAXTYPE is equal to Purchase
  

Description:

Returns records for the selected fields for the object Tax detail, where the TAXTYPE field is set to Purchase.

Result:

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

=SI.QUERY("Sage","TAXDETAIL",TEXTJOIN(" ",TRUE,SI.QUERYFILTER("Sage","TAXDETAIL","TAXTYPE","Purchase")),"TAXAUTHORITY,VALUE,RECORD_URL,GLACCOUNT,DESCRIPTION,STATUS",,{"CaseInsensitive",TRUE})

Send output to an Excel table, set column order

• Cost code is selected as the Object
• CUSTOMERNAME, PROJECTNAME, PROJECTID, RECORDNO, TASKID, NAME, PROJECTBEGINDATE, AND PROJECTENDDATE are selected as Fields
  
• The results are filtered to include only the records where the PARENTTASKNAME is set to openings
  

• 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 Order columns? switch in the Advanced menu is toggled on and the columns are drag-and-dropped to the following order
  

Description:

Returns records for the selected fields for the object Cost code, where the PARENTTASKNAME field is set to openings. The results are returned as an Excel table in cell A2 with columns arranged in the desired order. 

Result:

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

=SI.QUERY("Sage","TASK",TEXTJOIN(" ",TRUE,SI.QUERYFILTER("Sage","TASK","PARENTTASKNAME","openings")),"CUSTOMERNAME,PROJECTNAME,PROJECTID,RECORDNO,TASKID,NAME,PROJECTBEGINDATE,PROJECTENDDATE",,{"CaseInsensitive",TRUE},Sheet2!F2,Sheet2!C2,Sheet2!B2,Sheet2!A2,Sheet2!D2,Sheet2!E2,Sheet2!G2,Sheet2!H2)