When a search is performed on a repository, EnterWorks can only search on attributes in that repository or in linked repositories one "jump" away from the repository of interest. This means it is sometimes necessary to replicate data that is in a related repository of interest that is two or more jumps away into an adjacent repository, or even into the repository being searched. If the nature of the data is one-to-many where the repository of interest is the "one" and the related repository of interest is the "many", the different values in the "many" records need to be rolled-up into a single value and stored in a reachable repository. The rollup-attribute needs to be updated anytime the data in the "many" records is changed. For thoroughness, the rollup-attribute can also be updated anytime the record storing the value is changed (and the computed rollup value differs from the actual rollup value).
For this example, the data model has two repositories: PRODUCT_Staging and PRODUCT_Variant_Staging. There is a one-to-many relationship between PRODUCT_Staging (one) and PRODUCT_Variant_Staging (many). The PRODUCT_Variant_Staging has the attribute "Product Delivery", which is tied to a code set. The PRODUCT_Staging repository has the attribute "Product Delivery Rollup" which must contain a delimited list of all unique Product Delivery values from the linked PRODUCT_Staging records. In order to ensure the rollup attribute is always up to date, two change notification transformations need to be defined: one on PRODUCT_Staging and the other on PRODUCT_VARIANT_Staging.
The CN_Transformation_Registry repository needs an entry for each repository to perform the transformation whenever they are invoked by their respective CN_Registry entries which are invoked anytime records in the respective repositories have been created or changed:
The Transform Product transformation needs to collect the unique Product Delivery values from the linked PRODUCT_VARIANT_Staging records, then compare them to the Product Delivery Rollup value currently stored in the PRODUCT_Staging record. If the values differ, the Product Delivery Rollup attribute must be updated with the new computed value.
The target for PRODUCT_Staging is defined as follows:
PRODUCT_Staging is identified as the CN_Target_Repository. This is the same repository that is the trigger repository. This means the target record does not have to be identified by CN_Target_SQL or CN_Target_Link_Relationship. The source value for the target is defined by a SQL query that produces a delimited list of the unique Product Delivery attributes in the linked PRODUCT_VARIANT_Staging records using the following SQL Query:
select isnull(stuff((select ',' + isnull(pv.Product_Delivery, '') as [text()]
from (select distinct Product_Delivery from PRODUCT_VARIANT_Staging where Product_Id = ||Product ID||) pv
order by Product_Delivery FOR XML PATH('')), 1, 1, ''), '') as Product_Delivery_RollupThe SQL query ensures only the unique values are included (distinct) and they are sorted alphabetically.
Since the update is only necessary if the calculated list of values differs from the actual list of values, the condition for the transformation rule compares the two and requires that they are not equal.
The attribute to be compared on the trigger record is Product Delivery Rollup. The value to be compared is the calculated rollup value defined by the same SQL that is used to populate the target attribute. Since the SQL for the target and condition are identical it is only run once per event and the cached results used for the condition compare and the target value population.
The PRODUCT_VARIANT_Staging needs a similar Transformation to support the use-cases where new records are defined, existing records are linked to a different PRODUCT_Staging record, or Product Delivery is changed in an existing variant to a different value.
The target for PRODUCT_VARIANT_Staging is defined as follows:
PRODUCT_Staging is identified as the CN_Target_Repository. This is the not the same repository that is the trigger repository. This means the target record has to be identified by CN_Target_SQL or CN_Target_Link_Relationship. The target PRODUCT_Staging record for the triggered PRODUCT_VARIANT_Staging record is identified by the following SQL:
select InternalRecordId from PRODUCT_Staging where Product_Id = ||Product Id||The source value for the target is defined by a SQL query that produces a delimited list of the unique Product Delivery attributes in the linked PRODUCT_VARIANT_Staging records using the following SQL Query:
select isnull(stuff((select ',' + isnull(pv.Product_Delivery, '') as [text()]
from (select distinct Product_Delivery from PRODUCT_VARIANT_Staging where Product_Id =
||Product Id||) pv
order by Product_Delivery
FOR XML PATH('')), 1, 1, ''), '') as Product_Delivery_RollupThe SQL query ensures only the unique values are included (distinct) and they are sorted alphabetically.
Since the update is only necessary if the calculated list of values differs from the actual list of values, the condition for the transformation rule compares the two and requires that they are not equal.
The attribute to be compared on the trigger record is Product Delivery Rollup. Since the condition attribute is not in the triggered record, it must be retrieved from the PRODUCT_Staging record using the restricted name and the following SQL:
select isnull(Product_Delivery_Rollup, '') as Product_Delivery_Rollup from
PRODUCT_Staging where Product_Id = ||Product Id||The value to be compared is the calculated rollup value defined by the same SQL that is used to populate the target attribute. Since the SQL for the target and condition are identical it is only run once per event and the cached results used for the condition compare and the target value population.
The CN_Registry repository needs an entry for each repository to associate a triggered repository record to the appropriate Transformation.
If in the future additional transformations were needed for the PRODUCT_Staging and/or PRODUCT_VARIANT_Staging repositories, more entries would be added to CN_Transformation_Registry using the appropriate value for the CN_Transformation_Name attribute.