Skip to main content

Chaining Correlations

James Herring
Updated

Chaining Correlations

Chaining correlations is needed when a parent table you are migrating has reference fields that themselves point to other parent tables. To translate those reference fields, you need correlations for the referenced tables — which means those tables must be migrated first, creating a chain of dependencies.

In practice, this is typically a two-level chain. Deeper chains are unusual.

When Chaining Is Needed

Consider migrating incidents that have a department reference field. To translate department from source to target, you need a departments correlation. But when you go to migrate departments, you discover that each department has a location reference field. To translate location, you need a locations correlation.

The chain is:

Locations  →  Departments  →  Incidents

Each table must be migrated in order, and each step's correlation feeds into the next step's ID mappings.

Example: Locations, Departments, and Incidents

Step 1: Migrate Locations

Setting Value
Source table cmn_location
Output tab Table correlation enabled → creates locations correlation

After execution: The locations correlation cache contains source-to-target ID pairs for every migrated location.

Step 2: Migrate Departments

Setting Value
Source table cmn_department
Output tab Table correlation enabled → creates departments correlation
Field mapping: location ID mapping → correlation: locations, source: [location], target: sys_id

This step chains off the locations correlation — it translates the location field from the source location ID to the corresponding target location ID. It also creates its own departments correlation for use by the next step.

Step 3: Migrate Incidents

Setting Value
Source table incident
Output tab Table correlation enabled → creates incidents correlation
Field mapping: department ID mapping → correlation: departments, source: [department], target: sys_id
Field mapping: assigned_to ID mapping → correlation: users, source: [assigned_to], target: sys_id
Field mapping: location ID mapping → correlation: locations, source: [location], target: sys_id

The incidents step references multiple correlations — departments, users, and locations. It also creates its own incidents correlation for child records (journal entries, attachments, etc.).

Using correlation_cache() in Formulas

When you need more control than a simple ID mapping provides — for example, to provide a fallback value or add conditional logic — you can use the correlation_cache() formula function instead of an ID mapping:

correlation_cache("departments", [department])

This is functionally equivalent to an ID mapping but can be combined with other formula functions:

if_null(correlation_cache("departments", [department]), "DEFAULT_DEPT_ID")

You can also chain correlation_cache() calls in a formula, though this is rarely needed:

correlation_cache("locations", correlation_cache("departments", [department]))

This would look up a department's target ID, then use that as a key into the locations correlation. In practice, it is clearer to use separate ID mapping fields for each reference.

See correlation_cache() for the full function reference.

Execution Order

Chained correlations require that steps execute in dependency order:

  1. Locations — No dependencies, runs first
  2. Departments — Depends on locations correlation, runs second
  3. Incidents — Depends on departments, users, and locations correlations, runs third

If a step runs before its dependencies, the correlation cache is empty and every ID mapping returns no match. Ensure parent steps appear earlier in the procedure's step list.

For guidance on planning migration order, see Migration Strategy.

Multiple Correlations on a Single Step

A step can reference as many correlations as it needs. In the incidents example above, three different correlations are used for three different reference fields. Each ID mapping independently looks up its value from its assigned correlation — they do not interfere with each other.

There is no limit to the number of ID mappings on a step. If a table has 10 reference fields pointing to 5 different parent tables, configure 10 ID mappings referencing the 5 correlations.

Cross-Procedure Chains

Because correlations are project-scoped, the chain does not need to live within a single procedure. You can split the migration across multiple procedures:

  • Procedure 1: Migrate Locations, Departments, Users, Groups
  • Procedure 2: Migrate Incidents (references correlations from Procedure 1)
  • Procedure 3: Migrate Journal Entries, Attachments (references correlations from Procedure 2)

The only requirement is that Procedure 1 runs before Procedure 2, and Procedure 2 before Procedure 3. Because each correlation re-identifies its target records via its configured query, Procedure 2 will continue to find the records that Procedure 1 created on subsequent runs.

Troubleshooting

ID mapping returns blank values

  1. Check execution order — Confirm the dependency step ran before this step.
  2. Verify the correlation is enabled — Open the dependency step's Output tab and confirm the toggle is on.
  3. Confirm the dependency step migrated the expected parents — Review the dependency step's execution results to check that the source records you expect to be referenced were actually migrated.
  4. Check the source expression — The ID mapping's source expression must evaluate to the source ID (e.g., [department]), not the target ID.

Some mappings resolve but others don't

  • The source record may reference a parent that was not migrated (e.g., the location exists in the source but was excluded by the locations step's filter). Only migrated records have correlation entries.
  • Check for null or empty reference fields on the source record. An empty department field produces no match.

Comments

0 comments

Please sign in to leave a comment.

Powered by Zendesk