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.