Update tags on a bulk upload without replacing the ones that are already there

[tuckero]

Hello - 

We have a table of home improvement companies and we use the multiple select feature to indicate what services they offer. There are 82 services under the select feature and we add as many as the company does. 

For example, Best Painting is tagged for "Interior Painting" "Exterior Painting" etc. 

During a bulk upload, we use the website as a unique ID, and we want to add services to existing companies without replacing their old ones. 

Best Painting is on our bulk upload list, and we want to add "Cabinet Painting" to their services. When we do this using the CSV extension, it always clears and replaces the existing services. We have merge records toggled on and it will say "1 record will be updated" - but it always replaces instead of adds. 

Any tips?

[ScottWorld]

Is this a one-time upload, or will you be doing it on a regular basis?

[Dan_Montoya]

It sounds like it would be done on a regular basis.  In one of our apps, we handle an emailed CSV import by importing into a "processing" table.  When is record is created/updated in the processing table, an automation looks up the record in the main table.  When using this approach your automation can append to a field by using the new value from the processing table and the existing value in the main table separated by a comma.

[tuckero]

Regular basis 

[ScottWorld]

I can think of at least 3 ways to do this off the top of my head:

1. Dan’s approach above should work, and you don’t have to use external apps to make it happen. You can stick with Airtable's automations, but you'll just need to create that extra table that Dan was talking about.

2. A similar approach would be to merge your data into the existing table, but bring your services into a brand new field, and then use Dan's technique of merging the 2 fields together with an Airtable automation afterwards.

3. You can also drop your CSV file into a cloud folder, and then have Make’s CSV modules automatically import and merge the data for you by using the Airtable Upsert Module.

I demonstrate this entire process step-by-step on this Airtable podcast episode.

However, when you get to your multi-select field, you’re diving into the realm of working with arrays, because multi-select fields are arrays.

I discuss working with arrays on this Airtable podcast episode, but the screenshot below will show you how to merge the old values with the new values, while still accounting for the fact that some rows might be brand new so they might not have an old value.

This screenshot translates to: "If the Services array is previously empty (because it's a brand new record that you're adding into Airtable), then just insert the value from column 3 of your CSV file into the services field. Otherwise, add the new services into the existing services array, and get rid of any duplicate values within the array."

p.s. If your company has a budget for your project and you’d like to hire an expert Airtable consultant to help you with any of this, please feel free to contact me through my website: Airtable consulting — ScottWorld 

[kuovonne]

Depending on the number of records and the number of automation runs you have each month, you may not want to use up so many automation runs.

Since you have a person manually running the CSV import, the person might as well perform a couple more button clicks. 

Upload the services from the CSV file into a {New Services} field. Then have a formula field combine the existing {Services} field with the {New Services} field. Have the person copy the result of the formula field to the {Services} field, and then clear the {New Services} field for next time.

The formula should check if the {New Service} already exists so it does not repeat it. The formula will also need to add a comma if there are both new and existing services. (Services should not have commas or quotes in their names.) If there could be multiple new services, the formula gets trickier.

Keep the {New Services} and the formula field around for next time. 

[ScottWorld]

Yes, I was actually going to recommend that as the "manual way" of doing it.