Skip to main content

Import and Update Records with Many-to-Many linking

  • February 5, 2023
  • 4 replies
  • 84 views
Paul_Miller
Forum|alt.badge.img+10

I have unsuccessfully tried automations, extensions, and Zaps and haven't been able to work out an error free  approach to my challenge. I'm hoping for guidance on how to achieve the following:

Import contacts from .csv files and update the linked source field so that the new source is added (and not replace) existing linked records. 

Contacts Table 1 - email - fname, lname, org, source<linked Table 2>

Sources Table 2 - list_name, emails<link to Table 1>  

In descriptive language, I go to events where I get lists of people who came by my booth. I want to enter them into a database (contact info) while keeping track of the various times I've interacted with them (source info). 

I suspected that this was a simple task but I've spent an embarrassingly long time trying to figure it out. Guidance (or a straight up answer) is appreciated. 

 

 

4 replies

kuovonne
Forum|alt.badge.img+29
  • kuovonne
  • Brainy
  • February 6, 2023

I think that the direct many-to-many relationship is going to make this difficult to do without code. If you know how to write Airtable scripts, you could have a script process the CSV file and make the updates. However, this script is not a good project for someone new to coding.

An alternative is to use a junction table, so instead of a single many-to-many relationship, you would have a one-to-many relationship and a many-to-one relationship.

Another possibility is to import the new source information into a new field in your contacts table. Then use a formula field that combines the existing sources with the new source. Finally copy the result of the formula back into the linked record field.

pressGO_design
Paul_Miller
Paul_Miller
Forum|alt.badge.img+10
  • Paul_Miller
  • Author
  • Known Participant
  • February 6, 2023

I think that the direct many-to-many relationship is going to make this difficult to do without code. If you know how to write Airtable scripts, you could have a script process the CSV file and make the updates. However, this script is not a good project for someone new to coding.

An alternative is to use a junction table, so instead of a single many-to-many relationship, you would have a one-to-many relationship and a many-to-one relationship.

Another possibility is to import the new source information into a new field in your contacts table. Then use a formula field that combines the existing sources with the new source. Finally copy the result of the formula back into the linked record field.


Thank you for your response. I am pleased to that it is in-fact more complex than I initially thought and that I wasn't missing something silly.

With the insight you provided, and upon further reflection, I realize that the many-to-many relationship is unnecessary for my needs. I jumped into a not well thought out base and started trying solutions without first identifying my specific needs. Had I taken a few minutes before tackling the project, I may have realized that there was a simpler solution.

Thank you again for taking the time to provide much needed insight. 

pressGO_design
pressGO_design
Forum|alt.badge.img+21

With apologies for the length of this post and the understanding that this is no longer relevant to your case, I'm posting this on the off chance that it might be useful to someone else who comes across this thread in the future and is as script-averse as I am.

Create a table in your base called Trade Show Data. You're going to use this to import the CSV data that you get from the trade show. Have 3 checkbox fields in that table - Found Emails, New Emails & Duplicate Data - and create 3 views: Start Automation, which filters for when all the checkboxes are unchecked (yes, this is the same as the Grid View); New Emails, which filters for when New Emails is checked; and Duplicates filters for when Duplicate Data is checked.

Create a junction table that links email addresses to emails and sources to sources. This is a 3 column field: Name, link to Contacts, link to Trade Shows.

Create an automation where the trigger is “when a record enters a view” and the view is Start Automation There is 1 action and 3 conditional actions in this automation. The action is to find records in the Contacts table where the email matches the email in the Trade Show Data table.

If the automation finds 1 record, then it creates a record in the junction table with the email address and the Trade Show name and updates the record in the Trade Show Data table to check the Found Email box.

If the automation finds 0 records, then it creates a record in the contacts table with the email address and any other info you need from the imported CSV: fname, lname, company, etc etc etc - make sure you have fields for those things - and checks the New Email checkbox.

If the automation finds >1 record, then it updates the record in the Trade Show Data table to check the Duplicate Data box.

Create another automation, again when a record enters a view but this time using the New Emails view. This automation creates a record in the junction table using the email address that and the trade show name, and it updates the checkboxes to uncheck the New Emails box and check the Found Emails box.

When you've gotten the data from the trade show people, your workflow would be

  1. Enter the name of the Trade Show in your Trade Show table. Make sure it matches what it says on the CSV. If you don't like doing this, you can add an action to the first automation that creates a record in the Trade Show table for you before it goes and finds the email addresses.
  2. Use the Upload CSV extension to upload the CSV. Map fields as necessary.
  3. Let the automations do their things.
  4. Resolve duplicates, either manually or by using the dedupe extension. The first few times you do things this way, you may discover a lot of duplicates in your contacts table and it might make sense to re-import only the duplicate emails as another CSV so that the automations run instead of doing it manually.

Phew. Then you delete the Trade Show Data records and celebrate by doing whatever makes your heart sing.

Paul_Miller
Forum|alt.badge.img+10
  • Paul_Miller
  • Author
  • Known Participant
  • February 6, 2023

With apologies for the length of this post and the understanding that this is no longer relevant to your case, I'm posting this on the off chance that it might be useful to someone else who comes across this thread in the future and is as script-averse as I am.

Create a table in your base called Trade Show Data. You're going to use this to import the CSV data that you get from the trade show. Have 3 checkbox fields in that table - Found Emails, New Emails & Duplicate Data - and create 3 views: Start Automation, which filters for when all the checkboxes are unchecked (yes, this is the same as the Grid View); New Emails, which filters for when New Emails is checked; and Duplicates filters for when Duplicate Data is checked.

Create a junction table that links email addresses to emails and sources to sources. This is a 3 column field: Name, link to Contacts, link to Trade Shows.

Create an automation where the trigger is “when a record enters a view” and the view is Start Automation There is 1 action and 3 conditional actions in this automation. The action is to find records in the Contacts table where the email matches the email in the Trade Show Data table.

If the automation finds 1 record, then it creates a record in the junction table with the email address and the Trade Show name and updates the record in the Trade Show Data table to check the Found Email box.

If the automation finds 0 records, then it creates a record in the contacts table with the email address and any other info you need from the imported CSV: fname, lname, company, etc etc etc - make sure you have fields for those things - and checks the New Email checkbox.

If the automation finds >1 record, then it updates the record in the Trade Show Data table to check the Duplicate Data box.

Create another automation, again when a record enters a view but this time using the New Emails view. This automation creates a record in the junction table using the email address that and the trade show name, and it updates the checkboxes to uncheck the New Emails box and check the Found Emails box.

When you've gotten the data from the trade show people, your workflow would be

  1. Enter the name of the Trade Show in your Trade Show table. Make sure it matches what it says on the CSV. If you don't like doing this, you can add an action to the first automation that creates a record in the Trade Show table for you before it goes and finds the email addresses.
  2. Use the Upload CSV extension to upload the CSV. Map fields as necessary.
  3. Let the automations do their things.
  4. Resolve duplicates, either manually or by using the dedupe extension. The first few times you do things this way, you may discover a lot of duplicates in your contacts table and it might make sense to re-import only the duplicate emails as another CSV so that the automations run instead of doing it manually.

Phew. Then you delete the Trade Show Data records and celebrate by doing whatever makes your heart sing.


Thank you for outlining the solution using junction tables. This looks like a sound approach and one I wished I had found straight away when I embarked on the challenge. I hope it is useful to future frustrated users.

pressGO_design
Terms & ConditionsAccessibility statement