Working with calculated fields
Calculated fields let administrators define reusable formulas that compute new values from existing data in the Data Management module. This makes it possible to turn raw worker data and reference datasets into the values that processes actually need without hardcoding logic or rebuilding it for every use.
As an administrator, you can create calculated fields, build formulas using a guided editor, refer to external datasets, manage each formula through a controlled lifecycle, add published fields to data models, and have the calculated values computed for every worker during snapshot processing.
Understanding calculated fields
A calculated field is a formula that derives a value from data you already have. You can think of it as a reusable rule, defined once and applied consistently wherever it is used. This rule calculates a result for each worker when a snapshot runs.
Examples of calculated fields include a full name built from first and last name, an eligibility flag based on country, manager, or other criteria, a merit increase looked up from a matrix by country, or a prior-year compensation value pulled from historical data.
A calculated field differs from a custom field or entity. Custom fields and entities store data. Calculated fields derive new values from that data using a formula.
This is a tip message.
For details about custom fields and entities in PaySuite, see our Managing custom fields and Working with custom entities articles.
Overview
This is a tip message.
The Calculated Fields page is available under Workbench > Data Management > Business Logic > Calculated Fields. Access to Workbench depends on your role. You need the Data Foundation Admin, Configuration, or Support professional role to create and manage calculated fields.
The page opens to a list of all your calculated fields, each showing its Name, Description, Revised date, and Status. A Full Name field is pre-built and available immediately. Four summary tiles at the top give you a quick count of how many fields exist (Calculated fields) overall and how many are in each status (Published, Draft, Deactivated). Use the search bar to filter by name as you type, or select a specific status from the Status drop-down to narrow the list.
From this page, you can create new calculated fields, open existing ones to review or edit their formula, and take other actions, which we describe in Managing calculated fields.
The following figure presents the page:
Calculated Fields in Data Management
Calculated field lifecycle
Each calculated field has a status that controls what you can do with it. There are three statuses:
Draft: The field is under configuration. You can edit the formula, add references, and change the return type. A Draft field cannot be added to a data model or used in downstream processing.
Published: The field is finalized and read-only. Published fields can be added to data models and are evaluated during snapshot processing. That is why these fields are locked – to ensure stability for any data models and snapshots that depend on them.
Deactivated: The field is no longer in use.
Using calculated fields in Data Management
In this section, to illustrate the end-to-end process, we use a specific example: a Merit Increase field that looks up a per-country increase amount from a merit matrix.
Checking the reference data
The example looks up values from a custom entity called MeritMatrix, which has a Country business key and an IncreaseAmount value field, as shown in the image below:
Merit Matrix data in the Data Viewer
This entity must be published and populated before you build the calculated field. The country codes in MeritMatrix's Country field must match exactly those stored in the worker's record in WorkerWorkCountry. If the values do not match, the lookup will not find any row and will not return anything.
This is an note/information message.
A lookup table like this has one row per key value; in this example, one row per country. Unlike Worker-linked custom entities, it does not need a direct reference to the Worker entity, which means it will not appear as an available source in the Data Modeler. This is expected behavior: the table is connected to the formula through a calculated field reference, not through the data model.
Creating a calculated field
To create a calculated field, click the Create calculated field button above the list in the Calculated Fields page.
The following drawer will slide from the right side of the screen:
Create calculated field drawer
In the drawer, enter the field details:
• Name (required) – for example, "Merit Increase."
• Description (optional) – for instance, "Merit increase amount looked up from MeritMatrix by the worker's work country."
Click Create to confirm. The field is created as a Draft and the formula editor opens immediately.
Getting oriented in the formula editor
The formula editor looks as follows:
Formula editor opened for a new Draft field
It is divided into two panels:
Element library (on the left), where you can browse and insert fields and references into your formula. Worker fields are organized into expandable groups such as Demographics, Employment, and PaidCompensation; click the Collapse and Expand buttons next to each group to manage the view. Any published calculated fields appear in a dedicated Calculated section, and reference entities that you add appear in the Reference entities section. Each field shows its data type as a tag (String, Number, Date, etc.), which is convenient when confirming type compatibility as you build the formula. Click the + button next to any field to insert it into the expression at the cursor position. Use the search bar above the list to filter across all groups. The Add reference entity button at the bottom lets you connect external datasets.
Formula builder (on the right), which contains the Return type drop-down list; the Clear button to reset the expression; function category menus (Math, String, Aggregation, Conditional, Date/Time, Array); an operator bar with logical operators (AND, OR, true, false), comparison operators, and arithmetic operators; and the expression box where you build the formula.
This is an attention message.
The Return type defaults to String and must be set manually. The editor validates that your formula's output matches the declared type and will show an error if it does not. Refer to Validating and setting the return type for details.
Adding a reference entity
To add a reference entity, click Add reference entity in the Element library panel. The dialog that opens includes the following elements:
Alias: Enter MeritMatrix. This is the name you will use in the formula. It must be PascalCase, contain no spaces, be unique within the field, and consist of 128 characters or fewer.
Data entity: Select the published MeritMatrix custom entity.
Fields to fetch: Once the entity is selected, its fields appear. Check Country and IncreaseAmount. The Is range checkbox, available for numeric fields, enables approximate or range matching; leave it unchecked for an exact match.
Effective date mode: Though not used in this specific example, it controls which version of the entity's data the formula reads at snapshot time. Choose Absolute date to assign a specific date to the reference in a calendar picker or Reference date to use the snapshot's cut-off date. You can also check Relative offset to offset from the reference date by a defined time period (Years, Months, or Days in the drop-down list).
Click Add entity to confirm your configuration.
The picture below illustrates the dialog:
Add referenced entity dialog configured for MeritMatrix
The added reference entity appears in the Element library under Reference entities, where you can expand it to see its fields, or edit and remove it using the and icons, respectively. See the following figure:
Element library showing the MeritMatrix reference entity
Building the formula
Use the function menus and the Element library to compose the expression in the Formula builder. For the merit lookup, enter the following in the expression box:
LOOKUP(MeritMatrix, {Country: Worker.WorkerWorkCountry}).IncreaseAmountThis reads in the following way: from MeritMatrix, find the row whose Country matches the worker's WorkerWorkCountry, and return its IncreaseAmount.
The key for LOOKUP is written as an object, {Field: value}, naming the column (or columns) to match on.
This is a tip message.
Insert field names from the Element library or by using Ctrl + Space rather than typing them manually, to ensure the exact names are used.
The Formula builder includes a guided helper for building LOOKUP formulas. An icon appears next to the LOOKUP function in the expression box; clicking it opens a dialog that presents the lookup configuration as three sections:
Which reference table should be queried? Select the reference table to look up from. The Source drop-down lists all reference entities available in the current formula context.
Which keys do you want to use to find the correct row? For each identifying field of the selected source, specify the value to match against. Choose between Select from your data to browse available properties, or Write an expression to enter a custom expression manually.
What do you want to use in the lookup result? Choose a Return column to retrieve a specific field from the matched row, or leave it empty to return the entire row.
A Generated formula preview updates in real time as you make selections, so you can verify the expression before applying it. All required fields must be completed before the formula is inserted; a clear error banner lists any missing inputs.
Validating and setting the return type
The editor validates the formula as you type. Because the LOOKUP expression returns a number, leaving the Return type set to String produces a type-mismatch error:
Validation error in the Formula builder
Change the Return type to Number. The error clears and the formula is shown as valid:
Valid formula with the Return type set to Number
Publishing the formula
To publish the formula, click Publish in the top-right corner of the formula editor. The field transitions from Draft to Published and becomes read-only. It appears in the Calculated Fields list with an updated status, and the Published count in the summary tiles increases.
Adding the calculated field to a data model
Navigate to Workbench > Data Modeling > Data Modeler and create a new model or edit an existing one. The model wizard includes a dedicated Calculated fields step, as you can see below:
The Calculated fields step in the Data Modeler configuration wizard
Only Published calculated fields are available for selection. A tile at the top of the creation form shows how many fields are selected and the maximum allowed (ten per model). Select Merit Increase. Make sure the model also includes the Worker fields that the formula depends on, such as WorkerWorkCountry.
The following Summary step, reached at the end of the wizard, lists the selected entities and Calculated fields before you publish the model:
Summary step with the selected entities and the Merit Increase calculated field
This is an attention message.
You cannot remove a calculated field from a data model once the model configuration is saved.
This is a tip message.
For a full walkthrough of the Data Modeler, read Working with a data model.
Running a snapshot
Navigate to Workbench > Data Modeling > Snapshot Manager and create a snapshot using the data model that includes your calculated field. Choose the effective date and the population (or populations). Then, click Create, as presented in the following figure:
Creating a snapshot with the calculated field data model
When the snapshot runs, the calculated field is evaluated for every worker. The snapshot log records a CalculateFields step. On completion, it reports the number of calculated results created, one per worker situation. If a data model contains no calculated fields, this step is skipped and existing snapshot behavior remains unchanged.
This is an note/information message.
Calculated values are not included in the snapshot CSV export.
This is a tip message.
Find out more about snapshot configuration in Working with Snapshot Manager.
Managing calculated fields
The three-dot icon at the far right of each row in the Calculated Fields list opens a contextual menu. The actions available depend on the field's current status.
Viewing a calculated field
To view a calculated field, either click its name directly in the list or click the three-dot icon and select View. The Calculated field details drawer opens on the right, showing general information (Key, Name, Description, Status, Standard, and Revised date) as well as the field's Return type and formula under Configuration.
From the drawer, for Draft fields, you can also click Edit details or Open calculation workspace to take further action.
Editing a calculated field's details
You can update the name and description of a Draft field independently of the formula. To do so, click the three-dot icon and select Edit details, or open the details drawer and click Edit details. In another drawer that opens, update the Name and/or Description as needed, and click Save.
Opening the calculation workspace
To work on a Draft field's formula, click the three-dot icon and select Open calculation workspace, or use the same button in the details drawer. This opens the full formula editor described in Using calculated fields in Data Management above.
Duplicating a calculated field
Duplicating creates a new Draft copy of an existing field, whether Draft or Published.
Click the three-dot icon and select Duplicate. In the drawer that opens, enter a Name for the copy and optionally a Description. Then, click Duplicate. The new field is created as a Draft and is ready for editing.
This is a tip message.
This is the recommended way to revise a published formula: duplicate it, edit the copy, and publish the new version.
Deleting a calculated field
Only Draft fields can be deleted. Click the three-dot icon and select Delete. A confirmation dialog warns you that the action cannot be undone. Click Delete to confirm. The field is permanently removed.