Formula - sperate by comma

[Scott_Gardner1]

Hello-

I have this one also:

IF(
{MARKETING CAMPAIGN},
CONCATENATE(
{MARKETING CAMPAIGN},
IF( {ASSET TYPE}, " | " & {ASSET TYPE}),
IF( {ASSET TASK}, " - " & {ASSET TASK}),
IF( {CAMPAIGN TYPE}, ": " & {CAMPAIGN TYPE}),
IF( {CORPORATE TASK DESCRIPTION}, " - " & {CORPORATE TASK DESCRIPTION})

),
‘MARKETNG REQUEST’
& ’ | ’ &{ASSET TYPE}
& ': ’ &{ASSET TASK}
& ': ’ &{Item Description})

Need to have a comma separation when the item description is multiple so it doesn’t look like this:

abcd

Need - a,b,c,d

Thanks!

[WilliamPorter]

Can you give an actual example of a value in {Item Description} that’s causing a problem, that is, when the “item description is ‘multiple’” (as you put it)?

I’m guessing the solution here will involve either the SUBSTITUTE() function or one of the ARRAY functions but without knowing what you’re actually dealing with it, I personally don’t feel confident about a recommendation.

[Scott_Gardner1]

Its coming from a linked field.

[WilliamPorter]

Look into the ARRAY functions: ARRAYJOIN() or ARRAYUNIQUE() will probably do the trick. But be aware that plugging (say) the ARRAYUNIQUE() function into your larger formula might produce some curious results. You might want to wrap the result in square brackets or something else.

[Scott_Gardner1]

I just looked and its being pulled from a Look-up Field that has comma’s displayed.

[WilliamPorter]

Um, isn’t that what you WANT? In your original post you said,

Need to have a comma separation when the item description is multiple so it doesn’t look like this:
abcd
Need - a,b,c,d

[Scott_Gardner1]

Yes but the look up showing like this = A,B,C,D

The formula shows:

ABCD

Its when the look up has multiple items (Featured Marketing Games) - it doesn’t separate by comma.

IF({MARKETING CAMPAIGN},
CONCATENATE(
{MARKETING CAMPAIGN},
IF( {ASSET TYPE}, " | " & {ASSET TYPE}),
IF( {ASSET TASK}, " - " & {ASSET TASK}),
IF( {CORPORATE TASK DESCRIPTION}, " - " & {CORPORATE TASK DESCRIPTION})

),
‘MARKETNG REQUEST’
& ’ | ’ &{FEATURED MARKETING GAMES}
& ’ | ’ &{ASSET TYPE}
& ’ - ’ &{ASSET TASK})

[kuovonne]

Scott_Gardner1:

Yes but the look up showing like this = A,B,C,D

The formula shows:

ABCD

Lookup fields are the most problematic fields to use in formulas. In this case the lookup is an array that is displayed with commas in the lookup itself, but the commas don’t actually exist in the underlying data structure.

Try using ARRAYJOIN, as William suggested.

ARRAYJOIN({Item Description}, ",")

You can also consider using a rollup field instead of a lookup field.

[Scott_Gardner1]

Thank you both! How would that look in the actual formula since I have an if blank situation with the Marketing request free type.

[Scott_Gardner1]

Thank you, I used the array join with the roll up and it worked great!

Just need to remove the ( :slightly_smiling_face: character when the Marketing Campaign field is blank, and the Marketing request auto fills, how can we make sure that if item description is blank, the ( :slightly_smiling_face: will not show?

‘MARKETNG REQUEST’
& ’ | ’ &{ASSET TYPE}
& ': ’ &{ASSET TASK}
& ': ’ &{Item Description})

Thank you!