Hello! I have a Base with several complex formulas that I’m trying to pair down to be a bit more streamlined. If been trying to get the following formula to work for days with no luck. If anyone can help, it would be super appreciated… Here’s the setup:
Title Category (Detail) / Single Select Field: Feature, Series (Comedy), Series (Documentary)… etc.
Title Category (Main) / Nested IF Formula Field: This formula looks and the Genre Single select field and determines whether the record falls under four more macro-categories: Series, Feature, Sizzle, or Other.
Title Release Date / Date Field: Date field that every record has formatted as YYYY-MM-DD
Season Number / Single Select Field: Only records for series’ have this selected. If the record is for a movie it is left blank
The formula field I need help with is titled Year/Season For Campaign Title. What I want this formula to do is the following:
IF Title Category (Main) is Series, then use Concentrate to put parenthesis around the Season # field.
IF Title Category (Main) is anything else, then use Concentrate to put parenthesis around the release date in YYYY format.
Lastly, if there is no release date and the Title Category (Main) field is anything but series than put (TBD).
The result of the Year/Season For Campaign Title field is used to make up the record field. I use a formula in the record field to combined the title of the show with whatever is the result of Year/Season For Campaign Title. So like Name of Series (S1), Name of Movie (2002), Name of Movie (TBD). Is an example of what is in the far let.
The formula I have is this:
IF({SeasonNumber}=BLANK(),CONCATENATE("(",{Title Release Date}!="",DATETIME_FORMAT({Title Release Date},‘YYYY’),")"),CONCATENATE("(",{SeasonNumber},")"))
The results are less than spectacular:
(S1)
(S1)
(S1)
(S2)
(TRUE2020)
#ERROR!
#ERROR!
I have to fix the the (YYYY) result because I don’t want the “TRUE” in there. And on top of that I have to fix the formula so that instead of #ERROR! I get (TBD). How do I accomplish this? Any thoughts?
