Using tables as dynamic function arguments

Sometimes it is necessary to make function calls dynamic. To achieve this, you may use the following functions to look up function arguments from a config table:

• getFormulaFromConfig
• getValueFromConfig
• isConfigEmpty
• runFromConfig
• confFormula
• confValue

This is useful for situations where you want to reuse a variable library, while altering its configuration based on where it is used in the company hierarchy.

This tutorial shows how to move long function arguments into a config table.

1. Start with the formula you would otherwise write

Without a config table, the variable library formula has to contain every configurable detail directly. For example, a claim summary variable might load a CSV file and sum the claim amount for the current ProductCode:

tableSumIfs(
    csvToTable(
        ".\data\za_motor_claims.csv",
        "select PolicyID, ProductCode, ClaimAmount from tbl",
    ),
    "ClaimAmount",
    "ProductCode",
    ProductCode,
)

This works while every hierarchy node uses the same file, query, and column names. If those details vary by hierarchy node, the formula would need to duplicate the whole function call or wrap it in conditionals based on hierarchy node properties:

IFS(
    AND(LineOfBusiness="Motor", Country="ZA"),
    tableSumIfs(
        csvToTable(
            ".\data\za_motor_claims.csv",
            "select PolicyID, ProductCode, ClaimAmount from tbl",
        ),
        "ClaimAmount",
        "ProductCode",
        ProductCode,
    ),
    AND(LineOfBusiness="Property", Country="ZA"),
    tableSumIfs(
        csvToTable(
            ".\data\za_property_claims.csv",
            "select PolicyID, ProductCode, GrossClaim from tbl",
        ),
        "GrossClaim",
        "ProductCode",
        ProductCode,
    ),
)

This is hard to maintain because the reusable business formula and the per-node configuration are mixed together.

2. Create a config table

Create a table called t_ClaimSummaryConfig. The first column is the row lookup, and the remaining columns contain the arguments you want to configure. Here the first column uses a dynamic row lookup based on two hierarchy node properties, LineOfBusiness and Country:

LineOfBusiness|Country	TableFormula	AmountColumn	ProductColumn
Motor|ZA	csvToTable(".\data\za_motor_claims.csv", "select PolicyID, ProductCode, ClaimAmount from tbl")	ClaimAmount	ProductCode
Property|ZA	csvToTable(".\data\za_property_claims.csv", "select PolicyID, ProductCode, GrossClaim from tbl")	GrossClaim	ProductCode
Motor|NA	csvToTable(".\data\na_motor_losses.csv", "select PolicyNumber, Product, IncurredLoss from tbl")	IncurredLoss	Product

TableFormula contains a formula snippet. The column names are plain values. When the current projection node has LineOfBusiness="Motor" and Country="ZA", Autory selects the Motor|ZA row. When the current projection node has LineOfBusiness="Property" and Country="ZA", it selects the Property|ZA row.

3. Use the config table directly

You can read each configured argument explicitly:

tableSumIfs(
    getFormulaFromConfig(
        "t_ClaimSummaryConfig",
        "dynamicRowLookup",
        "TableFormula",
    ),
    getValueFromConfig(
        "t_ClaimSummaryConfig",
        "dynamicRowLookup",
        "AmountColumn",
    ),
    getValueFromConfig(
        "t_ClaimSummaryConfig",
        "dynamicRowLookup",
        "ProductColumn",
    ),
    ProductCode,
)

This calculates the sum of the configured amount column for the current ProductCode. The same formula selects different config rows in different projection nodes because dynamicRowLookup evaluates the current node's LineOfBusiness and Country values.

Use getFormulaFromConfig when the config cell contains a formula snippet that should be injected into the current formula. Use getValueFromConfig when the config cell contains a value such as a column name or file path.

4. Shorten the formula with runFromConfig

The same formula can be written more compactly with runFromConfig:

runFromConfig(
    "t_ClaimSummaryConfig",
    "dynamicRowLookup",
    "tableSumIfs",
    confFormula("TableFormula"),
    confValue("AmountColumn"),
    confValue("ProductColumn"),
    ProductCode,
)

Inside runFromConfig, confFormula uses the table name and row lookup from the surrounding call and behaves like getFormulaFromConfig. confValue behaves like getValueFromConfig.

5. Reuse the variable library

To reuse the same variable library in another hierarchy node, keep the variable formula unchanged and give each hierarchy node the properties that identify its config row.

Hierarchy node	LineOfBusiness	Country	Selected config row
South Africa Motor	Motor	ZA	Motor|ZA
South Africa Property	Property	ZA	Property|ZA
Namibia Motor	Motor	NA	Motor|NA

Each of these projection nodes can use the same variable set and the same runFromConfig formula. The dynamic row lookup combines multiple hierarchy node properties to choose the appropriate source table, query, and column names for that node.