Lookups

Lookups store regulation selection data for:

External data from third parties, e.g. tax tables
Immutable selection values that do not change during a payroll period
Values that may change cyclically or periodically

Section	Description
Threshold Lookup	Fixed-level range lookup with bracket matching
Progressive Lookup	Distributed range lookup across multiple brackets
Range Brackets	Full bracket breakdown via `LookupRangeResult`
Single-Record Lookup	JSON object stored as one lookup entry
Incremental Update	Partial update of large lookups
Lookup Import	`LookupTextImport` — mapping, bulk import, slice import

A lookup consists of a value list (Lookup Value), each entry composed of a key and a value. The key is a string that is unique within the lookup. The lookup value is described by a JSON structure — the user is responsible for ensuring structural consistency across all values of a lookup.

In addition to key-based retrieval, a lookup value can also be retrieved by a numeric range value (decimal). This is useful for income-dependent tax tables, for example.

A lookup contains entries with the following properties:

Access key (JSON-string)
Range value (decimal)
Value (JSON-string)

Within a lookup, the access key plus range value must be unique.

The RangeMode lookup property determines how the range value is interpreted:

None
Threshold, the first lookup value must be zero
Progressive, the first lookup value must be zero

Threshold Lookup

The following threshold lookup offers three levels. The query with a range value leads to the respective level.

Level 1: 0 - 10000 with the factor 0.05
Level 2: 10000 - 20000 with the factor 0.10
Level 3: 20000+ with the factor 0.15

regulations:
- name: TaxProvider
  updateMode: NoUpdate
  lookups:
  - name: ThresholdLookup
    rangeMode: Threshold
    values:
    - key: T0
      rangeValue: 0
      value: "0.05"
    - key: T10000
      rangeValue: 10000
      value: "0.10"
    - key: T20000
      rangeValue: 20000
      value: "0.15"

Using threshold lookup in actions:

# returns 1500 -> (15000 * 0.10)
ApplyRangeLookupValue('ThresholdLookup', 15000)
# returns 3750 -> (25000 * 0.15)
ApplyRangeLookupValue('ThresholdLookup', 25000)

The Lookup property RangeSize also allows you to limit the top level. A RangeSize of 40000 would reduce level 3 to the range from 20000 to 60000.

Progressive Lookup

The following progressive lookup offers three levels. The query with a range value distributes this across the respective level ranges.

regulations:
- name: TaxProvider
  updateMode: NoUpdate
  lookups:
  - name: ProgressiveLookup
    rangeMode: Progressive
    values:
    - key: T0
      rangeValue: 0
      value: "0.05"
    - key: T10000
      rangeValue: 10000
      value: "0.10"
    - key: T20000
      rangeValue: 20000
      value: "0.15"

Using progressive lookup in actions:

# returns 1000 -> ((10000 * 0.05) + (5000 * 0.10)) -> (500 + 500)
ApplyRangeLookupValue('ProgressiveLookup', 15000)
# returns 2250 -> ((10000 * 0.05) + (10000 * 0.10) + (5000 * 0.15)) -> (500 + 1000 + 750)
ApplyRangeLookupValue('ProgressiveLookup', 25000)

Range Brackets

When using threshold or progressive lookups, the engine can return the full bracket breakdown as a LookupRangeResult containing a list of LookupRangeBracket entries. Each bracket provides:

Field	Description
`RangeStart`	Start of the bracket range
`RangeEnd`	End of the bracket range
`Key`	Lookup value key
`Value`	Lookup value
`RangeValue`	Distributed value for progressive lookups, or the amount used from the matching bracket

This is useful when a payslip or report needs to show how a value was distributed across tax brackets, rather than just the final result.

Scripting API Access

The following scripting functions provide access to range brackets:

Function	Purpose
`GetLookupRangeBrackets`	Get the full list of range brackets for a lookup
`GetThresholdLookupBracket`	Get the matching threshold bracket for a range value
`GetProgressiveLookupBrackets`	Get the progressive brackets with distributed values for a range value

The brackets are also available via the REST API through the payroll controller endpoint for lookup ranges.

For a working example, see the Lookup test.

Single-Record Lookup

External data can be stored in a single lookup entry. The following example shows how state tax values are stored in a JSON object. The tax year serves as the access key.

regulations:
- name: TaxProvider.2027
  updateMode: NoUpdate
  validFrom: "2027-01-01T00:00:00.0Z"
  lookups:
  - name: TaxLookup
    values:
    - key: "2027"
      valueObject:
        MinimumYearlyWage: 19456.00
        CoordinatedDeduction: 23234.00
        UpperLimitYearlyWage: 84589.00
        MinimumCoordinatedWage: 3267.00
        MaximumCoordinatedWage: 56789.00

The yearly wage MinimumYearlyWage is accessed using the year of the current calculation year PeriodStartYear:

? ^^EmployeeSalary < ^#TaxLookup(PeriodStartYear, 'MinimumYearlyWage')

Incremental Update

For lookups that have a large number of entries and change only slightly, an update regulation can contain only the changed and new lookup values. Existing lookup values can be overridden with the same access key and range value.

Lookup Import

The Payroll Console offers the LookupTextImport command, which allows you to convert text files into lookups. The lookups can be saved in JSON files or imported directly into the backend.

Mapping is used to determine how the text line should be converted into a lookup value.

key:
  valueType: text
  start: 6
  length: 10
rangeValue:
  valueType: decimal
  start: 24
  length: 9
  decimalPlaces: 2
values:
- name: Code
  valueType: text
  start: 6
  length: 10
- name: MinimumTax
  valueType: decimal
  start: 45
  length: 9
  decimalPlaces: 2
- name: TaxRate
  valueType: decimal
  start: 54
  length: 5
  decimalPlaces: 4

Excerpt from a tax text file:

0601AGA0N       20250101000215100000005000 0000000020000040   
0601AGA0N       20250101000220100000005000 0000000020000050

The output lookup:

regulations:
- name: TaxProvider.2027
  updateMode: NoUpdate
  lookups:
  - name: TaxLookup
    values:
    - key: A0N
      value: '{"Code":"A0N","MinimumTax":2,"TaxRate":0.004}'
      rangeValue: 2151
    - key: A0N
      value: '{"Code":"A0N","MinimumTax":2,"TaxRate":0.005}'
      rangeValue: 2201

Bulk Import

The command option /bulk creates the lookup in bulk mode, which significantly optimizes performance for large data sources. If the lookup is imported multiple times, any existing lookup with the same name is deleted first.

Bulk Slice Import

In cases where the bulk data is split, an existing lookup can be extended setting his property UpdateMode: NoUpdate. In this case, it must be ensured that no entry with the same key/range value exists.

Example bulk import of the first slice:

regulations:
- name: TaxProvider
  updateMode: NoUpdate
  lookups:
  - name: TaxProvider
    updateMode: Update    # creates the lookup on first import
    values: []

Each additional slice:

regulations:
- name: TaxProvider
  updateMode: NoUpdate
  lookups:
  - name: TaxProvider
    updateMode: NoUpdate  # extends the existing lookup
    values: []

Next Steps

Regulation Design guidelines
Case Model design guidelines
Payrun Model design guidelines