If the same object appears multiple times in the step list, the value for the cross reference field will be taken from the last step. If the last step has a null value, it will continue searching through the steps, working backwards until the value is not null.
- Enter the name of the source field to use as a key
- Click the Browse button
(
) to
open the Source Cross Reference Key Builder dialog box, from which you can select a source field or create a
multi-field source key.
Note: If you are using a SQL source with a SQL target, verify that the Key Cross Reference target is mapped to a unique field whose value comes from the source. Otherwise, the Key Cross Reference table may not be populated as expected.
Only valid fields display, as follows:
| Operation | Valid fields |
|---|---|
| Select the field in the table to store the target key value. |
| Field defaults to TARGETKEY and cannot be changed. |
| Select either APP_A_KEY or APP_B_KEY. When you choose one of these values, the setting for the source Field in Cross Reference Table is automatically set to the other one |
- Enter the name you want to use for this field in the cross reference table
- Click the Browse button. The Source Cross Reference Name Builder dialog box, where you can select a source field or create a multi-field source name.
Entering a name in the Name Field(s) text box is optional. If you don’t specify a field name, no value is stored. The name of the field in the Scribe Internal Database is determined by the cross reference table you select. If you selected:
- KEYCROSSREFERENCE, the field name is TARGETNAME and cannot be changed.
- KEYCROSSREFERENCETWOWAY, the field name is either APP_A_NAME or APP_B_NAME, depending on the key field selected above.
Note: Name values in cross reference entries are only updated when the cross reference entry itself is updated. Keep in mind that the name stored with the entry may become out-of-date if it is changed in the application at a later time.
For example, consider a DTS that inserts a customer into the target, and then inserts an address which is keyed to the customer. The address step needs to know the key of the newly inserted customer.
If you select this option, Insight generates a calculated variable which performs the appropriate cross-reference lookup for you. See About Calculated Variables.
In the Select or enter variable name text box, you can either:
- Select the variable name generated by Insight
- Enter a name for the variable.
You can refer to this variable from data links or lookup links instead of building a formula to call the function yourself. If the value is used more than once, this is also more efficient since the database query for the cross reference lookup is done only once.