Replace/Substitute Multi Select Field Values with Values from Another Field

[Jess_Helmstette]

Hello! I am new to Airtable and have been chugging along until now lol. I have done hours of research and received advice from a few people but I am still very much stuck.

I have a multi select field and I am trying to write a formula that takes the options in that field and replaces/substitutes them with values from other fields.

So let’s say my multi select field contains the following values:
Buyer
Agent
Closing Attorney
Closing Attorney Assistant

I want a formula that looks at each value and replaces each one with an email address from a lookup field.
‘Buyer’ would be {Buyer Email}
‘Agent’ would be {Agent Email}
‘Closing Attorney’ would be {Closing Attorney Email}’
‘Closing Attorney Assistant’ would be {Closing Attorney Assistant Email}

The email addresses would need to have commas separating them.

And if there is no email address in any of the lookup fields it would just ignore it.

Please help!

[TheTimeSavingCo]

Hi Jess, I’ve put something together here for you that should do what you’re looking for

You can duplicate the base to view the formulas

The idea is we just use the following code for each email we need to display, replacing the variables where needed:

IF(
    AND(
      FIND(
        "Buyer", {Multi Select}
      ),
      {Buyer Email}
    ),
    {Buyer Email} & ","
  ) & 

I used a REGEX_REPLACE to get rid of the trailing comma and you can remove that if you want

[Jess_Helmstette]

Genius!! Thank you so much for taking the time to help me. :hugs:

[Jess_Helmstette]

Hey Adam!

I am running into a couple snags.

1. The “Buyer” lookup field is multi select so when there is more than one buyer, it combines the email addresses with no comma. See image below.
   

2. For some reason it is adding the buyer emails in the CC field when the “Buyer” is not an option. See image below.
   

Any assistance you could give would be greatly appreciated!

[TheTimeSavingCo]

Jess_Helmstetter:

The “Buyer” lookup field is multi select so when there is more than one buyer, it combines the email addresses with no comma. See image below.

Weird! Could I get a screenshot of the field(s) that contain the data buyerone@email.combuyeringtwo@email.com please? That’ll help me figure out what’s going on

Jess_Helmstetter:

For some reason it is adding the buyer emails in the CC field when the “Buyer” is not an option. See image below.

Ah, yeah, that’s because the formula just looks for the word Buyer. Since the CC (from Email Templates) field contains Buyer Agent etc, and thus the word Buyer, it fulfils the condition and shows the buyer emails

I think the only way around this would be to make the text we’re looking for unique, so we would have to change Buyer to something that wouldn’t be found in your other options, maybe Buyer Primary, Buyer Client or some such

[Jess_Helmstette]

Adam_TheTimeSavingCo:

Jess_Helmstetter:

The “Buyer” lookup field is multi select so when there is more than one buyer, it combines the email addresses with no comma. See image below.

Weird! Could I get a screenshot of the field(s) that contain the data buyerone@email.combuyertwo@email.com please? That’ll help me figure out what’s going on

See below!

Adam_TheTimeSavingCo:

Jess_Helmstetter:

For some reason it is adding the buyer emails in the CC field when the “Buyer” is not an option. See image below.

Ah, yeah, that’s because the formula just looks for the word Buyer. Since the CC (from Email Templates) field contains Buyer Agent etc, and thus the word Buyer, it fulfils the condition and shows the buyer emails

I think the only way around this would be to make the text we’re looking for unique, so we would have to change Buyer to something that wouldn’t be found in your other options, maybe Buyer Primary, Buyer Client or some such

Is there any other way to tackle this one? It’s not as simple as renaming the Buyer field because this isn’t the only one that would display this behavior. I also have Lender / Lender Assistant, Closing Attorney / Closing Attorney Assistant, etc.

Thank you!!

[TheTimeSavingCo · Accepted solution]

Hi Jess, thanks for the screenshots

For the missing comma thing, we’ll need to put an ARRAYJOIN() around the lookup field we’re displaying the emails from, so that it looks something like this:
ARRAYJOIN({Buyer Email (from Buyer)})

This should solve that issue I think

Jess_Helmstetter:

Is there any other way to tackle this one? It’s not as simple as renaming the Buyer field because this isn’t the only one that would display this behavior. I also have Lender / Lender Assistant, Closing Attorney / Closing Attorney Assistant, etc.

Ahh, understood. Hmm, we could modify the formula so that it looks like the following?

FIND(
    "Buyer,", {Multi Select} & ","
  ),

I’ve made the updates to the base linked above as well

[Jess_Helmstette]

It’s all working beautifully!!! Thank you so much! You have no idea how much I appreciate your help. :hugs: