BC.QUERYTABLELOOKUP

Overview

The function returns a value from a Business Central table record that matches key values in specified key fields. The record must match all the key values you provide, and the function returns the value of one field from that record. If no matching record is found, the function returns an error.

The function supports the same related-table joins and unpivot() expressions as BC.QUERYTABLE. Use BC.QUERYTABLE to return multiple records or columns, and BC.QUERYTABLELOOKUP to retrieve a single value from a single record.

The function requires the Velixo Extension for Business Central. See the update instructions.

BC.QUERYTABLELOOKUP retrieves data the same way as BC.QUERYTABLE: it first runs the query defined by Table, Filter, Select, and Settings, and caches the resulting dataset. The lookup itself is then performed within that cache. All lookup formulas that define the same query share a single cache, so any number of lookups against the same dataset produces only one request to Business Central.

For large tables, narrow the dataset with Filter or the Limit setting to improve performance – a smaller dataset means a faster query and a lighter cache.

Syntax

=BC.QUERYTABLELOOKUP(
    ConnectionName,
    Table,
    Filter,
    Select,
    FieldToReturn,
    Settings,
    KeyFields,
    KeyValue,
    OtherKeyValue1,
    OtherKeyValue2,
    OtherKeyValue3,
    OtherKeyValue4,
    ...
)

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.
`Table`	Required	Table name. Use the BC.EXPANDTABLERANGE function to retrieve a list of tables available for querying.
`Filter`	Optional	Filter expression. Use the BC.QUERYTABLEFILTER function to create ready-to-use filters. Filtering on fields from related tables does not reduce the number of records in the cached dataset: records that do not match the condition are still included, with an empty value in the joined field.
`Select`	Optional	The list of columns the lookup can see. `FieldToReturn` must name one of these columns. To look up an ordinary field of the main table, omit the argument - all main-table columns are included automatically. Provide the argument when the value to return is not an ordinary main-table field: To return a field from a related table, list it as a joined field using the `->` operator - for example, `Bill-to Customer No.->Name`. Joins used in `Filter` or `Settings` do not add columns; a joined field is available only if it is listed here. To return a dimension value, include an `unpivot(key, value)` expression - for example, `unpivot(Dimension Set ID->Dimension Code, Dimension Set ID->Dimension Value Code)`. It turns each dimension code into its own column, which `FieldToReturn` can then name. To create columns for specific dimensions only, list each code as a separate quoted value after the key field: `Dimension Code("AREA","DEPARTMENT")`. A single comma-separated string is not supported. Only one level of join is supported. When a join matches several records, their values are combined into a single cell unless unpivoted. Use the BC.TABLEDEFINITION function to list available columns.
`FieldToReturn`	Required	Name of the field whose value the function returns: a main-table column, a joined field, or a column produced by `unpivot()` - such as a dimension code.
`Settings`	Optional	Two-column array, containing one or more of the following keys: `Limit` - the maximum number of records in the cached dataset. Default value: 20000. `ExtraJoinKeys` - required when a single foreign key is not sufficient to identify the correct record in the related table. Syntax: `MainTableField->JoinTableField = MainTableFieldToFilterOn`. Multiple entries can be provided. You can use the VX.SETTINGS function to construct the array.
`KeyFields`	Required	A field name, or an array of field names, that identifies the record to look up - for example, `{"Document Line No.","Document No."}`.
`KeyValue1`	Required	The key value corresponding to the first field in `KeyFields`. At least one key value is required.
`KeyValue2, KeyValue3, ...`	Required if `KeyFields` contains more than one field	Key values corresponding to the remaining fields in `KeyFields`, in the same order. The number of key values must match the number of key fields; the function returns an error if too few or too many key values are provided.

Examples

Look up a single field value

=BC.QUERYTABLELOOKUP(,"Customer",,,"Name",,"No.","10000")

Description: Returns the value of the Name field for the Customer record whose No. equals 10000, using the default connection.

Result:

Look up an unpivoted dimension value

=BC.QUERYTABLELOOKUP(
    ,
    "Item Attribute Value",
    ,
    "unpivot(Attribute Name, Value)",
    "Colour",
    ,
    "ID",
    "5"
)

Description: Runs the same query as =BC.QUERYTABLE(,"Item Attribute Value",,"unpivot(Attribute Name, Value)"), turning each attribute name into its own column, and returns the value in the Colour column for the record whose ID is 5.

Result:

The Colour column does not exist in the table itself - it is created by the unpivot() expression, which is why the expression must be included in the Select argument.