Solved

Zapier Doesn't Update Airtable record with multiple values


Userlevel 1

Hey folks,

I needed your assistance with an issue.

I have an Airtable base with two tables: Requirements and Clients.

The Requirements table receives orders, even from Contacts who are not in our Client list yet. Once they put in a Requirement, we use Zap to create a new entry for them in the Client table. 

 

Since the Client entries are made dynamically, we cannot pre-populate the Client table. As a result, all the requirements that come through require Zap to be linked from the Requirements table to the Clients table.

Now the issue is that even thoguh the Requirements column on the clients table is a field that allows linking to multiple data entries, Zap does not update the Requirements column on the clients table with multiple fields. It always replaces the old Requirement links/entries associated with any Client, with the Requirement ID for a new Requirement made by the client.

 

I want the field to hold multiple entries for the older ones, on the basis of their business name and email. The Zap filter is working fine. I just need some advice as to how I can get Zap to update the Airtable field with multiple values instead of replacing the old values. 

icon

Best answer by Mitch 7 September 2020, 10:36

Hi @rhysm13 ,

How I’ve got about it is….

On Airtable:

INVOICENUMBER’ is in my first column which will be linking tab A to tab B (my unique identifier)

Tab A - create a Linked field colum to tab B for the example I’ve called it ‘TAB B LINK’

Tab A - you need to do a Record_ID () formula - for namesake I’ve called it ‘REC’

Tab B - you need to look up that column of Record_ID ()  - called ‘LookUpREC’ - then….
            Create a formula column and = the ‘lookupRecID’ column - called ‘LookUpRECString’

 

On Zapier:

Make a 3 stage Zap.

Step 1 - create record in Airtable (where your Tab A info feeds in)

Step 2 - Find or Create record in Airtable (TAB B Info)

Search by field will be ‘INVOICENUMBER’ and search value will be the ‘INVOICENUMBER’ column from Step 1 (Tick the ‘create Airtable Record if it doesn’t exist yet?’ box if it’s relevant to you) - again, you need to fill the ‘INVOICENUMBER’ record from Step 1

Step 3 - Update Record in Airtable (TAB A)

In the ‘Record’ selection, use the ID from Step 2.

In the ‘TAB B LINK’ selection, use the ‘INVOICENUMBER’ selection from Step 1.

 

When new information enters Tab A, it will now find Tab B, see if the ‘INVOICENUMBER’ exists, if it doesn’t it will create a record in Tab B. It will then add the ‘INVOICENUMBER’ in to the linked column of Tab A.

You can hide the ‘LookUpREC’ and ‘LookUpRECString’ columns now as they will effectively be working in the background of your zap.

 

Sounds a bit complicated but the process is actually very easy once you know how to do it. It doesn’t quite make sense to me how this is the only work around (that i have found) but it’s been working for me ever since :)

Happy to explain any of the steps further if you need more help & hope that makes sense…

Mitch

 

View original

15 replies

Userlevel 4
Badge +3

Hi @Arkadeep_v3rb,

 

Just to confirm our workflow is something like this: Zapier Trigger → Create new record in Contacts → Link new “Contacts” record to an existing record in Requirements

 

Can you share some screenshots of what you have built so far.  I have some ideas to solve this but want to make sure I understand what your doing before I start pointing you in a direction.

Userlevel 7
Badge +9

Hi @Arkadeep_v3rb!

Since this was a little while ago, I wanted to check in to see if you’d managed to solve this or whether you still needed help here. Please let us know!

Userlevel 1

Hi @Arkadeep_v3rb!

Since this was a little while ago, I wanted to check in to see if you’d managed to solve this or whether you still needed help here. Please let us know!

 

Hi, @nicksimard 

I’ve just found this post and also have this issue. 

Here’s where I’m getting stuck…

Tab A - multiple customer records will appear here - customer identified by an invoice number.

Tab B - Invoice numbers relating to the individual customer - linked record to Tab A

When Tab A populates, I want the new record to find the Invoice Number in Tab B & add to the linked record, so we can have multiple linked records in one cell.

We have to manually add the linked record to Tab B at the moment which is not ideal at all.

Example below:

 

My Zapier knowledge is pretty decent as we’ve been using it for about 2 years, so don’t be afraid to get complicated with it - i’d love for this to be resolved!

Thanks,

Mitch

Userlevel 7
Badge +5

Hi @Mitch ,

Thanks for reaching out- hopping in for Nick here to tag in one of our wonderful Zapier Experts, @BowTieBots as it looks like they may have some ideas around this workflow!

@BowTieBots would love to hear your thoughts on this one!

Userlevel 1

Hi @nicksimard @Liz_Roberts @BowTieBots ,

Any thoughts on a solution for this? Just thought i’d check back in and see if there was an update.

Cheers!

Mitch

Userlevel 5
Badge +3

Hey folks you need the Id of the to be linked record. Create a rec() function in the table and use a function to find the matching case then pick the ID and link it.

 

