Help

Upcoming database upgrades. to improve our reliability at 03:30 UTC on Feb. 25 / 7:30pm PT on Feb. 24. Some users may briefly experience slow load times or error messages. Learn more here

If formula question - help!

Topic Labels: Formulas
138 1
cancel
Showing results for 
Search instead for 
Did you mean: 

Hello-

Any help with if CAMPAIGN THEME is blank, the “-” will not show in the formula?

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(OR({CAMPAIGN TYPE}, {CAMPAIGN THEME}, {EVENT NAME}), " - ", “”)
),
IF({EVENT NAME},
{EVENT NAME})
),
‘EVENT’ & ’ - ’ & {EVENT NAME}
)

A - B: HOLIDAY PROMOTION - HALLOWEEN -

The theme is Halloween.

Thanks!

1 Reply 1

Hey @Scott_Gardner1!
That's one beast of a formula you've got here.

Here's a formatted version of what you originally submitted:

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(
                OR({CAMPAIGN TYPE}, {CAMPAIGN THEME}, {EVENT NAME}), 
                " - ", 
                ""
            )
        ),
        IF(
            {EVENT NAME},
            {EVENT NAME}
        )
    ),
    "EVENT" & " - " & {EVENT NAME}
)

I didn't actually get a chance to test this yet, but here's a version of the formula that should fit your requirements:

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

I spiked in some IF functions inside the OR statements that evaluate the presence of a value inside the Campaign Theme field.
If the field is blank, then we skip it. Otherwise, it will evaluate the OR statement and include it