Skip to main content

Hi everyone,


I have a Table that has two Email columns.

One which the Email provided by the person at signup (Email), and one that tracks every Email provided at a signup for an Event (Email copy).

The Email copy column is a rollup with arrayunique and gives back every unique Email.

Now since most give the same Email at general signup and at Event signup, I’d like to have an Arrayunique on both columns, yet when I do this it gives me:



The Formula in the Final Email column is

ARRAYUNIQUE(Email & ", " & {Email copy})


Is there any way to make this work?


Any Idea appreciated.


Thank You

Airtable’s array functions only work on arrays generated by other fields. I haven’t found a way to create an array from scratch, even though the array function reference makes it appear that that’s possible.


If you were using ARRAYUNIQUE on {Email copy} alone, it would work (and by “work” I mean that it would see {Email copy} as an array, and operate on it as such). In your formula, though, you’re concatenating that array with a string from the {Email} field, which generates an output string as a result. ARRAYUNIQUE no longer has an array to work with, which is why it’s not removing the duplicate email addresses.


On a side note, the reason that the commas from {Email copy} don’t appear in your formula output is because the commas you see in that rollup field are only there for display clarity. When turning an array into a string, Airtable just mashes all the array items together without any separators.


If your goal is to pull {Email} out of the {Email copy} array if it’s there, the formula below will work, though the end result will still be a string, so that may affect any other processing you want to do with the email addresses elsewhere.


Email & ", " &

IF(

    FIND(

        Email,

        "" & {Email copy}

    ),

    SUBSTITUTE(

        ARRAYJOIN(

            {Email copy},

            ", "

        ),

        Email & ", ",

        ""

    ),

    ARRAYJOIN(

        {Email copy},

        ", "

    )

)

This will only remove {Email} if it’s found in {Email copy} (Test 1 record). Otherwise it will leave it alone (Test 2 record).


Thank You again so much Justin,

this partly works and has some fascinating side effects.

First I removed the & ", " after the Substitute old text value (EMail), because for some reason this didn’t work with me.


Now it works, but only if the E-mail in Email copy is standing alone or the second value of the array.

It doesn’t work if there are multiple values and the E-Mail to substitute is the first one.


I checked and it seems like for some reason he can’t find the string if it’s the first part.


Now that got me trying and I figured out that the check for whether the string is in Email copy is not necessary, as the substitute function actually finds the string or ignores it.


So I left it with this


IF(EMail="", “”, EMail & ", ") & SUBSTITUTE(ARRAYJOIN({Email copy}, ", "), EMail, “”)


And it seems to work perfectly.


Thank You so much for this, couldn’t have done it without you!

Reply


Terms & Conditions