We provide professional airtable support @greg.kelly  should we create a hardcore how to for it? 

Userlevel 1

Hi @Wemakefuture Thanks for the advice.

I don’t quite follow though… so using the record ID is the only way around having a zap which can populate multiple records in a linked field and not just the latest one? It seems to me that it would still just kick off pre-existing linked records with the latest one which matches from Tab A to Tab B (in my example above)

Please let me know if this is the case.

Thanks,
Mitch

Userlevel 5
Badge +3

@Mitch If you want, share your base and we get a Dev on it to test it and help you. wemakefuture.com/go

Userlevel 1

@Wemakefuture Hi and thanks for your reply.

I’ve worked out how to do this :grin:

I appreciate your help!

Mitch

Hey @Mitch 

I’ve got the same issue as you. Do you mind sharing your solution?

Thanks mate!

Userlevel 1

Hi @rhysm13 ,

How I’ve got about it is….

On Airtable:

INVOICENUMBER’ is in my first column which will be linking tab A to tab B (my unique identifier)

Tab A - create a Linked field colum to tab B for the example I’ve called it ‘TAB B LINK’

Tab A - you need to do a Record_ID () formula - for namesake I’ve called it ‘REC’

Tab B - you need to look up that column of Record_ID ()  - called ‘LookUpREC’ - then….
            Create a formula column and = the ‘lookupRecID’ column - called ‘LookUpRECString’

 

On Zapier:

Make a 3 stage Zap.

Step 1 - create record in Airtable (where your Tab A info feeds in)

Step 2 - Find or Create record in Airtable (TAB B Info)

Search by field will be ‘INVOICENUMBER’ and search value will be the ‘INVOICENUMBER’ column from Step 1 (Tick the ‘create Airtable Record if it doesn’t exist yet?’ box if it’s relevant to you) - again, you need to fill the ‘INVOICENUMBER’ record from Step 1

Step 3 - Update Record in Airtable (TAB A)

In the ‘Record’ selection, use the ID from Step 2.

In the ‘TAB B LINK’ selection, use the ‘INVOICENUMBER’ selection from Step 1.

 

When new information enters Tab A, it will now find Tab B, see if the ‘INVOICENUMBER’ exists, if it doesn’t it will create a record in Tab B. It will then add the ‘INVOICENUMBER’ in to the linked column of Tab A.

You can hide the ‘LookUpREC’ and ‘LookUpRECString’ columns now as they will effectively be working in the background of your zap.

 

Sounds a bit complicated but the process is actually very easy once you know how to do it. It doesn’t quite make sense to me how this is the only work around (that i have found) but it’s been working for me ever since :)

Happy to explain any of the steps further if you need more help & hope that makes sense…

Mitch

 

Userlevel 5
Badge +3

@Mitch perfect solution! This is how you do it :) 

Link to another record only one variable otherwise it could fail. 

 

Hi @rhysm13 ,

How I’ve got about it is….

On Airtable:

INVOICENUMBER’ is in my first column which will be linking tab A to tab B (my unique identifier)

Tab A - create a Linked field colum to tab B for the example I’ve called it ‘TAB B LINK’

Tab A - you need to do a Record_ID () formula - for namesake I’ve called it ‘REC’

Tab B - you need to look up that column of Record_ID ()  - called ‘LookUpREC’ - then….
            Create a formula column and = the ‘lookupRecID’ column - called ‘LookUpRECString’

 

On Zapier:

Make a 3 stage Zap.

Step 1 - create record in Airtable (where your Tab A info feeds in)

Step 2 - Find or Create record in Airtable (TAB B Info)

Search by field will be ‘INVOICENUMBER’ and search value will be the ‘INVOICENUMBER’ column from Step 1 (Tick the ‘create Airtable Record if it doesn’t exist yet?’ box if it’s relevant to you) - again, you need to fill the ‘INVOICENUMBER’ record from Step 1

Step 3 - Update Record in Airtable (TAB A)

In the ‘Record’ selection, use the ID from Step 2.

In the ‘TAB B LINK’ selection, use the ‘INVOICENUMBER’ selection from Step 1.

 

When new information enters Tab A, it will now find Tab B, see if the ‘INVOICENUMBER’ exists, if it doesn’t it will create a record in Tab B. It will then add the ‘INVOICENUMBER’ in to the linked column of Tab A.

You can hide the ‘LookUpREC’ and ‘LookUpRECString’ columns now as they will effectively be working in the background of your zap.

 

Sounds a bit complicated but the process is actually very easy once you know how to do it. It doesn’t quite make sense to me how this is the only work around (that i have found) but it’s been working for me ever since :)

Happy to explain any of the steps further if you need more help & hope that makes sense…

Mitch

 

Mitch, if you have a chance, could you please explain this a little further? If you could snap a quick screenshot or two, that would be especially appreciated!

Userlevel 1

Hey all,

