GI() function

Created by Harry Lewis, Modified on Thu, 21 Nov at 2:40 PM by Harry Lewis

Applies to:

GI Module
ERP: Acumatica, CEGID, Haufe x360, JAMIS Prime, MYOB Acumatica

Velixo recommends Microsoft 365 or Excel 2021 or higher for this feature. Experimentally, Excel 2010 or higher can be used. For prerequisites and an overview of Generic Inquiries, see Introduction to Generic Inquiries.

TABLE OF CONTENTS

Description
Syntax
Parameters
- Additional parameters available with Velixo NX:
Excel Online
Examples
- Creating an array
- Creating an Excel table (Velixo NX only)
Configuration
Accommodating pre-defined filters in Acumatica
Filtering large generic inquiry datasets

Description

The GI function returns the contents of the specified generic inquiry.

Syntax

This is the syntax for this function:

=GI(Connection, Name, Filter, Select, IncludeHeader)

Parameters

This function has these Parameters:

Parameter	Required/Optional	Description
Connection	Required	The name of the connection, as specified in the Connection Manager
Name	Required	The name of the generic inquiry.
Filter	Optional	The conditions that determine which records should be selected from the inquiry. The recommended way to construct this parameter is by using the GIFILTER function. Otherwise, see Advanced Filtering in GI Functions.
Select	Optional	Comma-separated list of field names to be included in the resulting dataset. If this parameter is empty, all the columns from the generic inquiry will be returned (including columns that may not be shown in the ERP's user interface).
IncludeHeader	Optional	TRUE or FALSE, indicating if column headers should be included in the dataset.

Additional parameters available with Velixo NX:

Parameter	Required/ Optional	Description
OutputColumn1	Optional	If no other Output Column is specified, this parameter contains the cell location where the data is to be placed - as an Excel table. If more than one Output Column is specified, this parameter specifies the cell in which the first column of the array is to appear within the table.
OutputColumn2	Optional	If more than one Output Column is specified, this parameter specifies the cell in which the second column of the array is to appear within the table.
...
OutpotColumnN	Optional	If more than one Output Column is specified, this parameter specifies the cell in which the last column of the array is to appear within the table.

Note:  These additional parameters are currently ONLY available in Velixo NX.  This feature is not yet compatible with Velixo Classic. For details about using the OutputColumn parameters, please see the documentation for the TOTABLE function.

Excel Online

Important: Loading large datasets with the GI() function is not performant in Excel Online due to the limitations of the Excel platform in the browser.  If your dataset contains more than approximately 100,000 records, we strongly recommend using a desktop version of Excel 365 for Windows or Mac OS.

Examples

Creating an array

=GI("Demo", "VelixoReportsPro-Projects", A1, "ProjectCD, Description", FALSE)

Description: Returns the project code and the description of records returned by the VelixoReportsPro-Projects generic inquiry, which match the filter conditions specified in cell A1. FALSE as the last parameter specifies that no column headers should be returned. This information is displayed as an Excel array.

Result:

Creating an Excel table (Velixo NX only)

The following and other examples of creating Excel tables can be found in Table Mirroring.

Certain Excel features do not work with Excel arrays but, rather, require that data be in an Excel table.

As a result, it can be convenient to have the option to create such a table from our generic inquiry data.

This example...

=GI("Demo", "AR-Invoices and Memos", "Type eq 'Credit Memo', "Type,ReferenceNbr,Status,Customer,PostPeriod,Amount", FALSE)

...would return data from the AR-Invoice and Memos generic inquiry and display it as an Excel array:

If, however, we modify the function by adding a value for the (Velixo NX only) OutputColumn1 parameter...

=GI("Demo", "AR-Invoices and Memos", "Type eq 'Credit Memo', "Type,ReferenceNbr,Status,Customer,PostPeriod,Amount", FALSE, H2)

Description: This returns data the data from the generic inquiry and displays it as an Excel table starting in cell H2.

Result:

Note: the GI() function still resides in cell A2, and the same data is returned. However, the results are displayed as an Excel table starting in the cell specified in in the OutputColumn1 parameter.

Configuration

For configuration details, see Generic Inquiries Options.

Accommodating pre-defined filters in Acumatica

Acumatica Generic Inquiries can include pre-defined parameters which are used within Acumatica to filter the results of the inquiry:

e.g.,

Acumatica's OData interface (used by Velixo for querying the GI) does not support this type of filtering. To be compatible with your Velixo report, you would need to modify your GI by either:

removing the parameters or
making them optional by ensuring that the conditions in your GI will return the full dataset when the parameters are not specified.

Filtering large generic inquiry datasets

If you experience either long load times (especially with Velixo Classic) or a persistent #BUSY! message (using Velixo NX) for inquiries with a very large result set [e.g. GL transactions or invoices], make sure to always supply a GIFILTER filter when using the other GI-related functions with such inquiries.

In Velixo Classic, if you're using always load the entire Generic Inquiry to enable more advanced OData syntax, consider disabling it for larger inquiries and rewriting your filters so that they do not involve ODATA functions unsupported by Acumatica [such as toupper()]. Applying your filters on the server [before downloading the data] can significantly reduce the load time.