MySQL and Pipedrive: how to create two way data sync

16:55

MySQL and Pipedrive: how to create two way data sync

n8n 30.11.2022 5 568 просмотров 50 лайков

Machine-readable: Markdown · JSON API · Site index

Смотреть на YouTube

Поделиться Telegram VK Бот

Транскрипт Скачать .md

Анализ с AI

Описание видео

↔️ In this video, we learn how to quickly create a two way sync between MySQL and Pipedrive with n8n. Our workflow will create new records in one source if it only exists in the other. Where matching records have different data it will sync the most recently updated version. We have used MySQL and Pipedrive in this example, but you could use this workflow with any CRM (e.g. Hubspot, Salesforce) and database (e.g. Postgres, MongoDB, Airtable) 👉 Get the workflow free here: [https://n8n.io/workflows/1333-two-way-sync-between-pipedrive-and-hubspot/](https://n8n.io/workflows/1822-two-way-sync-pipedrive-and-mysql/) 👉 See what other CRMs and databases you can connect via n8n here: [https://n8n.io/integrations/](https://n8n.io/integrations/) 0:00 - Intro 0:30 - Setup 1:22 - Walkthrough of workflow 2:54 - Compare dataset node 4:03 - Create contacts in MySQL database 5:09 - Create contacts in Pipedrive 6:20 - Data item change 7:14 - IF ode 8:40 - Expressions 9:50 - Date & Time node 10:37 - If updated on Node 11:38 - Set node to merge data 13:00 - Update MySQL node 14:08 - Update data in Pipedrive 16:10 - Activate the workflow

Оглавление (15 сегментов)

Intro

foreign workflow to create a two-way data sync from pipedrive to mySQL and vice versa so I already prepared the workflow and set up my mySQL database and my pipedrive account and this is the workflow that we will be going through in a minute

Setup

and for today's tutorial we are focusing on synchronizing contact person data from pipedrive I have already filled up the pipedrive with some fake contact data three in total and I've already set up a mySQL database which we're looking at through atminer at the moment with a very simple database table schema for name email phone and then updated on timestamp that automatically updates every time we update the record that we will use later on so right now as we can see in MySQL we only have one contact Kai Jacobson and in pipedrive we have three two are missing in MySQL so let's go to our

Walkthrough of workflow

workflow schedules on a trigger for the purpose of our demo we are using the execute workflow button to manually execute our workflow once we've done that we'll see that first off we are getting all the MySQL data in our MySQL note this is a simple query to select ID name email and phone from our contacts table and we are fetching all the pipedrive contact persons we are using the resource person the operation get many and return all so the pipedrive data format is pretty more complex than our SQL table so what we'll do first is we are using the set node to transform our data to match a more simpler version of our mySQL database so what we've done is we've created some values for ID name email phone and updated on and we created some Expressions to match incoming data to our set fields for example we used the ID expression to map an ID or we used things like our primary email to match to our email field

Compare dataset node

field so now we have a clean data set and both of our MySQL and pipedrive data is coming into the two inputs of our compare data set node so the is getting two inputs one from our MySQL node and one from our pipedrive or our set node and we compare the data sets using the fields email to match so anything that has the same email address is regarded as one entity and we can see that the node has four outputs that you can use by connecting different nodes here and right now at the moment we see that if something is only in input 1 so MySQL we create a pipe Drive person if something is only in input 2 we create a MySQL contact if it's the same we don't care about that and if it's different

Create contacts in MySQL database

we'll go another route that is actually updating the pipedrive person or MySQL contact in the end for our first execution let's remember we had only one contact in our mySQL database so what happened is we got one contact for MySQL and three contacts from pipedrive we matched over email and we can see now that we have two items going down to our create contact MySQL node because two of those emails didn't exist in MySQL the create contact MySQL note is pretty simple it's using the insert operation for the table contact and it's matching the incoming column's name email and phone to actually create or insert those items so if we now go back to adminer we can see that in our mySQL database we have created the two missing contacts

Create contacts in Pipedrive

so now let's do it again but the other way around let's go to pipedrive and actually delete these two contacts here so now we only have one contact and pipedrive and three in MySQL execute again and what we will see now is that our compare data set node is receiving three items for MySQL one item from pipedrive and we can see that two of those only existed in MySQL input 1 so it created two pipedrive persons the pipedrive node is pretty simple you're using the resource person the operation create and then we're using Expressions to map Fields like the name field to our incoming name field the email field and the phone field and like that if we're looking into pipedrive we can see that we have these two newly created contacts so this is how you use the compare data set node to create new persons and create new contacts so what about if one data item changed

Data item change

meaning what if we don't actually add a new contact but we just change some data here so for the purpose of this demo let's change the phone number for kayakomson in pipedrive so now these two phone numbers are different in MySQL and in pipedrive let's go back to our workflow execute the workflow again and what we should see now is that we have none new items in pipedrive or MySQL so we don't create any new contacts or persons and we have three different items that apparently changed so connected to a different output in

IF ode

the compare dataset node we can see here a new if note and the if note says data changed and from there only one item continues so how is this working well let's look at how the what US what the compare data set node is actually giving us if both are different so we get the keys object for email and we get the same object for all the fields that were the same in both input 1 and input 2 and then we get a different field that is telling us hey what Fields were actually different in both input 1 and input 2. so naturally this will be ID and the updated on timestamp that exists in both services and in this case here it's the phone number that we changed so what is the if note doing well this if node checks if actually any name or phone field exists in the different fields of our compare data set output so in this case we can see that if nothing changed only ID and updated on exists and if something changed there needs to be either a phone or a name field inside different

Expressions

so what we've done is we've used an expression expressions in Nan are all these things in between two brackets and inside Expressions we can map data like the different name property or the different phone property and everything inside those expressions is also JavaScript so what we've actually done here is we created an or condition that is checking if either the different name or different phone field exists and if one of those exists it's true and the data continues in our flow so now we can see that for a true Branch only the one with the actual phone number that changed continues to execute and our faults Branch where nothing changed we also do nothing so connected to true we go forward and we can see that the item propagates to or update MySQL contact because let's remember we actually did change the phone number in pipedrive so how is it does it know that it should update MySQL here well we're actually

Date & Time node

using the updated on timestamp so the first thing that we do is we use the date and time note because our MySQL timestamp is a little bit different from our pipedrive time step coming in input 2. so we've used an expression to map the timestamp as a value and then we are setting it back to the same property so different updated on input one and we're using this custom time format to actually match both time terms timestamps to the same format so now we have two timestamps that we can easily compare this is where the next node is

If updated on Node

coming in place the if updated on node so if updated on is using a condition a date and time condition we use Expressions to map both our input 1 and input 2 timestamp and then we use the operation or cured after meaning if input 1 the changes occured after input 2 will go to true else we go to false let's remember that input 1 is our MySQL note and input 2 is our pipedrive node we just changed the phone number in pipedrive so input 2 actually occurred after input 1 and that's why we're going down default branch and default Branch connects to or MySQL contact instead of our pipedrive contact so all we have to do now is actually select the data that we want to update and we use a little trick here and the

Set node to merge data

trick is that we are using a set mode to actually merge the data from input to input twos or pipedrive nodes so again we use Expressions to map our ID name and phone field the email is the same we don't need to work with the email here so let's look at name what we're actually doing is we're using an expression with a little if condition inside so the if condition tells n and then hey if we do have something that is a different name than actually use the different name from input to input 2 is our pipe Drive and if it's not different just use the same name so if names are identical in both pipedrive and MySQL so this is the case here the name is identical so the name in that case will be Kai Jacobson the difference is the phone number so if the phone number exists in the different field we are selecting input 2 here from pipedrive and that's how we create a simple data schema for ID name and phone that we should update in MySQL and the update MySQL contact node is

Update MySQL node

pretty easy it's an execute query node where we use again Expressions to create an SQL query that is updating the contact setting the name to name the phone to phone where the ID is ID so which idea are we using so that's another important aspect here we are using name and phone of input 2 from pipedrive but we are always using the ID from input 1 so MySQL because we want to update the already existing contact in MySQL and again we are using the same logic here with different ID if that exists use the different ID from myxql if not use the same ID and we have to do this in case that both the IDS either in MySQL or on pipedrive are the same in the end we have a valid MySQL ID and can update our contact so now let's try

Update data in Pipedrive

the other way around let's go into MySQL let's refresh the page we can see that the phone number updated from pipe Drive and now let's select another contact in the mySQL database actually rename this contact to Audrey Schultz and in adminer we have to set the new timestamp manually here so save now we have changed the name in MySQL let's go back to our workflow execute again and what we should see now is that the data is flowing through the compare data set node again we don't need to create a new person or a new contact we see that one item did actually change and now if updated on our timestamp occurred later in MySQL then in pipedrive so we are going up the route to pipedrive and again we are using the same strategy in our set input node to map incoming data but now instead of using input 2 here we are using input 1 for name and phone input one is coming from MySQL because MySQL changed and the ID we are actually selecting the input 2 ID from pipedrive so like that we have all the data necessary to update our pipedrive person and this looks like this we have the resource person the operation update we are using an expression to map our ID and our name and our phone number and that's how we actually updated our pipedrive contact from Audrey Hefner to Audrey schlotz

Activate the workflow

so now we can simply activate our workflow we are using a scheduled trigger to execute that workflow every five minutes we use activate here got it and now every five minutes and it then will automatically load all the MySQL data and pipedrive data compare data sets create persons that are missing in pipedrive or create contacts MySQL and is using our strategy to compare which data changed later in each of those systems to update the according contacts thank you for tuning in happy automating

Другие видео автора — n8n

Ctrl+V

Экстракт Знаний в Telegram

Экстракты и дистилляты из лучших YouTube-каналов — сразу после публикации.

Подписаться

Лучшие методички за неделю — каждый понедельник