SI.QUERYLOOKUP

Overview

The SI.QUERYLOOKUP function returns a field from the specified Sage Intacct object, using key fields/values and an optional filter expression.

While SI.QUERYLOOKUP is more efficient for retrieving values from individual fields, we recommend using SI.QUERY for returning multiple fields from a single object.

Syntax

=SI.QUERYLOOKUP(
    ConnectionName,
    Object,
    Filter,
    FieldToReturn,
    KeyFields,
    KeyValue, 
    OtherKeyValue1, 
    OtherKeyValue2,
    OtherKeyValue3,
    OtherKeyValue4,
    ... 
)

Arguments

The SI.QUERYLOOKUP function uses the following arguments:

Argument	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.
`Object`	Required	A case-insensitive Sage Intacct object name.
`Filter`	Optional	SQL-like query based on fields on the object. The following operators are supported: `<`, `>`, `>=`, `<=`, `=`, `like`, `not like`, `in`, `not in`, `IS NOT NULL`, `IS NULL`, `AND`, `OR`. Joins are not supported. You can use the SI.QUERYFILTER function to generate filters ready to use in this argument.
`FieldToReturn`	Required	The name of the field to be retrieved from Sage Intacct.
`KeyFields`	Required	Specifies which field or fields uniquely identify the record to look up. Provide either: A comma-separated list of field names — for example, `ProjectID` or `CustomerID,LocationID`. The combination must uniquely identify a single record within the results returned after the `Filter` argument is applied. An Excel range reference containing the field names. Provide the matching value for each key field in `KeyValue` and the `OtherKeyValue` arguments, in the same order.
`KeyValue`		The value that identifies the record, matching the first field listed in `KeyFields`
`OtherKeyValue1`		The value matching the second field listed in `KeyFields`. Add one `OtherKeyValue` argument per additional key field, in the same order as `KeyFields`
...
`OtherKeyValueN`		The value matching the final field listed in `KeyFields`.

Example

=SI.QUERYLOOKUP(
    "Sage",
    "Project",
    ,
    "Managercontactname",
    "ProjectID",
    "10041"
)

Description

Queries the Sage Intacct Project object to retrieve the project manager contact name for project with ID 10041

Result

https://s3.ca-central-1.amazonaws.com/cdn.velixo.com/helpdesk/qmn-RAXUbanPHWSHUseapJctDbpbDICMVA.png