Skip to main content

Switch formula with 2 single select fields

  • July 20, 2022
  • 3 replies
  • 73 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!

    This topic has been closed for replies.

    3 replies

    Alexey_Gusev
    Forum|alt.badge.img+25

    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
    • 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

    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