The Target tab identifies what attribute in what repository records to update and with what value if the Rule conditions are met.
CN_Target_Repository – identifies the target repository to be updated if the Transformation rules determine any changes should be made. This will typically be the same repository identified in the CN_Registry entry that invokes this transformation. If the target repository is not the same, there must the specific records to be updated must be identified by either the CN_Target_SQL attribute.
CN_Target_Attribute_Name – name of the target attribute to be updated by the transformation.
CN_Target_SQL - SQL expression identifying the Target records to be updated with the target value. The SQL must return the InternalRecordID of the existing records to be updated. If it returns a null or a negative number, a new record will be created with this value. If other rules identify the same InternalRecordID, they will be included in the created records. The use of negative numbers allows for multiple records to be created at one time by different rules. If the query returns references to multiple records (i.e., multiple result rows), each referenced record is to be updated according to the Target_Attribute_Name and Target value.
By default, the identified target value will be updated in each repository record identified by the CN_Target_SQL. Additional attributes can be updated in those records by configuring the CN_Target_SQL to return additional columns, with their names exactly matching (including case) attribute names in the target repository.
This means that linked repository records can be updated with a single Transformation rule AND each record can be updated with dynamic information. When using just one of the Target_Value_<type> attributes, each record identified by the CN_Target_SQL will be updated with the same value AND multiple rules would need to be defined to populate multiple attributes in each of those records.
A practical example would be to define a Transformation rule that creates missing DAMLink records when a repository record is created or updated or a DAMMaster record is created. The Rule Condition can determine whether any DAMLink records are needed for the repository record and the CN_Target_SQL can populate all fields in each of those missing records. Each of the result rows must have a unique negative number. This can be accomplished using SQL similar to the following for SQL Server:
select -1 * (ROW_NUMBER() OVER(ORDER BY <anyColumn> ASC)) as InternalRecordId,Where <anyColumn> is any column accessible by the query, such as the one representing the primary key.
Multiple attributes in the trigger record can be updated from the same CN_Target_SQL query by having the first column be defined as: ||InternalRecordId||. This will result in those result columns being added to the item changes being collected for the trigger record.
CN_Target_Value_Literal – specifies the value for the target attribute if the transformation rule conditions are met. Must be blank if the target value is to come from an attribute in a linked record. The value "[clear]" (without quotes) must be used if the target attribute is to be cleared of its value.
CN_Target_Value_Source_Repository – name of the repository containing the target value to be used. This repository must only be specified when the source is the trigger record. Ignored if the Target_Literal_Value is not empty
CN_Target_Value_Source_Attribute – name of the attribute in the trigger record whose value is to be placed in the target.
CN_Target_Value_Source_SQL – SQL expression returning a row of data to be used as the source for the target value. A column name must match the Target_Value_Source_Restricted_Name (defined below). The same SQL may be defined in multiple conditions but each referencing a different column. This allows the data for multiple conditions to be returned and used for multiple conditions and rules with a single execution of the query. This SQL can return calculated values as well as columns from linked records. For example, if the target value is revising an End Date to be one day before a new record's start date.
CN_Target_Value_Source_Restricted_Name – restricted name of the query result column containing the target value to be used. Ignored if the Target_Literal_Value is not empty.
CN_Target_Trigger – causes the trigger to be fired for every record that is updated by this rule if Yes. This should be set to Yes only in cases where the updates being made to the target records are subject to change notification/transformation processing.
CN_Delete_Target_From_SQL – deletes the target records identified in the CN_Target_SQL if Yes.