What's the Easiest Way to Update a Linked Record Field with a List from Excel?

[Joel_Andrews1]

I have a base that mirrors a health record system. I have a Forms table and a User Role table connected by a linked record field. Whenever new or existing forms get added to user roles, I have been manually adding and linking the forms to the associated roles. I can pull an updated list of forms associated with a given user role from the system and clean it up in Excel, but when I try to import the new list and merge it with the existing list using the CSV import tool, it always ends up creating duplicates, even when I select "merge with existing records". I've tried using the Import Data tool, but that won't let me update linked record fields.

What am I doing wrong? I wish there was some way I could just copy and paste from the form list in Excel into the Forms field in Airtable and have it just overwrite what's in the cell and create new records as needed.

Thanks!

[Joel_Andrews1]

I just realized that the reason copy/paste probably isn't working is because I have my primary field in my Forms table set as a formula field that concatenates several other fields. I'm guessing that's throwing everything off. Is there a way around this? 

[pressGO_design]

I’ve edited this comment a few times now - apologies for the multiple notifications.

It’s unclear to me whether you’re using Excel as a workaround to fix a problem in your Airtable or you’re using it because there is other data that you get in Excel and it’s just easier for you to combine the new with the old in Excel and then import it into Airtable.

If it’s the former, then I would suggest creating a join table composed of a bunch of one-to-one relationships with users and forms that will allow you to create views and edit things more easily.

On the other hand, if it’s the latter, I would suggest creating another table in your base where you can paste in updates and then use either formulas or automations (or both) to manipulate the data in the updates.

Can you share a little more about your workflow and how Excel fits into the mix?

[Joel_Andrews1]

I'm copying the current list of forms associated with a given user role from a report generated in the web-based application. I use Excel to clean up and alphabetize the list before importing to Airtable. The report adds a bunch of leading spaces before the form names, so I use the TRIM function to remove those. I've been just adding the column name "Form" above the forms, and adding a "Role" column and filling it with the associated role name. When I use the CSV import method, I still end up getting some duplicates, even when the Form names are identical, and I select "merge with existing records".