Configure lookup tables

What are lookup tables?

Lookup tables let you define a reference dataset that your cycle's formulas can query. Instead of hardcoding logic like "if performance rating is Exceeds, raise is 5%," you upload a table with those mappings and Pave looks up the right value for each employee automatically. When the data changes, you update the table once and every formula that references it picks up the change.

Common uses:

• Map performance ratings to raise percentages or flat dollar amounts
• Define merit guidelines by department, level, or location
• Set equity targets based on job level and pay zone

Each lookup table belongs to one merit cycle, and you can create multiple lookup tables per cycle. You reference a table in custom column formulas and recommendation logic using the {{lookupValue}} helper. Lookup table values are not currently referenceable as variables directly in reward letters. If you need a lookup-derived value in a reward letter, create a custom column that uses {{lookupValue}} to calculate the value, then reference that column in your reward letter template.

Find lookup tables

1. Open a cycle and go to Configuration.
2. Select Data upload.
3. Select the Lookup tables tab.

The list shows every lookup table in the cycle with its name, table key, description, size, and last updated date. Select a table key to copy it to your clipboard.

Create a lookup table

Step 1: Upload a CSV

Select Create new. If you want a starting point, select Download a sample to get a template CSV.

Drag and drop a CSV file or select one from your computer. Your file must meet these requirements:

• At least two columns
• Column names that use letters and numbers only: no spaces, hyphens, or special characters
• No duplicate or empty column names

After uploading, assign a data type to each column: String, Number, Boolean, or Date. Pave uses these types to validate your data and to match values when a formula runs a lookup, so choose the type that matches how the same field is stored in your cycle (see Matching rules).

Step 2: Validate your data

Pave checks each value against the data type you assigned. Review any flagged cells and fix them before continuing.

Step 3: Name and configure the table

Once your data passes validation, configure the table:

• Table name: A display name shown in the lookup tables list. Letters, numbers, and spaces are allowed. Names must be unique within the cycle.
• Table key: A short identifier Pave generates from your table name (for example, "Performance Ratings" becomes performanceRatings). Formulas use the key, not the display name. The key can't be changed after you create the table.
• Description: Optional. A note about what the table is used for.
• Match columns: The columns Pave uses to find the right row for each employee. The combination of values in your match columns must be unique across all rows. For example, if you pick "performanceRating" and "level" as match columns, each combination of rating and level can appear only once.

Columns you do not select as match columns become result columns: the values Pave returns when it finds a matching row.

Select Save to create the table. Pave then recalculates every formula column and recommendation in the cycle that references lookup tables. Updated values can take a few moments to appear in the worksheet.

Use a lookup table in a formula

Once you create a lookup table, you can reference it in any custom column formula or recommendation logic in the cycle using the {{lookupValue}} helper.

Syntax

Each {{lookupValue}} call has three parts: the table key, a value for each match column, and the result column to return.

{{
  lookupValue
  table="performanceRatings"
  performanceRating=columns.performanceRating
  level=columns.level
  return="raisePercentage"
}}

In this example:

• table="performanceRatings" is the table key
• performanceRating and level are the match columns, each receiving the employee's value from the cycle
• return="raisePercentage" is the result column whose value Pave returns

Pave finds the row where every match column equals the employee's value, then returns that row's raisePercentage. To pull several values from the same row, such as a minimum, target, and maximum for a recommendation, repeat the lookup with a different return value each time.

Copy a ready-made snippet

You don't need to write the call from scratch. Open a table from the list to see a snippet at the top of the detail view with your table key and column names filled in. Copy it into your formula and replace the columns. references with the cycle columns you want to match on.

To copy only the key, select the Table key pill on any row in the list.

Get help in the formula editor

The formula editor suggests table keys, match columns, and result columns as you type. When you save, it flags any lookup call that references a table or column that doesn't exist.

Matching rules

Lookups use exact matching. If no row matches an employee's values, the formula returns a blank value rather than an error. Common causes of a blank result:

• Case or spelling differences: USA does not match usa.
• Data type differences: a match column typed as Number won't match a cycle column stored as text, even if both show 1.
• Missing match columns: the formula must pass a value for every match column in the table. A partial match never returns a value.
• Empty employee values: employees with no value in a match field return blank.
• Returning a match column: return must name a result column. Match columns can't be returned.

Number columns are normalized when saved, so 0.80000 is stored as 0.8 and 007 as 7. If leading zeros matter (for example, job codes like 007), set that column to String.

For more on writing formulas, see Writing formulas with Handlebars.

Edit a lookup table

Open a table from the list to view its rows, search by match column values, or download it as a CSV. From there you can:

• Edit table: make spot changes to individual values or delete rows.
• Upload new CSV: replace the table with a new file, including its columns, data types, and match columns. Use this for larger changes, like swapping placeholder numbers for final values.
• Edit name and description: update the display name or description. The table key stays the same, so existing formulas keep working.

After you save changes, Pave recalculates every formula column and recommendation in the cycle that references the table.

Delete a lookup table

Open the row menu for a table and select Delete. If any custom columns or recommendations reference the table, Pave lists them before you confirm. You can still delete the table, but those formulas will return blank values for all employees until you update them to point to another table.

Things to know

Size limit: All lookup tables in a cycle share a combined limit of 500 KB. Each table's size is shown in the list, and Pave warns you as you approach the limit. Uploads that would push the total over 500 KB are blocked, so delete a table or trim your file to make room.

Duplicate cycles: When you duplicate a merit cycle, its lookup tables copy over to the new cycle automatically.

Dependencies: The column configuration table shows which lookup table each column references.

Audit log: Select Audit log from the lookup tables page to see when tables were created, edited, or deleted, and by whom.