ARRAYJOIN doesn't take recognise separator

[Benjamin_Ho]

> As detailed on this page, the ARRAYJOIN function is supposed to let you choose a separator. However, no matter what I type for the separator, it remains as a comma without a space after it.

The relevant part of my formula code:
(ARRAYJOIN(Author, ", ")

{Author} contains two items in the array: “Surname, Name” and “Surname2, Name2”.

What I get after ARRAYJOIN: “Surname , Name,Surname 2, Name2”.

Just in case, my full formula code:
SUBSTITUTE(CONCATENATE(SUBSTITUTE(ARRAYJOIN(Author, “, “), '”’, ‘’), " (”, Year, "). ", Title, ". ", Publication, ", ", Volume, “(”, Issue, "), ", Pages, “.”), “()”, “”)

[Arlo_Haskell]

I was struggling with this too, but just got it to work. I think the documentation here on ARRAYJOIN is not very clear.

Above documentation shows brackets [ ] in the formula like this ARRAYJOIN([1,2,3], "; ")

But I got it to work just by taking the brackets out:

ARRAYJOIN(values, "; ") succeeds in returning the string with a semicolon separator instead of a comma

[Helen_Vaskevitc]

Hm, I tried this without the brackets, and it only will list the first element. Here’s my formula:

ARRAYJOIN({F&F}, Weekly, {Custom Rate}, ", ")

This is to provide a list of potential discounts a customer received. The three discounts available are Friends & Family, Weekly and Custom Rate. But, it only lists if they got the Friends & Family discount, because that is listed first. It won’t list the other two.

[AlliAlosa]

Hi there!

I believe you’re having issues because, to my knowledge, the formula is really only meant to separate one array at a time. Here is a workaround that might work for you:

ARRAYJOIN({Field 1}, ", ") & IF(AND({Field 1}, {Field 2}), ", ") & ARRAYJOIN({Field 2}, ", ") & IF(AND(OR({Field 1}, {Field 2}), {Field 3}), ", ") & ARRAYJOIN({Field 3}, ", ")

Or using your field names:

ARRAYJOIN({F&F}, ", ") & IF(AND({F&F}, {Weekly}), ", ") & ARRAYJOIN({Weekly}, ", ") & IF(AND(OR({F&F}, {Weekly}), {Custom Rate}), ", ") & ARRAYJOIN({Custom Rate}, ", ")

This will treat each array separately (pun intended? :slightly_smiling_face: ) and add commas (", ") in between each one if necessary.

[W_Vann_Hall]

@AlliAlosa’s solution is correct, but it’s necessary only if {F&F}, {Weekly}, and {Custom Rate} are all lookup or rollup fields — that is, if they are all retrieved from a linked record in another table. If they are all simple fields in your current table, then all you need is her formula minus the ARRAYJOIN() functions:

{F&F}&
IF(
    AND(
        {F&F},
        {Weekly}
        ),
    ', '
    )&
{Weekly}&
IF(
    {Custom Rate},
    IF(
        OR(
            {F&F},
            {Weekly}
            )
        ),
    ', '&{Custom Rate}
    )

[Helen_Vaskevitc]

@W_Vann_Hall
Do some of your IF() formulas have fewer than three arguments? (The first and third IFs.) I haven’t seen that before, how does that work?

[Helen_Vaskevitc]

@W_Vann_Hall
Alright, with a few tweaks to the locations of commas, I modified your formula and go it to work. here’s what I did:

{F&F} &
IF(
AND(
{F&F},
Weekly
),
', ',
’
') &
Weekly &
IF(
{Custom Rate},
IF(
OR(
{F&F},
Weekly
),
', ',
‘’
) &
{Custom Rate}
)

EDIT
Well gosh. I don’t know how to make it show indents in here. Sorry for the illegibility of my formula.

[W_Vann_Hall]

Helen_Vaskevitch:

Alright, with a few tweaks to the locations of commas, I modified your formula and go it to work.

Yep, you fixed it: my original would have given you “, {Custom Rate}” whenever the customer only had {Custom Rate} defined. Unfortunately, I had the remains of three broken teeth extracted yesterday (plus fillings in two more), and Airtable and pain medicine is not a good combination. :winking_face:

Helen_Vaskevitch:

Do some of your IF() formulas have fewer than three arguments? … I haven’t seen that before, how does that work?

Yes, the ‘ELSE’ portion of the IF() statement is optional; if the matching criteria is not met, nothing happens. In my experience, it’s preferable to let Airtable handle the NOP (No Operation) branch, as it avoids one accidentally casting the response to an unexpected data type. That is, it’s better to write

IF(
    {This},
    {That}
    )

than

IF(
    {This},
    {That},
    BLANK()
    )

or (especially!) than

IF(
    {This},
    {That},
    ''
    )

In your case it doesn’t matter, as the formula is going to return a text string containing comma-separated values since we’re concatenating the results of multiple IF() statements. But in the case of a standalone IF(), assuming {That} is a numeric value, the first two examples above would return a number, but the last would return a numeral — that is, a text representation: '42' instead of 42. If you’re trying to use that result mathematically in a formula, you’d get either #ERROR! or NaN.

---

Also, re: indents. Discourse (the forum software used here) supports [most] Markdown encoding. (Until I discovered that, I tore my hair out trying to force indentation, as my earliest posts will show.) To kick Discourse into preformatted text/code mode, precede and follow your indented code with 3 backtick characters (’'’) (typically, the unshifted tilde [’~’] character to the left of the 1 key) on a line by themselves{

[``` goes here]
Indented
    Text
        Goes
    Here
[``` goes here]

(Text colors are Discourse’s attempt to provide syntax highlighting; I’m not sure what language it assumes code blocks to be.)

[Helen_Vaskevitc]

Thank you! So much useful information here! Particularly re: if statements, I’ve run into that exact situation before, where I put ‘’ as the third argument, and then had the result formatted as text instead of a numeral. So this is very good to know!