Formula question concatenate

[Scott_Gardner1]

Hello-

Any idea on how to remove the -

When the Campaign Brand is field is empty so it doesn’t show:
-CAMPAIGN NAME:CAMPAIGN TYPE

And only shows:

CAMPAIGN NAME:CAMPAIGN TYPE

When the Campaign Brand is blank

IF(
{CAMPAIGN NAME},
CONCATENATE(
{CAMPAIGN BRAND},
IF( {CAMPAIGN NAME}, " - " & {CAMPAIGN NAME}),
IF( {CAMPAIGN TYPE}, ": " & {CAMPAIGN TYPE}),
IF( {CAMPAIGN THEME}, " - " & {CAMPAIGN THEME}),
IF( {EVENT NAME}, " - " & {EVENT NAME})

  ),

‘EVENT’
& ’ | ’ &{EVENT NAME}

)

[Jeremy_Oglesby]

Hi @Scott_Gardner1,
A quick and dirty fix would be to just shift your punctuation to the ends of the preceding conditional:

IF(
  {CAMPAIGN NAME},
  CONCATENATE(
    IF({CAMPAIGN BRAND}, {CAMPAIGN BRAND} & " - "),
    IF({CAMPAIGN NAME}, {CAMPAIGN NAME} & ": "),
    IF({CAMPAIGN TYPE}, {CAMPAIGN TYPE} & " - "),
    IF({CAMPAIGN THEME}, {CAMPAIGN THEME} & " - "),
    IF({EVENT NAME}, {EVENT NAME})
  ),
  'EVENT' & ' | ' & {EVENT NAME}
)

But that may still leave you with situations where you have dangling punctuation if there is nothing following one of the earlier fields. The formula above will work only if you ALWAYS have an {EVENT NAME} or an {EVENT}.

Here’s a more robust version that also prevents dangling punctuation:

IF(
  {CAMPAIGN NAME},
  CONCATENATE(
    IF(
      {CAMPAIGN BRAND}, 
      {CAMPAIGN BRAND} & 
      IF(OR({CAMPAIGN NAME}, {CAMPAIGN TYPE}, {CAMPAIGN THEME}, {EVENT NAME}), " - ", "")
    ),
    IF(
      {CAMPAIGN NAME},
      {CAMPAIGN NAME} &
      IF(OR({CAMPAIGN TYPE}, {CAMPAIGN THEME}, {EVENT NAME}), ": ", "")
    ),
    IF(
      {CAMPAIGN TYPE},
      {CAMPAIGN TYPE} &
      IF(OR({CAMPAIGN THEME}, {EVENT NAME}), " - ", "")
    ),
    IF(
      {CAMPAIGN THEME},
      {CAMPAIGN THEME} &
      IF({EVENT NAME}, " - ", "")
    ),
    IF({EVENT NAME}, {EVENT NAME})
  ),
  'EVENT' & ' | ' & {EVENT NAME}
)

[Scott_Gardner1]

Jeremy_Oglesby:

IF(
  {CAMPAIGN NAME},
  CONCATENATE(
    IF(
      {CAMPAIGN BRAND}, 
      {CAMPAIGN BRAND} & 
      IF(OR({CAMPAIGN NAME}, {CAMPAIGN TYPE}, {CAMPAIGN THEME}, {EVENT NAME}), " - ", "")
    ),
    IF(
      {CAMPAIGN NAME},
      {CAMPAIGN NAME} &
      IF(OR({CAMPAIGN TYPE}, {CAMPAIGN THEME}, {EVENT NAME}), ": ", "")
    ),
    IF(
      {CAMPAIGN TYPE},
      {CAMPAIGN TYPE} &
      IF(OR({CAMPAIGN THEME}, {EVENT NAME}), " - ", "")
    ),
    IF(
      {CAMPAIGN THEME},
      {CAMPAIGN THEME} &
      IF({EVENT NAME}, " - ", "")
    ),
    IF({EVENT NAME}, {EVENT NAME})
  ),
  'EVENT' & ' | ' & {EVENT NAME}
)

Thank you very much!! WOW!

One more for you,

If Item Description is blank, I still want to show the Asset Type and Asset Task.

IF(
{MARKETING CAMPAIGN TITLE},
{MARKETING CAMPAIGN TITLE} & IF(
{ITEM DESCRIPTION},
" - " & {ITEM DESCRIPTION}," - “& {ASSET TYPE}
) & IF(
{ASSET TASK},
“: " & {ASSET TASK}
),
IF(
OR(
{ITEM DESCRIPTION},
{ASSET TYPE},
{ASSET TASK}
),
IF(
{ITEM DESCRIPTION},{ITEM DESCRIPTION} &
IF(
{ASSET TASK},” - " & {ASSET TASK} &
IF(
{ASSET TYPE},”: " & {ASSET TYPE}
)
)
)
))

Then if The Item Description is BLANK, I want to add in a Field called Corporate that would be free type that would show anything you typed a the end. ASSET TYPE-ASSET TASK: ‘FREE TYPE’

Thank you!

[Jeremy_Oglesby]

You’ve got some duplication going on here, and dependencies that I don’t think you actually want (IF’s nested inside of IF’s, that I don’t think you actually mean to have nested).

I don’t want to make assumptions about what you actually want in your final output, but it’s a bit difficult to discern your intentions from your formula, because it seems like you might have lost the forest for the conditional trees, so to speak, while writing it.

Could you instead:

1. List the fields you have that are involved in this formula
2. Describe what you want the output to look like, providing an example of the variations

[Scott_Gardner1]

Thanks Jeremy-

The Key Fields are:

MARKETING CAMPAIGN TITLE

If there is a MARKETING CAMPAIGN TITLE then it overrules the ITEM DESCRIPTION and it doesn’t show.

If an ASSET TYPE AND ASSET TASK are selected, I want it to show always.

And if there isn’t a MARKETING CAMPAIGN TITLE or a ITEM DESCRIPTION and only an ASSET TYPE AND ASSET TASK, I want the option if the field CORPORATE has text in it - then it shows also.