Lookups

Lookups

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)

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",
        "values": [
        ]
      }
    ]
  }
]

Each additional slice:

"regulations": [
  {
    "name": "TaxProvider",
    "updateMode": "NoUpdate",
    "lookups": [
      {
        "name": "TaxProvider",
        "updateMode": "NoUpdate",
        "values": [
        ]
      }
    ]
  }
]

Next steps

• Regulation Design guidelines
• Case Model design guidelines
• Payrun design guidelines