Skip to main content

Switch formula with 2 single select fields

  • July 20, 2022
  • 3 replies
  • 33 views
Scott_Gardner1
Forum|alt.badge.img+15

Hello-

I am looking for a formula that I am stumped on:

I have a field with a single select that is called Campaigns.

Campaigns has three categories, A, B, C.

A, B, and C also represent tasks which is another field.

These tasks have 20 options that represent a lead time.

That lead time needs to be a workday add formula to a set lookup date that is driving these dates.

So I need the option for all noted: As an example I’ll use this, when you select A from campaigns that equals -30 days, allows you to select all tasks that each have a different lead time associated to then auto subtract workdays from the static look up date.

Does this make any sense, need multiple switch functions?

Thanks!

    3 replies

    Alexey_Gusev
    Forum|alt.badge.img+25
    • Alexey_Gusev
    • Brainy
    • 1261 replies
    • July 20, 2022

    Hi,
    Since you need a value (days), you can just add your campaign value to a result of other field, like this:

    -30*({Campaign}=A)
-20*({Campaign}=B....

    considering TRUE=1, FALSE=0

    Scott_Gardner1
    Forum|alt.badge.img+15
    • Scott_Gardner1
    • Author
    • Known Participant
    • 31 replies
    • July 20, 2022

    Hi,
    Since you need a value (days), you can just add your campaign value to a result of other field, like this:

    -30*({Campaign}=A)
-20*({Campaign}=B....

    considering TRUE=1, FALSE=0


    Thank you!

    For clarity, I need another switch function to represent the Marketing Campaigns:

    If the Single select field type which is Marketing Campaigns is A, B, C, and if one of the below Task Types is selected it will add the days for A=7 + ADS= -14.

    IF({PRODUCT LAUNCH DATE}=BLANK(),BLANK(),
    WORKDAY(
    {PRODUCT LAUNCH DATE},
    SWITCH(
    {TASK TYPE},
    “BOOTH DESIGN”,-21,
    “SHOWCASE VIDEO”,-21,
    “SHOWCASE PHOTOGRAPHY”,-21,
    “LIFESTYLE VIDEO”,-21,
    “LIFESTYLE PHOTOGRAPHY”,-21,
    “APPAREL”,-14,
    “BACKLIT AD”,-14,
    “ADS”,-14,
    “SNAPCHAT ADS”,-14,
    “PR MARKETING GRAPHICS”,-14,
    “END SLATE”,-14,
    “PIN”,-14,
    “PRE ROLL AD”,-14,
    “EMAIL BLAST”,-14,
    “DISTRIBUTION ADS”,-14,
    “PRINT AD”,-14,
    “DIGITAL GRAPHIC”,-14,
    “QUARTERLY DIGITAL SPEND AD”,-14,
    “WEB ADS”,-14,
    “RETAILER KIT”,-14,
    “PRINT GRAPHIC”,-14,
    “OTHER”,-14,
    “GOOGLE DISPLAY ADS”,-14,
    “TENSION BANNER”,-14,
    “DIGITAL SCREEN GRAPHIC”,-14,
    “COPY ONLY”,-7
    )))

    Alexey_Gusev
    Forum|alt.badge.img+25
    • Alexey_Gusev
    • Brainy
    • 1261 replies
    • July 20, 2022

    Thank you!

    For clarity, I need another switch function to represent the Marketing Campaigns:

    If the Single select field type which is Marketing Campaigns is A, B, C, and if one of the below Task Types is selected it will add the days for A=7 + ADS= -14.

    IF({PRODUCT LAUNCH DATE}=BLANK(),BLANK(),
    WORKDAY(
    {PRODUCT LAUNCH DATE},
    SWITCH(
    {TASK TYPE},
    “BOOTH DESIGN”,-21,
    “SHOWCASE VIDEO”,-21,
    “SHOWCASE PHOTOGRAPHY”,-21,
    “LIFESTYLE VIDEO”,-21,
    “LIFESTYLE PHOTOGRAPHY”,-21,
    “APPAREL”,-14,
    “BACKLIT AD”,-14,
    “ADS”,-14,
    “SNAPCHAT ADS”,-14,
    “PR MARKETING GRAPHICS”,-14,
    “END SLATE”,-14,
    “PIN”,-14,
    “PRE ROLL AD”,-14,
    “EMAIL BLAST”,-14,
    “DISTRIBUTION ADS”,-14,
    “PRINT AD”,-14,
    “DIGITAL GRAPHIC”,-14,
    “QUARTERLY DIGITAL SPEND AD”,-14,
    “WEB ADS”,-14,
    “RETAILER KIT”,-14,
    “PRINT GRAPHIC”,-14,
    “OTHER”,-14,
    “GOOGLE DISPLAY ADS”,-14,
    “TENSION BANNER”,-14,
    “DIGITAL SCREEN GRAPHIC”,-14,
    “COPY ONLY”,-7
    )))


    Yeah, I see. You may combine these formulas
    Just a few notes:

    instead of checking field emptiness like
    IF({FIELD}=BLANK(),BLANK(), some_function(FIELD) )
    you may use shorter form
    IF({FIELD}, some_function(FIELD) )

    This community editor turns straight brackets ' " to left-right brackets ’ " " ', which are invalid to use in formula if you copypaste formula from here.
    Use </> (Preformatted text) tag to avoid such behavior.

    since you select from a list of SINGLE SELECT options, and most values are -14, you may use it as default (and, for example, add zero value for empty value) :

    IF({PRODUCT LAUNCH DATE},
WORKDAY({PRODUCT LAUNCH DATE},
SWITCH(
{TASK TYPE},
'',0,
'BOOTH DESIGN',-21,
'SHOWCASE VIDEO',-21,
'SHOWCASE PHOTOGRAPHY',-21,
'LIFESTYLE VIDEO',-21,
'LIFESTYLE PHOTOGRAPHY',-21,
'COPY ONLY',-7,
-14
)))

    and that’s just for fun (I would not use such method in ‘real life’ ), it works the same as your formula at the beginning:

    IF({PRODUCT LAUNCH DATE},
IF({TASK TYPE},
WORKDAY({PRODUCT LAUNCH DATE},-7*(2+
(FIND(LEFT({TASK TYPE},4),'BOOTSHOWLIFE')>0)
-({TASK TYPE}='COPY ONLY')
))))


    Terms & ConditionsAccessibility statement