Nested IF with Substitute Formula

[Tom_R]

Hi all,

I’m trying to create a formula in a field that will take the value from one of two fields, whichever is not empty. One field is a multi-select, and the other a lookup.

In essence this is quite simple for the multi-select field and the rollup field records (that are standalone strings), given the following formula:

IF({Multi-select field}, {Multi-select field}) & IF({Rollup field},{Rollup field})

However, if a primary key in this table is linked to more than one linked record that the rollup field is referencing, then the rollup field outputs each of their associated rollup records separated by a comma.

Thus, if the same above formula applies, I get an output this removes the comma and essentially combines all these rollup values into one long string. To address this, and to copy exactly how the values in the rollup field look (no comma removal), I nested a substitute formula to add any commas back in for when my second IF statement is true, but am getting an error (only for the rollup field).

IF({Multi-select field}, {Multi-select field}) & IF({Rollup field},SUBSTITUTE({Rollup field},",",", "))

Is there something wrong with my syntax? Would be super helpful if someone could push me in the right direction!

Thanks in advance.

Regards,
Tom.

[Justin_Barrett]

When pulling data from a rollup field (or a lookup), Airtable doesn’t instantly convert the array into a string. It leaves it as an array, and only displays it with commas for clarity purposes. SUBSTITUTE doesn’t work on arrays, only strings, so that’s likely why it’s throwing an error.

Because you want to convert that array into an actual string, using ", " as a separator, you can use the ARRAYJOIN function. Here’s your formula modified with that in mind:

IF({Multi-select field}, {Multi-select field}) & IF({Rollup 
field},ARRAYJOIN({Rollup field},", "))