Comparing 2 objects in bi-directional sync

[mppowe]

Wondering if anyone else has had to crack this nut before.

We're in the planning stage of a bi-directional integration between Hubspot and Dynamics CRM (I know Hubspot has a delivered integration but it won't work for our purposes).

It looks like Hubspot is smart enough that if I send an Update and the data is the same as what's already there, it ignores the update. But Dynamics CRM does not do this, so I'd like to compare the values currently in Dynamics with the values coming from Hubspot to make sure there's something to actually update.

However, there are a few entities in play, so I wonder if I could make a generic recipe to receive 2 objects (the Hubspot record and the Dynamics record) and compare them to see if all fields match or any are different. Bonus would be to return the Dynamics record with only differences to use in the "update" action.

As I think about mapping the Hubspot fieldnames to the Dynamics fieldnames, and handling the different data types, especially Lookups, which have Guids and a Name translation, or OptionSets which have an "internal" value like "5" and a "display" value like "Virginia"... and it seems like too much for Workato's OOTB offering to handle.

But just thought I'd check to see if others have had to deal with something like this before, and what you came up with. Thanks for your time!

[rbflood]

I've done this type of thing by using our data warehouse (BigQuery) to hammer out the logic in SQL and compare the tables, and them create a view that shows only the rows that need to get updated, and the values I want the fields to be updated to, and then use a pretty simple Workato recipe to push that into the destintaion system (usually Salesforce). Then the data warehouse updates and the view is empty again until something changes. It's not a great solution for things that need real time updates, and it can only run as often as your data warehouse sources are updating, but it is pretty simple and works well if those aren't concerns for your use case and you have a good data warehsoue to work with.

[david-kreitter_]

As a Marketo nerd whose dream it is to recreate the native Marketo-SFDC sync using Workato + <insert DB vendor>, I love this topic. Recreating the native sync will involve all of this + auto-detect when a new field has been added to a object in System A >> auto-create this field in System B and sync the data.

I don't have any sage wisdom for you yet except to say I've found what you're describing does indeed require an external database/warehouse because we need somewhere to store the "last known state" of each relevant record so it can be compared against new data at a later time. Awesome to hear folks like Brian Flood have got this working!

Mike Power if you ever want to chat about this, I do have a spreadsheet / process flow I can share w/ you that explains the process by which a sync like this could work and what some of the basic tables required would be at a high level. Feel free to schedule time if interested.

[justinmnorris]

Great discussion. Conceptually I think the key would be - as Brian Flood and David Kreitter note - a separate database that contains an independent copy of the last known canonical values for any record. This allows you to compare the latest changes from both systems, identify and prioritize field-level changes, and then pass appropriate updates along to the other system(s) involved.

Since any source systems are constantly changing and diverging from each other, only having that stable independent copy to compare against will allow you to identify field-level changes and resolve conflicts.

I'm hopeful that the recently discussed "Data Hub" accelerator (see Dan Kennedy's session from Automate) could be adapted for this purpose.