Handling Complex Parent-Child Relationships in Source Filtering

It is common to want to migrate records related to a set of 'parent' records. For example migrate closed incidents and all related subtasks. In many cases, the child record contains a direct reference to its parent and you can use a simple inclusion filter (see here)

However sometimes the relationship is more complex, and requires 1 or more intermediate queries to get the final set of child records. In this case, you need to have intermediate mappings to perform these queries, using an inclusion filter, but they should not migrate data. There sole purpose is to provide input to the inclusion filter of subsequent mappings.

Worked Example

We want to migrate incidents and their related subtasks to target. However, the relationship between incident to subtask is stored on a separate relationships table containing the identifier for the incident and related subtask. 

      Incident-> Relationships <- Subtask

On the target, the relationship is simpler, the subtask holds the id if the parent incident:

      Incident -> Subtask

How can we map this?

1. Map the source incident table to target incident table. This will have a standard filter to identify the incidents we want to migrate. Map the fields as needed

2. Map the source relationship table to the target incident table. We don't want to create or update records here, we just want to run the query to extract the list of subtask id's. So:

   • On the options tab set both the Record Exists and Record Does not Exist to Skip

   •  Don't map any fields

   • In source filtering, create an inclusion filter with Value Set Definition Method 'From Previous Migration Set' as follows:

   • Incident ID as the source field (foreign key)

   • Incident mapping as the Value Set Migration mapping

   • Incident ID (primary key) as the value set field. 

   • Leave Include Skipped Records unchecked.

3. Map the source subtask table to the target subtask table. Map fields as needed. In the source filtering create an inclusion filter with Value Set Definition Method 'From Previous Migration Set' as follows:

   • Subtask Id as the source field (primary key)

   • Relationship mapping as the Value Set Migration mapping

   • Subtask Id ID (foreign key) as the value set field. 

   • Check the Include Skipped Records option
      

Lets say we are migrating 1 incident, that has 6 relationship records to 6 subtasks.

The execution result should show:

• 1 Incident created/updated

• 6 relationship records skipped

• 6 subtasks migrated.

The relationship mapping should be relatively fast in processing, as it is performing retrieval actions only (no transform or insert/update)

Extended Example

The above basic pattern can be extended if there are more levels of querying needed to get the required records. For example, if we want the work notes for the subtasks to be added as work notes to the parent incident on target, without migrating the subtasks:
 

Source: Incident-> Relationships <- Subtask -> Work-note

Target: Incident -> Subtask Work-notes

1. Map the incident -> incident as before.

2. Map the relationship -> incident as before (skip all inserts/updates)

3. Map the subtask -> subtask (skip all inserts/updates) as before, but no field mappings needed. Check include skipped records in the inclusion filter.

4. Map the work-notes -> work-notes. Map all fields an add an inclusion filter referencing the subtask mapping with include skipped records checked.