Data reference checking

The Data Reference Check action allows you to verify that the values in a column exist in another table. When executed, the Data Reference Check action adds a new column to each table relationship tested to notify you of the presence (1) or absence (0) of that value in the other table. You can:

• Create an optional summary table that provides an overview of each relationship tested
• Use table mapping to define the relationships to test between input tables

On the Configuration tab:

1. Review the fields for the first table selected.

2. Review the fields for the second table selected.

3. Drag between fields to create the mapping; click the link to change the lookup option.

4. Add other tables as necessary.

5. In Output Fields, the lookup table is identified.

6. In Output Fields, the target table is identified.

7. Use the Result Field to overwrite the output default reference check column name if necessary.

Possible applications include:

• Verify referential integrity (no missing values) between related tables in a data model
• Identify record gaps when combining data from multiple sources

Check data referenced in another table

1. Drag the Data Reference Check icon onto the design surface.
2. On the Connections tab , enter a Name and a Description to identify the action, then select the database and tables:

   Input -

   • For Database Connection, select the database in which records are being checked, or choose New Database Connection to establish a new connection to another database.
   • For Table Name, select two or more tables for the integrity check. To remove a table from the list, highlight the table name, and click the Remove icon .

   Output -

   • Optionally, click Create Summary Table to generate a summary of the reference check results. For each pair of mapped fields, the summary includes the table names and totals from each table -
     • table name
     • number of rows
     • number of rows found
     • number of rows missing
     • number of values found
     • number of values missing
   • The Database Connection reflects the database in which the tables are being checked.
   • Enabled when Create Summary Table is selected, use the Table Name field to specify the name of the summary table.
   • Output Mode indicates that the summary table will be created if it does not already exist, or overwritten if it does already exist. Using this mode, you can also choose to -
     • Add a new _autokey field to the table, with incremented values that make each table row unique. This is required by some database types.
     • Indicate that the table should be deleted after the macro or workflow has executed.
3. On the Configuration tab :
   • Use the Table Mapping area to create relationships between the fields in the table(s).
     • While the actual steps you perform to create the mapping are the same as those discussed in Table Mapping using joins, the mapping you define here establishes relationships between the target and lookup tables used to perform the reference check in this action.
     • Drag from a field in one table “A” to a field in another table “B.” This creates an initial relationship between the two fields. By default, the records of target table “B” are checked against the records of lookup table “A” and this is indicated by a line extending between the two fields; in this case, the results are written to table “B.”
     • To change the type of check being performed, right-click on the relationship’s line, and select a different type -
       • Check values in <Table A> against values in <Table B>

         Target Table A uses Table B as the lookup table; in this case, the results are written to table “A.”

       • Check values in both tables

         Each table uses the other as the lookup table, and separate results are written to each target table.

     • Repeat for all fields being mapped between the two tables.
     • When creating multiple field mappings between two tables, all the mapping types must be the same; all mappings are changed to the type of the most recently configured mapping.
     • Create relationships between additional tables as necessary; there is no fixed limit on the number of tables.
     • To remove the mapping between two fields, right click on the mapping line and select Remove, or left-click to highlight the line and press Delete.
   • In Output Fields, the Result Field of each target table in the relationship identifies the name of the column to which the reference check results are written; accept the default; or overwrite the name.

Data Reference Check data example

In this example, data integrity is checked between a table containing handling cost data and another table containing shipment data.