A powerful new function has been added, allowing calculated fields to search through tables with thousands of entries, returning a single value that matches specific conditions.
The data tables are created in Excel format and uploaded to the system through the new Lookup Tables option in the Product Design menu. Multiple tables can be created, and each table can have multiple versions valid for specific date ranges.
Access to this feature is controlled by four new security rights, ViewLookupTable, CreateLookupTable, EditLookupTable, and DeleteLookupTable. These rights have been added to the Product Designer role by default.
For details about creating data tables and uploading them to the system, see the Lookup Tables section of the Bridge 2.2.1 - User Guide - Product Design document.
Once the lookup tables are configured, the following function is used to retrieve the data.
$Lookup(p0,p1,...,p10)
Takes one or more condition fields and checks a lookup table for the value that matches the conditions, returning that value. Lookup tables are configured in the Lookup Tables option in the Product Design menu.
P0 (any): Code of the lookup table to be checked
P1 (any): The first condition to be used in identifying the appropriate value. Additional conditions can be added, separated by commas. The number of conditions in the function should match the number of conditions in the target lookup table. If the function includes more conditions than exist in the table, the function does not return any value. If the table includes more conditions than are used in the function, the function returns the first value that matches the conditions. There is a maximum of ten conditions.
Returns (any): The function checks the Effective Date of the current transaction, and looks for any versions of the lookup table that are valid for that date. If more than one version is valid for the date, the most recent table is used. The functions returns the first value in the table that matches all of the conditions. If the lookup table does not have a valid version for the date, the function does not return any value.
If the function is used to lookup numerical values, the function can be used as-is. If the values include text or any non-numerical characters, the String: prefix must be used.
This new function is fully compatible with other functions, allowing conditions to be pulled from fields in the submission form or calculated from other information. The $If function can be used to select between tables or define different conditions based on triggers.
For more information about calculated fields and functions, please see the Bridge 2.2.1 - User Guide - Calculated Fields document.