Synchronization Integration won't allow Target to Source "Id" column synchronization

  • 36Views
  • Last Post 16 October 2018
0
votes
Jacob Widmer posted this 11 October 2018

Hello there. 

I have successfully synced a SFDC object/table to a table in a PostgreSQL database. I added a new primary-key column (named "_id") and am able to sync records back and forth. My source is Salesforce, my Target is my PostgreSQL database.

I am now working on inserting new records into Postgres and having them sync back to Salesforce. I have a record that has been inserted into both, however the Salesforce generated "Id" column is not synced back into my Postgres instance. Looking at the mappings for the Synchronization Tasks, I am able to map "Source to Target" the "Id" column, but the "Id" column does not show for the "Target to Source" mapping. The columns should be availiable in both mappings, correct? Any solution to this?

Thanks.

Attached Files

Order By: Standard | Newest | Votes
0
votes
Mariia Zaharova posted this 11 October 2018

If we understood you correctly, you want to specify primary key column mapping in order to allow the system to know which record in one source corresponds to which record in the opposite source. Unfortunately, at the moment, it is impossible to specify any key (Id) for mapping existing records in the synchronized sources to each other for the first synchronization. The main requirement for synchronizing database data is that the primary key columns of the database tables must be autogenerated. The primary key columns cannot be used in the mapping.

 

Our synchronization is best suited for cases when one source contains all the data, and another one is empty before the first synchronization. In this case, the first synchronization will simply copy data from the first source to second and vice versa, and remember which original record corresponds to which copy.

 

When performing synchronization repeatedly, Skyvia synchronizes only data that was changed since the previous synchronization. It uses the fields storing information about when a record was created and modified in cloud applications and creates special tracking tables and triggers in relational databases for data modification tracking.

 

Please refer to: 

https://skyvia.com/resources/docs/index.html?synchronization_overview.htm

https://skyvia.com/resources/docs/index.html?synchronizing_product_data.htm

 

Best regards,

Mariia

0
votes
Jacob Widmer posted this 11 October 2018

Sorry, I may have worded that poorly.

I've set up my sync job as suggested, and have completed the requirement for synchronizing as the primary key columns of the database tables are autogenerated. I have created a new column "_id" that serves as my Postgres database's primary key column. In Salesforce, the primary key is the "Id" column. 

I've successfully synced from one source with all data (Salesforce) to my target (Postgres) database, pulling all of the data in. All records have their Salesforce "Id" as well as my (autogenerated) primary key "_id" filled in. 

However, with the case of creating new records to be pushed from my target database to my source SFDC instance, I inserta a new row into my database and run the sync job, thus creating a new record in Salesforce as well.

My issue comes in where after the sync job is finished, I do not have the (newly generated) Salesforce "Id" coming back and being mapped to my existing "Id" column in my database. It's not my primary key, it's Salesforce's. 

Looking at the mapping for the sync job's Task I am unable to map the "Id" column on the Target to Source mapping (first picture), but it's there when mapping Source to Target (second picture). Is there a reason it's not showing? Why isn't that column showing up, is that a bug? Since Salesforce's "Id" column is no longer my database's primary-key column, it should be treated as any column and that data should be synchronized between the source and target.

Source to Target, allows for

There are quite a few columns that I can map from Source to Target but not Target to Source. One is a custom field "O_ID__c", which is an auto number, one is "Created_Date_2__c" which is a Formula (Date/Time) field. These do not show in the Target to Source mapping setup, but show in the Source to Target mapping setup. This would make sense if after a sync the data in Salesforce (source) would be mapped and sent to the databse (target), but this is not the case. My "Id", "O_ID__c" and "Created_Date_2__c" fields have VALID values in Salesforce, but after a sync they are not populated in my database even with those fields mapped for the Source to Target mapping

 


This is happening after I create a record in my database and then the sync integration runs, the sync completes without those Salesforce values coming back into my database. 

0
votes
Jacob Widmer posted this 15 October 2018

Any help?

 

0
votes
Mariia Zaharova posted this 16 October 2018

 sync job's Task I am unable to map the "Id" column on the Target to Source mapping (first picture), but it's there when mapping Source to Target (second picture). Is there a reason it's not showing? Why isn't that column showing up, is that a bug? Since Salesforce's "Id" column is no longer my database's primary-key column, it should be treated as any column and that data should be synchronized between the source and target.

 

The Target to Source is the from PostgreSQL to Salesforce direction. Salesforce Id field is a key field and it is auotegenerated at Salesforce side. This field cannot be set with values manually and, thus, is not available in this mapping direction. Skyvia builds the id map when inserting data, and remembers it, and when performing next synchronization, Skyvia knows how the records correspond to each other

 

 One is a custom field "O_ID__c", which is an auto number, one is "Created_Date_2__c" which is a Formula (Date/Time) field. These do not show in the Target to Source mapping setup, but show in the Source to Target mapping setup. 

Auto number, formula fields are filled with the values automatically at Salesforce side and cannot be used in the mapping as target fields.

 

This is happening after I create a record in my database and then the sync integration runs, the sync completes without those Salesforce values coming back into my database. 

As we have mentioned above, the first synchronization will simply copy data from the first source to second and vice versa, and remember which original record corresponds to which copy. When performing synchronization repeatedly, Skyvia synchronizes only data that was changed since the previous synchronization. When you add record to your database (no changes are made at Salesforce side after first sync), these processes occur:

1) Source to Target sync is performed. I.e. changes made in Salesforce since last sync are sent to PostgreSQL database. Since there were no changes made in Salesforce, no changes are sent to PostgreSQL database.

2) Target to Source sync is performed. I.e. changes made in PostgreSQL database since last sync are sent to Salesforce. In your case 1 new record will be sent from your database to Salesforce.

3) Tracking tables are cleaned up in the database and LastSyncTime parameter is filled with the newly generated value.

4) Sync is completed.

 

The behaviour you are encountering is an expected one.

0
votes
Jacob Widmer posted this 16 October 2018

Thank you for your reply.

So then, if I am following correctly, after the (new) record is sent from Target to Source on the initial sync, the formula field and auto number values are then generated by Salesforce (Id, O_ID__c, etc)...and will not sync on the next synchronization unless the record is modified yet another time on the Salesforce side? 

That's the issue I am running into now.

I generate a new record on the Target side, push to Source (Salesforce) with a synchronization. The record then exists in both, however the sync does not return the auto-generated values from the Salesforce side. I have to manually update something on the record through Salesforce for Skyvia's synchronization change detection to pick up ALL of the generated values (Id, etc) before they will be sent back to Target.

Is there any way around this need to modify the record in Salesforce?

Close