Kofax Insight Studio > Records > Create records > New records - join data sources

New records - join data sources

A record may pull columns from multiple tables in one or many data sources to retrieve all of the record fields it needs. This is referred to as joining data sources or inserting a relation. Joining is done by relating an additional child table to one of the tables already in a record definition via a shared column (such as a key or ID) that is defined with the Relationship Editor. A child table can relate to the main table through a relationship with another child table (such as Main > Child 1 > Child 2).

  1. Click the source table to select it and then click the Add symbol.

    You cannot add a child table unless a source table is selected.

  2. When the Add Source window appears, select Join to existing root (JOIN).
  3. In the New source type section, select the source type to use.
  4. In the Select new source section, click the ellipsis button to open a menu of available data sources.
  5. Select the data source to join and click OK.

    In the Define Condition section, you have two columns: a Left Item and a Right Item. Here, you relate the field in the parent table to the matching field in the child table.

    For example, if a column showing Product ID exists in both tables, you can match the rows from one table to the other. The Left Item column is where you select the key field from your main (or originally selected) table used to join the new child table to the main table. The Right Item column is where you select the key field from the recently joined table that links to the key field in the Left Item column.

  6. Optionally, change the default join condition by following these steps.
    1. Click Click here to add new item.
    2. Click in the box under Left Item to open a drop-down menu that lists all table fields.
    3. Select your key field.
    4. Select the same field in the Right Item drop-down menu to map these fields together.

      The relationship between two tables could be as simple as table1.keyField = table2.keyField or as complex as the business logic requires.

      For example, you might have composite keys or a customer ID in one database that is a four-digit code and in another database a seven-digit code, with the first three digits equal to zero. Defining the relation expression enables you to express the logic to adjust for those differences. This tool is powerful.

      You can perform a wide variety of relationship expressions, including those requiring mathematical transformations, conditional logic, database function calls, custom variables, or other query parameters.

  7. To use custom join conditions, in the Define Condition section, select Advanced > Edit condition.

    The expression editor window appears.

  8. In the Advanced Options section, select the join type to use.

    The Join Type is based on how the two tables should be joined if no match is found between rows in parent and child tables.

    • Join of both tables (INNER JOIN): Fields for a record from both the parent and child tables are included if a match is found.

    • All records from parent table and corresponding records from the child (LEFT JOIN): Includes all records from the parent table even if no matches are found in the child table. Where no matching child row exists, there are null values for fields coming from the child table.

    • All records from the child table and corresponding from the parent (RIGHT JOIN): Includes all records from the child table even if no matches are found in the parent table. Where no matching parent row exists, there are null values for fields coming from the parent table.

    • All records from the parent and all records from the child (FULL JOIN): Includes all records from both the parent and child tables even if no match is found.

  9. Click OK.

    The Child table is listed in the Sources field under the Mapping tab.