I have a field string that pulls info from other fields, but I don't want parts of the string to show if blank

  • 0
  • 1
  • Question
  • Updated 4 years ago
  • Answered

So I have field called Black Out Window.  This is to show me when a title I have is out of sale (so to speak).  I have four formats that can be out of sale:

  • P:HD
  • P:SD
  • R:HD
  • R:SD

For me to set up a title I need to use this string and put in the date values to take them out of sale:

  • P:HD:US::,P:SD::,R:HD:US::,R:SD:US::

Now values to this string get filled in from other fields pulling information of those dates.  This formula is set up like this:
"P:HD:US:"&[Black Out Start (HD:EST)]&":"&[Black Out End (HD:EST)]&",P:SD:"&[Black Out Start (SD:EST)]&":"&[Black Out End (SD:EST)]&",R:HD:US:"&[Black Out Start (HD:VOD)]&":"&[Black Out End (HD:VOD)]&",R:SD:US:"&[Black Out Start (SD:VOD)]&":"&[Black Out End (SD:VOD)]&""

That works perfect if all the fields are used.  However what I am looking to do it not always show all the dates.    Sometimes only one format is going out of sale.  For example I might only have the P:SD going out of sale (and the other formats would remain in sale).  So I would only want to show this part of the string
  • P:SD:"&[Black Out Start (SD:EST)]&":"&[Black Out End (SD:EST)]

Is there a way I can set this up to say "Ignore the fields that are blank and only show the fields that have dates in them and only show that part of the string"?



Photo of Cecelia

Cecelia

  • 160 Points 100 badge 2x thumb

Posted 4 years ago

  • 0
  • 1
Photo of QuickBaseCoach App Dev./Training

QuickBaseCoach App Dev./Training, Champion

  • 65,096 Points 50k badge 2x thumb
The List function separates non blank values https://www.quickbase.com/db/6ewwzuuj?a=dr&r=e2&rl=bt7

