Merging multiple fields into a single multi-select field

[Justin_Eppley]

Hello Airtablers. Hoping someone out there might be able to provide a solution to a challenge I am having manipulating data between tables. I’ve created a graphic that shows what I am trying to achieve.

Basically, in words what I am trying to achieve is the following:
(1) Using a reference table that is setup where each record is a product with five fields and each of the five fields is a different single text field that is a tag for the product, bring into a separate table where each record is a product a single multi-select field that has all the tags from the five fields in the reference table.

AND

(2) Create a third table, called “Tags”, where the records represent all the unique values across all five tag fields in the Reference table and where one field is a single multi-select field with all the products that have that tag.

For the second issue, from some earlier help form a fellow Airtabler, I discovered that I could link the five tag columns to a tags table and this would get the unique list of tags I am looking for but when I link the five tag columns this way the products are listed in separate fields in the Tags table when I need them to be in one field as a multi-select field.

Thanks in advance for any help. It is definitely greatly appreciated.

[Mohamed_Swella1]

Hi @Justin_Eppley ,

Maybe you need to merge all 5 fields in one field using a formula?

Something like:

IF({Tag 1}, {Tag 1}&
IF({Tag 2}, ", "&{Tag 2}&
IF({Tag 3}, ", "&{Tag 3}&
IF({Tag 4}, ", "&{Tag 4}&
IF({Tag 5}, {Tag 5}
)))))

This formula is very simple but its not taking into consideration if Tag 5 (for example) is filled where Tag 4 is empty if that makes sense.

You can then change this to a multiple select or a Linked table

[Justin_Eppley]

Hi @Mohamed_Swellam. Thanks for the suggestion! The challenge with that solution is that once I convert the field to multiple select after using the formula, the field is now static and when the data source is updated to include either additional tags or new products the field doesn’t update accordingly :frowning: Is there any equivalent in Airtable where when a field is updated (i.e., a record one day doesn’t have a value for tag 2 but later on does) that the formula field updates and then a multiple select field stays in sync?

[Mohamed_Swella1]

You can use an automation to do that.

1 automation will be to watch for updates in the tags and another one will be to watch for new records

[Justin_Eppley]

@Mohamed_Swellam Hmmm… never used automations before in Airtable. I’m struggling to understand how this would help in this situation, though, after playing around with the triggers. What would the logic be? How would an automation work to get the field to return to the state of a formula field to add new values? I’m not really seeing that as an action option after a trigger. I’m probably missing something entirely as I’m also new to Airtable generally. Thanks for your help.

[Mohamed_Swella1]

@Justin_Eppley

Actually, why not link the Tags to the Products table directly? This way when you remove one of the Linked records it will just unlink from the other table?

I think I need to understand the workflow a bit more to be able to help.

[Justin_Eppley]

@Mohamed_Swellam Hmmm… I think recapping where I’m having the issue might help too in a different way. Basically, the “Reference Table” shown in the original post is the starting point. The “Products” and “Tags” tables are both tables that are then being synced to Webflow as two seperate collections on Webflow. The “Tags” field in the “Products” table is a multi-select so that I can map it to Webflow’s multi-reference field type. Similarly, the “Products” field in the “Tags” table is multi-select in order to be a multi-reference field as well.

I think where I’m probably making it extra confusing is why even have the “Products” table? I think that’s what you’re suggesting? The “Products” table in our workflow case actually comes from a separate data source where records will be updated (removed/deleted) depending on inventory levels and this is the table that syncs to Webflow to keep the website’s products “live”. The Reference Table comes from a separate data source that’s more of a static product catalog and thus items are only updated/added, rarely removed. We’re using the reference table as purely a lookup to get details on the products that aren’t visible from the data source we’re using to get the Products Table.

Hope this helps explain the workflow a bit more and gives some more clarity in a potential path forward. Very much appreciate your willingness to further troubleshoot. We’re super excited about the potential we see in using Airtable and hope we can get over this last barrier :slightly_smiling_face:

[JudoHacker]

If your goal is to sync to Webflow, then I agree with @Mohamed_Swellam that you might be better off using ‘link to record’ fields instead of using a multi-select field. A tool like PowerImporter will automatically sync those fields as multi-reference fields in Webflow.

[Justin_Eppley]

@JudoHacker Thanks for the info. And, I do love Powerimporter in a major way. I actually had success already mapping a lookup field as well to multi reference in Webflow. The struggle I am having is that the first go around to get Powerimporter to work I just manually entered multiple tags values in one column in the reference table making the assumption that when I connected to the real data source the manipulation to get into the needed form wouldnt be too challenging… but I digress…lol. I fear the advice y’all are giving might be flying right over my head.

In the reference table, I have 5 fields, not all always containing data, with a singular tag that relates to a product in each field. If I turn each of the five tag columns and link each to the Tags table, I get multiple columns in the Tags table with products. As I understand how I got Powerimporter to work, I need the tags table to be one field of unique tags with ONE field that has all the products associated with the tags for my multi reference field. I think this accomplishable by writing a formula field in the Tags table that brings the products together with comma separation. BUT, my understanding is that to come into Webflow the field would need to be a multi select/linked field that then means it’s no longer a formula field and thus won’t run on autopilot when new data is added to the reference table. Am I understanding this correctly?