GI

Overview

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

Syntax

This is the syntax for this function:

=GI(
    ConnectionName,
    Name,
    Filter,
    Select,
    IncludeHeader,
    OutputColumn1, 
    ...
)

Arguments

Velixo NX-only arguments :

Excel Online

Rows with no value in selected columns

The function returns all rows from the generic inquiry, including rows where the selected columns have no value. Blank cells in the results are expected and are not an error.

Because every returned record produces a row, the spilled range sizes to the number of records in the inquiry result, not to the number of populated values. For example, selecting a column that is populated for 5 of 493 records returns 493 rows containing 5 populated cells.

To exclude rows with no value in a column, build the filter with GIFILTER using <>null as the criteria and pass the result into the Filter argument:

=GIFILTER(
    "Acumatica",
    "AR-Customers",
    "ParentAccount",
    "<>null"
)

This returns the OData clause ParentAccount ne null. See Filter blank or non-blank values in Filtering Generic Inquiries for details.

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 argument specifies that no column headers should be returned. This information is displayed as an Excel array.

Result:

Creating an Excel table

You can find the following and other examples of creating Excel tables in the article about 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 argument...

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

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

Result:

Configuration

For configuration details, see Generic Inquiries Options.

Generic inquiries with parameters

A generic inquiry in Acumatica can have parameters: values you enter on the inquiry page to narrow its results, such as a warehouse or a vendor. To pass parameter values from Excel, provide a two-column range in the Name argument of GI, GILOOKUP, or GILOOKUPF instead of the inquiry name.

Parameters require Acumatica 2024R2 or later.

You define the parameters of an inquiry on the Parameters tab of the Generic Inquiry page in Acumatica, for example:

Building the Name range

Each row of the range holds a key in the first column and its value in the second column. For example, to query the DB-StorageDetails inquiry for one warehouse, enter:

The range follows these rules:

• The giName row is required. Its value is the inquiry name, the same value you would otherwise enter in Name. The same restrictions on special characters in the inquiry name apply.

• Every other key is the name of an inquiry parameter, as shown in the Name column on the Parameters tab of the Generic Inquiry page in Acumatica, not the Display Name. If you use a name the inquiry doesn't have, the error message lists the parameter names it does have.

• Keys are not case-sensitive, and spaces before and after a key are ignored. A parameter name can't contain spaces or punctuation characters other than the underscore (_).

• Each key can appear only once.

• The rows can be in any order. Rows with an empty first column are ignored.

• Don't add a header row such as Key and Value. Velixo reads every row as a key and its value.

• The range must have exactly two columns.

• Instead of a worksheet range, you can use an array constant, such as {"giName","DB-StorageDetails";"Warehouse","VA-WHOLESL"}, or the output of VX.SETTINGS. VX.SETTINGS merges repeated keys, so the duplicate-key error doesn't appear.

• A range with only the giName row works the same as entering the inquiry name.

Required and optional parameters

A parameter is required when Is Required is selected for it on the Parameters tab in Acumatica.

• If you don't provide a value for a required parameter, the function returns an error that names the parameter.

• If you enter only the inquiry name, or a range with only the giName row, for an inquiry that has required parameters, the function returns an error that lists them.

• You can leave out an optional parameter or leave its value empty. Velixo then doesn't send a value for it. Whether the inquiry returns rows depends on how its conditions in Acumatica handle an empty parameter.

Parameter values

Velixo converts each value to the data type that the inquiry declares for the parameter.

If a value can't be converted to the parameter's type, the function returns an error before it contacts Acumatica. Acumatica then checks the value. If Acumatica validates the parameter, for example, a project ID, a value that doesn't exist returns an error. Otherwise, the function returns Function returned zero rows (#N/A).

Combining parameters with a filter

You can use parameters and the Filter argument together. Acumatica applies both. GIFILTER takes the inquiry name as text. If you pass the two-column range to GIFILTER instead, Excel returns #CALC!.

=GI(
    "Demo",
    A1:B2,
    GIFILTER("Demo", "DB-StorageDetails", "InventoryID", "WIDGET*"),
    "InventoryID,InventoryDescription,QtyOnHand"
)

Description: Returns the widget items (inventory IDs starting with WIDGET) in the VA-WHOLESL warehouse, with their description and quantity on hand. Cells A1:B2 contain the range shown above.

Result:

If a formula returns an error, see Errors when passing parameters to a generic inquiry.

Filtering large generic inquiry datasets

If you experience either long load times or a persistent #BUSY! message 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.

Known issues

The GI() function may return more columns than you expect.

For example, a generic inquiry that queries PMProject returns the ProjectID field, which is not specified on the result grid.

It is a correct behavior, as Acumatica unconditionally adds to the GI result all key columns of the objects used in the GI.

If you don't like seeing them, please use the Select argument to explicitly specify the columns you'd like to see in the result.