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.