GILOOKUPF (Velixo Classic)

Overview

The GILOOKUPF function returns the value of a column from a generic inquiry by specifying a Filter to be applied on the server and the Keys of the row to locate.

Syntax

This is the syntax for this function:

=GILOOKUPF(
    ConnectionName,
    Name,
    Filter,
    Column,
    Key,
    OtherKey1,
    ...
)

Arguments

This function has the following arguments:

Arguments	Required/Optional	Description
`ConnectionName`	Optional	Provide one of the following values: Connection name as defined in Connection Manager. Velixo range expression defining a set of connection names. An array of settings as described in the Multiple connections per formula article. We recommend using the VX.SETTINGS function to construct the array. OR Omit the argument to return results for all compatible connections with default aggregation settings.
`Name`	Required	The name of the generic inquiry. To search for generic inquiry names, use the Inquiry Title lookup on the Generic Inquiry page in Acumatica. Use only letters, numbers, spaces, hyphens (`-`), and underscores (`_`) in the inquiry name. Velixo can't find a generic inquiry whose name contains certain special characters, such as `&`, `%`, `/`, `.`, `#`, or `+`. For details, see Error: Generic inquiry not found when the inquiry name contains special characters.
`Filter`	Optional	The condition that determines which records should be selected from the inquiry. The recommended way to construct this argument is by using the GIFILTER helper function. Otherwise, see the OData filter reference sheet.
`Column`	Required	The name of the column from which the value will be returned.
`Key`	Required	The values for every key column of the generic inquiry. Those will be used to locate the data row in the generic inquiry result set.
`OtherKey1`	Optional	Second key used for filtering the data.
...

Example

=GILOOKUPF(
    "Demo",
    "VelixoReportsPro-Accounts",
    C8,
    "Description",
    "10200"
)

Description: Cell C8 contains the filter =GIFILTER("Demo", "VelixoReportsPro-Accounts", "AccountCD", "1*"), which returns startswith(AccountCD, '1') eq true. Suppose the key for the VelixoReportsPro-Accounts generic inquiry is configured to be the AccountCD field. The above formula will then:

Fetch the accounts whose account code starts with 1 from the generic inquiry, applying the filter specified in the cell C8.
Retrieve the record where the value of the key column (AccountCD) is 10200.
Return the value of the Description field for this record.

Result:

Additional Notes

For each Generic Inquiry that you use with the GILOOKUPF function, you will need to explicitly specify the inquiry's key columns in the Generic Inquiries Options section of the Velixo options window.