When a parent repository record shares an attribute with linked child (or grandchild) repository records, a change to the value in the parent repository record needs to be cascaded to all of the children/grandchildren. This may be needed to facilitate searches (to overcome the EnterWorks limitation of searching on attributes on the current or linked repository when the values to be searched are more than one "jump" away. Another common use-case is when the parent and child (or grandchild) records must have the same Taxonomy node assignment.
In such cases, the CN_Transformation_Registry record must be assigned to the parent repository and use the CN_Target_SQL attribute to identify the records to be updated with the new value in the parent record.
For example, if the Product_Staging repository has a one-to-many relationship with Item_Staging and both repositories have the Taxonomy attribute and the value of the Taxonomy in the Item_Staging records must match the value of the Taxonomy in the Product_Staging record, the CN_Transformation_Registry entry for the Product_Staging repository must identify the linked Item_Staging records for the target:
In the above example, the SQL identifies all linked Item_Staging records for which the Taxonomy value does NOT match the Product_Staging's Taxonomy value. If an Item_Staging record has a matching value, then it is not updated. Since the Target SQL will only update the Item_Staging records that need to be updated, the Condition for the rule needs only to determine if ANY Item_Staging record needs to be updated. This can be accomplished by performing a rollup query on the linked Item_Staging records to create a delimited list of unique values. If any Item_Staging is missing the Taxonomy value or the value does not match the Product_Staging Taxonomy value, the computed condition value will not equal the Product_Staging Taxonomy value:
If all linked Item_Staging records have the same Taxonomy value AND that value matches the Product_Staging Taxonomy value, then the Condition for the rule is not met and no update takes place.