Force text into date field
I have a date formula field called [Due in Opentext] that displays dates based on another date field called [To MRC]. However, in a very specific case there are no dates in [To MRC] in which case I'd ...
Forum Discussion
I was afraid this would be the answer :( Thanks for your help with that. I'll try to explain my issue and maybe someone can help.
My app tracks the life cycle of photographs through a post-production process. The field [To MRC] is one of the steps in that process and it is a date field. [Due in Opentext] is also a date that looks up the date in [To MRC] and returns that date with 5 business days added. It's purpose is to predict when an image will be due for publication to our Opentext platform.
Each image has a [Ref ID] field, a number associated with a series of images. So we could have images named 123456_alternate1.tif, 123456_lifestyle.tif and 123456_standard.tif, the [Ref ID] being 123456.
So here's the problem. I want there to be a due date for all images in [Due in Opentext] because all images technically are due in Opentext, but we have to base that date off the field [To MRC]. Any images with the name "standard" in the filename will not have a date in [To MRC] so there is no data available to generate a date in [Due in Opentext]. My original question was, could I add text to [Due in Opentext] for all "standard" files to indicate to my users to check another field. The answer is no.
Next possible solution - can I automatically populate "standard" images with a [Due in Opentext] date by using the same date in [Due in Opentext] for images named "lifestyle"? Because these images both live together in the post-production process and so the due date for a "standard" image is the same as for a "lifestyle".
The really difficult part here is the [Ref ID]. I would need to look up the "lifestyle" image for a [Ref ID], for example 123456, see if there was a "standard" image also under 123456, and then populate the field [Due in Opentext] for that "standard" file with the [Due in Opentext] date from the corresponding "lifestyle" image. There would have to be some kind of match function or something in QB and I'm not aware of one if there is.
Very complicated. Possible? Or a fool's errand?
My app tracks the life cycle of photographs through a post-production process. The field [To MRC] is one of the steps in that process and it is a date field. [Due in Opentext] is also a date that looks up the date in [To MRC] and returns that date with 5 business days added. It's purpose is to predict when an image will be due for publication to our Opentext platform.
Each image has a [Ref ID] field, a number associated with a series of images. So we could have images named 123456_alternate1.tif, 123456_lifestyle.tif and 123456_standard.tif, the [Ref ID] being 123456.
So here's the problem. I want there to be a due date for all images in [Due in Opentext] because all images technically are due in Opentext, but we have to base that date off the field [To MRC]. Any images with the name "standard" in the filename will not have a date in [To MRC] so there is no data available to generate a date in [Due in Opentext]. My original question was, could I add text to [Due in Opentext] for all "standard" files to indicate to my users to check another field. The answer is no.
Next possible solution - can I automatically populate "standard" images with a [Due in Opentext] date by using the same date in [Due in Opentext] for images named "lifestyle"? Because these images both live together in the post-production process and so the due date for a "standard" image is the same as for a "lifestyle".
The really difficult part here is the [Ref ID]. I would need to look up the "lifestyle" image for a [Ref ID], for example 123456, see if there was a "standard" image also under 123456, and then populate the field [Due in Opentext] for that "standard" file with the [Due in Opentext] date from the corresponding "lifestyle" image. There would have to be some kind of match function or something in QB and I'm not aware of one if there is.
Very complicated. Possible? Or a fool's errand?
Hi James,
Thank you for confirmation. Here you go.
Create a formula-text field and paste the below formula and replace the [Date] field with your date field.
Please let me know if you need any further help.
Thanks,
Gaurav
Thank you for confirmation. Here you go.
Create a formula-text field and paste the below formula and replace the [Date] field with your date field.
var text day = Case(DayOfWeek([Date]), 0,"Sunday", 1,"Monday", 2,"Tuesday", 3,"Wednesday", 4,"Thursday", 5,"Friday", 6,"Saturday"); var text month = Case(Month([Date]), 1,"Jan", 2,"Feb", 3,"Mar", 4,"Apr", 5,"May", 6,"Jun", 7,"Jul", 8,"Aug", 9,"Sep", 10,"Oct", 11,"Nov", 12,"Dec"); $day &","&" "& $month &" "& Day([Date])
Please let me know if you need any further help.
Thanks,
Gaurav