|The Update function enables bidirectional data update between two data sources.|
NOTE: For the difference between 'update' and 'synchronization' in terms of Apatar functionality, please review Table Update guide.
The data sources supported at the moment and Salesforce and JDBC driven databases like MS SQL, My SQL, ProstgreSQL, Progress, etc. and applications based on these databases (if you are connecting directly to an application's database).
The transformation will be run in the update mode regardless of the mode you select at the last configuration screen of the connectors.
NOTE: Please do not use any other operations between the connectors and Update operation.
Operation setup window components:
The window should have 4 tabs: Source1TableName > Source2TableName, Source2TableName > Source1TableName, Update Conditions, Match by.
Fields mapping setup
|Source1TableName > Source2TableName tab should be filled in first.|
- Drag and drop fields from Source1 and Source2 tables and link them applying functions where necessary to convert data types. For example, if you need to write data from a numeric field to a text field, use To String function.
- Once you are done, switch to Source2TableName > Source1TableName tab. The fields mapping will be copied from the previous tab. Automatic replacement of some functions will be done.
NOTE: a) If a replacement function has not been found automatically, the fields will still copied to the work area automatically, but they will not be linked.
b) some functions will be deleted as useless (for example, Crop function)
- Review the mapping to make sure mapping and function replacements are correct and add missing functions.
NOTE: Fields mapping will only be copied once. If you change the mapping at Source2TableName > Source1TableName tab (change source or target fields, add or delete field mappings) and go back to Source1TableName > Source2TableName tab, these changes will be preserved at Source2TableName > Source1TableName tab but not copied to Source1TableName > Source2TableName tab. Also, if you go back to Source1TableName > Source2TableName tab, make changes there and switch to Source2TableName > Source1TableName tab, the changes will not be copied.
Update conditions setup
- Go to Update conditions tab.
- Drag and drop fields and functions to set conditions by which the records should be updated. For example, if I want to update the records by last modified date the condition will look like this:
Apatar will compare the dates from 2 data sources and decide which one is greater. The record with the latest date will be written to the other data source.
You can set more than one condition for the update. In this case all the conditions should be true for Source1 records so that the related Source2 records is update with it values. If at least one of the conditions is not true for Source1 record, it will be updated with the matching Source2 record data.
1) Source1LastDate is greater than Source2LastDate
2) Source1LastUser equals to Mike.
For record 1 in Source1 both conditions are true, so record A in Source2 will be updated with record 1 values
For record 2 in Source1 the first condition is not true, so it will be updated with the values of record B from Source2.
Records matching conditions setup
Just like you select fields to be used as primary keys at the last screen of connectors configuration wizard (if you choose to run a transformation in the update or synchronize mode), you need to set the conditions by which records in 2 different data sources should be defined as matching.
Building matching conditions is pretty much the same as creating join conditions in Join operation.
- Go to Match by tab.
- Click '+' to add matching conditions. In one column user selects a field from Source1 and in the other - the field from Source2 which value should match the Source1 field value. If a match as found, the record will be updated. If not, new record will be created.
In the screenshot above, if the Name field value in one source is the same as CONTACT value in the other source, the records will be matched and updated. If there is a record in Contact that doesn't have a match in CONTACT1, a new records will be created in CONTACT1 and vice versa.