List(","

IF(not isnull ([Black Out Start (HD:EST)]) or not isnull([Black Out End (HD:EST)]),

  "P:HD:US:"&[Black Out Start (HD:EST)]&":"&[Black Out End (HD:EST)]),

if(not isnull([Black Out Start (SD:EST)]) or not isnull([Black Out End (SD:EST)]),

  P:SD:"&[Black Out Start (SD:EST)]&":"&[Black Out End (SD:EST)]),



etc

) // close the List




 




Photo of Xavier Fan

Xavier Fan, Champion

  • 410 Points 250 badge 2x thumb
How do you determine if a format is going out of sale?

Is it that "Black Out Start" and "Black Out End" for that format are blank?  Or just either Start or End is blank for that format?

Once you know that - you can put "IF" statements around each part, to determine whether they show.
Photo of Cecelia

Cecelia

  • 160 Points 100 badge 2x thumb
How we determine if a title is out off sale is if there is a date within a window of time.  For example: title A for the format for SD/EST it goes out of sale window starting from 10/13/15 and comes back in sale on the end date of 12/15/15.  So essential this is out of sale dates are from today until December 15th. Make sense?  And if the dates are blank, then that says it is still in sale.  So therefore I can't have the string show up for those blanks.
Photo of Cecelia

Cecelia

  • 160 Points 100 badge 2x thumb
How do I close out the formula?  I get a syntax error
Photo of Cecelia

Cecelia

  • 160 Points 100 badge 2x thumb
So this is what I have come up with but I am getting errors



List(","

IF(not isnull ([Black Out Start (HD:EST)]) or not isnull([Black Out End (HD:EST)]),

  "P:HD:US:"&[Black Out Start (HD:EST)]&":"&[Black Out End (HD:EST)])",


if(not isnull([Black Out Start (SD:EST)]) or not isnull([Black Out End (HD:VOD)]),

  "P:SD:US"&[Black Out Start (SD:EST)]&":"&[Black Out End (SD:EST)])",


if(not isnull([Black Out Start (HD:VOD)]) or not isnull([Black Out End (HD:VOD)]),

  "R:HD:US"&[Black Out Start (HD:VOD)]&":"&[Black Out End (HD:VOD)])",


if(not isnull([Black Out Start (SD:VOD)]) or not isnull([Black Out End (SD:VOD)]),

  "R:SD:US"&[Black Out Start (SD:VOD)]&":"&[Black Out End (SD:VOD)]"))
Photo of QuickBaseCoach App Dev./Training

QuickBaseCoach App Dev./Training, Champion

  • 65,096 Points 50k badge 2x thumb
What is the error message? You may need to wrap each date field with ToText([my date field])
Can you post a picture of the screen shot with your Question?
Photo of Cecelia

Cecelia

  • 160 Points 100 badge 2x thumb
Here is the screen shot



Just so I am clear...if HD/EST has no dates filled in I don't want this part of the string to show up P:HD:US: and the dates associated with.  And to do this for all of these so that I can have different combinations if need be.



thanks!
Photo of QuickBaseCoach App Dev./Training

QuickBaseCoach App Dev./Training, Champion

  • 65,096 Points 50k badge 2x thumb
Try this - there were some extra quotes at the end of your IFs which i did not have in my post.
List(","

IF(not isnull ([Black Out Start (HD:EST)]) or not isnull([Black Out End (HD:EST)]),

  "P:HD:US:"&[Black Out Start (HD:EST)]&":"&[Black Out End (HD:EST)]),


if(not isnull([Black Out Start (SD:EST)]) or not isnull([Black Out End (HD:VOD)]),

  "P:SD:US"&[Black Out Start (SD:EST)]&":"&[Black Out End (SD:EST)]),


if(not isnull([Black Out Start (HD:VOD)]) or not isnull([Black Out End (HD:VOD)]),

  "R:HD:US"&[Black Out Start (HD:VOD)]&":"&[Black Out End (HD:VOD)]),


if(not isnull([Black Out Start (SD:VOD)]) or not isnull([Black Out End (SD:VOD)]),

  "R:SD:US"&[Black Out Start (SD:VOD)]&":"&[Black Out End (SD:VOD)]))
Photo of Cecelia

Cecelia

  • 160 Points 100 badge 2x thumb
hmm...still getting the same error

List(","

IF(not isnull ([Black Out Start (HD:EST)]) or not isnull([Black Out End (HD:EST)]),

  "P:HD:US:"&[Black Out Start (HD:EST)]&":"&[Black Out End (HD:EST)]),


if(not isnull([Black Out Start (SD:EST)]) or not isnull([Black Out End (SD:EST)]),

  "P:SD:US"&[Black Out Start (SD:EST)]&":"&[Black Out End (SD:EST)]),


if(not isnull([Black Out Start (HD:VOD)]) or not isnull([Black Out End (HD:VOD)]),

  "R:HD:US"&[Black Out Start (HD:VOD)]&":"&[Black Out End (HD:VOD)]),


if(not isnull([Black Out Start (SD:VOD)]) or not isnull([Black Out End (SD:VOD)]),

  "R:SD:US"&[Black Out Start (SD:VOD)]&":"&[Black Out End (SD:VOD)]))
Photo of QuickBaseCoach App Dev./Training

QuickBaseCoach App Dev./Training, Champion

  • 65,096 Points 50k badge 2x thumb
Cecelia,
Can you contact me via the information in my profile and I will have a look at that field in your app myself. I cannot immediately see the syntax error and it would be easier if I had access to your app. Just click on my profile and it will give contact information.
Photo of Cecelia

Cecelia

  • 160 Points 100 badge 2x thumb
Mark,
Thanks so much for cleaning this up.  It works exactly as I wanted.  Really appreciate the help.

Here is the final formula

List(",",

If(not IsNull([Black Out Start (HD:EST)]) or not IsNull([Black Out End (HD:EST)]),

  "P:HD:US:" & [Black Out Start (HD:EST)] & ":" & [Black Out End (HD:EST)]),


If(not IsNull([Black Out Start (SD:EST)]) or not IsNull([Black Out End (SD:EST)]),

  "P:SD:US:" & [Black Out Start (SD:EST)] & ":" & [Black Out End (SD:EST)]),


If(not IsNull([Black Out Start (HD:VOD)]) or not IsNull([Black Out End (HD:VOD)]),

  "R:HD:US:" & [Black Out Start (HD:VOD)] & ":" & [Black Out End (HD:VOD)]),


If(not IsNull([Black Out Start (SD:VOD)]) or not IsNull([Black Out End (SD:VOD)]),

  "R:SD:US:" & [Black Out Start (SD:VOD)] & ":" & [Black Out End (SD:VOD)])
)