Table Link (key fields)

Overview

The Table Link (key fields) for a transfer specifies the unique link between a record in the source table and the corresponding record in the destination table.This can be the primary key of each table, but it can also be a different set of fields.

• It is important that these fields make up a unique key, so that a lookup in the destination table based on the fields in the "table link" will return one row exactly.
• You can use Formulas in the left side (the source side) to transform the source value(s) into a key that matches the destination.
• When using "Disable Dest Lookup" the table link is not used, but you must specify something anyway or you will get an error message.

Scenarios:

1. Table Link used in combination with Store new ID field:

• When using "Store new ID" feature, the table link has a special meaning (and is not used for a lookup in the destination as usual): In the left side you specify the primary (unique) key to the source table and in the right side you specify the ID (primary key) field from the destination table, that you want to fetch and store in the source table.
• In the below example, the destination field ‘No’ will be written back into the source system in a field called ‘AccountNumber’.

Dependencies:

• Commit per must always be 1 
• Under Filters Source (delimitations), a filter should be set up: e.g. AccountNumber = ‘’
• In the General section, use Add in combination with Disable Dest. Lookup.

2. The source and destination key fields match and the record unique id/number is the same in both systems:

• You would add both fields under Table Link (key fields).

Dependencies: 

• In the General section, you can use Add and Update if you want existing records to be created and new ones to be created if they are not found in the destination system. 
• Source Control Field needs to be used for a better performance of both systems and track only the new and/or updated records.
• Commit per should be set up to 200.

3. No key fields in Table Link (key fields):

• When you do not have any key fields in the Table Link(key fields), then you would need to use a Link Storage that will act as a Table Link. In the Link Storage, you will set up the key fields for your specific type of data ( customers, contacts, orders,etc).
• As soon as you have it created, you can add it under the General section for your specific transfer. 

Dependencies: 

• In the General section, you can use Add and Update if you want existing records to be created and new ones to be created if they are not found in the destination system. At runtime, it will check the records from the Link Storage: if they exist, they will only be updated ( if there is an update); if they don’t exist, they will be created in the destination system and a new entry will be created in the Link Storage. 
• Source Control Field needs to be used for a better performance of both systems and track only the new and/or updated records.
• Commit per should be set up to 200.