Jul 20, 2022 12:04 AM
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!
Jul 20, 2022 04:33 AM
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
Jul 20, 2022 09:10 AM
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
)))
Jul 20, 2022 01:34 PM
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')
))))