Lookup Tables / Company Parameters (Quick Reference)

Use lookup tables when you need to create a table with key-value tuples. There are two main types of lookup tables:

Simple – Contains key-value pairs.

Matrix – Contains key-value tuples. A matrix table contains 1-6 key columns and 30 customizable attribute columns.

The methods vLookup() and findLookupTableValues() allow you to easily query these tables. api.vLookup() allows you to find a row with a unique key combination. api.findLookupTableValues() allows you to perform more advanced queries and retrieve more than one result, similarly to api.find().

Query Single-Key Tables

In lookup tables with a single key column, the column is called name.

Look up a single value with api.vLookup(). The result will be a map that represents a single row, or null if no result matched the query.

Country.groovy

import groovy.transform.Field @Field final String TABLE_NAME_COUNTRY = 'CountryInfo' @Field final String COLUMN_NAME_COUNTRY_NAME = 'attribute1' @Field final String COLUMN_NAME_COUNTRY_CODE = 'name' String findCountryName(String countryCode) { return api.vLookup( TABLE_NAME_COUNTRY, // ❶ [COLUMN_NAME_COUNTRY_NAME], // ❷ [(COLUMN_NAME_COUNTRY_CODE): countryCode] // ❸ )?.getAt(COLUMN_NAME_COUNTRY_NAME) }

❶ Specify the name of the table to query.
❷ fetch only the necessary data.
❸ A query is built from an object that maps column names to column values. In this case, there is only a single key name.

Query Multi-Key Tables

For lookup tables with 2-6 key columns, the key columns are called key1, key2, key3, key4, key5, and key6.

ExchangeRates.groovy

import groovy.transform.Field @Field final String TABLE_NAME_EXCHANGE_RATES = 'ExchangeRatePerMonth' BigDecimal convertCurrency( BigDecimal amount, String currencyFrom, String currencyTo, Date date = libs.TrainingLib.DateUtils.today() ) { if (null in [amount, currencyFrom, currencyTo, date]) { throw new Exception("All arguments must must be non-null") } if (currencyFrom == currencyTo) { // Exchange rate = 1 return amount } String month = date.format("yyyy-MM") def rateFromKeys = [ key1: currencyFrom, key2: month ] def rateToKeys = [ key1: currencyTo, key2: month ] def rateFrom = api.vLookup( TABLE_NAME_EXCHANGE_RATES, // ❶ ['InEuros'], // ❷ rateFromKeys // ❸ )?.getAt('InEuros') as BigDecimal if (rateFrom == null) { throw new Exception("Failed to find rateFrom with keys ${rateFromKeys}") } def rateTo = api.vLookup( TABLE_NAME_EXCHANGE_RATES, // ❶ ["InEuros"], // ❷ rateToKeys // ❸ )?.getAt('InEuros') as BigDecimal if (rateTo == null) { throw new Exception("Failed to find rateTo with keys ${rateToKeys}") } if(rateTo == 0){ throw new Exception("rateTo is 0, which would lead to division by zero.") } def amountInEuros = amount * rateFrom def amountInToCurrency = amountInEuros / rateTo return amountInToCurrency }

❶ Specify the name of the table to query.
❷ Fetch only a specific value.
❸ The query will be built from the map with the keys. In this case, there are two keys – key1: currencyTo and key2: month.

Find All Values

To find all values, you only need to specify the lookup table name with api.findLookupTableValues().

Country.groovy

@Field final String TABLE_NAME_COUNTRY = 'CountryInfo' @Field final String COLUMN_NAME_COUNTRY_CODE = 'name' @Field final String COLUMN_NAME_COUNTRY_NAME = 'attribute1' @Field final String COLUMN_NAME_REGION_NAME = 'attribute2' List<String> findAllCountryCodes() { return api.findLookupTableValues( TABLE_NAME_COUNTRY, // ❶ [COLUMN_NAME_COUNTRY_CODE], // ❷ COLUMN_NAME_COUNTRY_CODE // ❸ )?.collect { row -> row[COLUMN_NAME_COUNTRY_CODE] as String // ❹ } }

❶ Specify the table to query.
❷ Fetch only the necessary data.
❸ Sort in alphabetical order.
❹ Transform List<Map> into List<String>.

Select Fields to Retrieve

To retrieve a subset of all values in a lookup table, specify a filter with api.findLookupTableValues().

Country.groovy

❶ Specify the table to query.
❷ Include only the necessary amount of data.
❸ Filter by the country name.
❹ Include only countries that are located in the specified region.

Found an issue in documentation? Write to us.