I ran into this problem too where Zapier would override the existing linked records in a field and replace them with the single linked record value from the update function. I tried the solution that @Mitch wrote, but it didn’t work for me. As @Wemakefuture mentioned, this only works if your linked tables have a one-to-many relationship. 

In Mitch’s example, he was able to look up the corresponding record from Table B and perform an ‘Update Record’ function in Table A to add in the linked record ID from Table B. But Table A records will only be linked to ONE record from Table B. 

In my case, I have two tables with a many-to-many relationship, so Mitch’s solution didn’t work (or I misunderstood it).

Here’s what I did. This solution will work for both many-to-many AND one-to-many relationship tables:

TL;DR

When you update a linked record field, you have to also include the existing linked record IDs along with the new one you want to add, separated by a comma. You can find the existing linked record IDs by searching for that existing record using a ‘Find Record’ step in Zapier. 

Here’s my example:

Table A has location records:

Table A has location records. ‘LocationID’ is unique.

Table B has ContactMap records:

Table B has ContactMap records. ‘ContactMap_ID’ is unique.

A contact can be associated with multiple locations, and a location can be associated with multiple contacts.

If I am creating one of these records from scratch, Mitch’s solution works, but if a record already exists and I simply want to update it with additional linked records, it breaks. 

Here’s what I did to make sure Zapier doesn’t erase the existing linked records when it updates:

In Airtable:

In both tables, set up a unique ID column and a linked record column that links your two tables together.

In Zapier:

Step 1: Pull the data that you want to use to create a record in Table B into Zapier with a trigger.

Step 2: Set up a ‘Find a Record’ step to search for an existing record in Table A. In this use case, you’re expecting to find an existing record in Table A that has other records linked to it. 

In the Search Value field, enter in whatever unique identifier you’re using. For contacts, we use email address, but it could be an invoice number or something else that comes in from your source data set. 

Step 2: 

Set up an ‘Update Record’ step to link the existing record you found in Table A from the previous step with the new (or existing) record in Table B.

In the ‘Record’ field, use the Airtable ID for the record found in the previous step.

Existing record found in Table A

*HERE’S THE KEY STEP*

In the field where you want to update the linked Table B records to also include the new record your created (or that already exists) in Table B, you need to also include the existing linked record IDs when you make the update. You can pull them from your previous search step.

 

Here’s how it looks in Zapier for me:

Hope this is helpful! This is my first post ever, so if anyone needs clarification, I’m happy to provide it. :grin:

Hey all,

I ran into this problem too where Zapier would override the existing linked records in a field and replace them with the single linked record value from the update function. I tried the solution that @Mitch wrote, but it didn’t work for me. As @Wemakefuture mentioned, this only works if your linked tables have a one-to-many relationship. 

In Mitch’s example, he was able to look up the corresponding record from Table B and perform an ‘Update Record’ function in Table A to add in the linked record ID from Table B. But Table A records will only be linked to ONE record from Table B. 

In my case, I have two tables with a many-to-many relationship, so Mitch’s solution didn’t work (or I misunderstood it).

Here’s what I did. This solution will work for both many-to-many AND one-to-many relationship tables:

TL;DR

When you update a linked record field, you have to also include the existing linked record IDs along with the new one you want to add, separated by a comma. You can find the existing linked record IDs by searching for that existing record using a ‘Find Record’ step in Zapier. 

Here’s my example:

Table A has location records:

Table A has location records. ‘LocationID’ is unique.

Table B has ContactMap records:

Table B has ContactMap records. ‘ContactMap_ID’ is unique.

A contact can be associated with multiple locations, and a location can be associated with multiple contacts.

If I am creating one of these records from scratch, Mitch’s solution works, but if a record already exists and I simply want to update it with additional linked records, it breaks. 

Here’s what I did to make sure Zapier doesn’t erase the existing linked records when it updates:

In Airtable:

In both tables, set up a unique ID column and a linked record column that links your two tables together.

In Zapier:

Step 1: Pull the data that you want to use to create a record in Table B into Zapier with a trigger.

Step 2: Set up a ‘Find a Record’ step to search for an existing record in Table A. In this use case, you’re expecting to find an existing record in Table A that has other records linked to it. 

In the Search Value field, enter in whatever unique identifier you’re using. For contacts, we use email address, but it could be an invoice number or something else that comes in from your source data set. 

Step 2: 

Set up an ‘Update Record’ step to link the existing record you found in Table A from the previous step with the new (or existing) record in Table B.

In the ‘Record’ field, use the Airtable ID for the record found in the previous step.

Existing record found in Table A

*HERE’S THE KEY STEP*

In the field where you want to update the linked Table B records to also include the new record your created (or that already exists) in Table B, you need to also include the existing linked record IDs when you make the update. You can pull them from your previous search step.

 

Here’s how it looks in Zapier for me:

Hope this is helpful! This is my first post ever, so if anyone needs clarification, I’m happy to provide it. :grin:

A simple and elegant solution. Thank you 

Reply