Force text into date field

  • 0
  • 1
  • Question
  • Updated 9 months ago
  • In Progress
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 like to display text in [Due in Opentext] instead of it being left blank.

It would look something like:

If
(
[Shot Type]="standard","See lifestyle"
)

Is this possible?

I've tried using ToText() to force the text into the field but the problem is that this is only designed for converting an existing field to text, not for adding text to a blank field, so it doesn't work. Anyway, thought I would check here before giving up.
Photo of James Trory

James Trory

  • 656 Points 500 badge 2x thumb

Posted 9 months ago

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

QuickBaseCoach App Dev./Training, Champion

  • 51,436 Points 50k badge 2x thumb
You cannot render text in a date field type.

You can show words in a email x field and words which look like a date in a e TD field, but they would be words and not an actual date field type.
Photo of James Trory

James Trory

  • 656 Points 500 badge 2x thumb
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?
Photo of Gaurav Sharma

Gaurav Sharma

  • 5,756 Points 5k badge 2x thumb
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.

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
Photo of James Trory

James Trory

  • 656 Points 500 badge 2x thumb
Hi Gaurav,

Thanks. I created a new formula-text field, pasted this to it and changed the [Date] name. I received this error:

"The types of the arguments or the number of arguments supplied do not meet the requirements of the function DayOfWeek.

The function is defined to be DayOfWeek (Date d)."
Photo of Gaurav Sharma

Gaurav Sharma

  • 5,756 Points 5k badge 2x thumb
Is your [date] field a date type field or a formula-text field?

It is better to post your formula here. So, I can review it.
Photo of James Trory

James Trory

  • 656 Points 500 badge 2x thumb
This is where it gets tricky. I have a formula-text field [Due in Opentext] that adds 5 business days to a date field [To MRC] and then converts that date to text. So yes, it is formula-text but is technically displaying a date. It displays the date as 02-27-2018 but I want the format to be Tuesday, Feb 27 (like you can do with date fields, convert the date to text).

What formula do you need to see? It is [Due in Opentext] that is the formula-text field that has a converted date in it, which I want to display in a different format.
(Edited)
Photo of Gaurav Sharma

Gaurav Sharma

  • 5,756 Points 5k badge 2x thumb
Gotcha! Now, you don't have to worry. So, there is no need to create a new field.

Just modify the code of your [Due in Opentext] field.

var date toMRC = WeekdayAdd([To MRC], 5);
var text day = Case(DayOfWeek($toMRC),
0,"Sunday",
1,"Monday",
2,"Tuesday",
3,"Wednesday",
4,"Thursday",
5,"Friday",
6,"Saturday");

var text month = Case(Month($toMRC),
1,"Jan",
2,"Feb",
3,"Mar",
4,"Apr",
5,"May",
6,"Jun",
7,"Jul",
8,"Aug",
9,"Sep",
10,"Oct",
11,"Nov",
12,"Dec");

var text dueInOpentext = $day &","&" "& $month &" "& Day($toMRC);
If([Shot Type]="Standard","See lifestyle", $dueInOpentext)

Try this formula into your [Due in Opentext] field. Please let me know if this works for you.

Thanks,

Gaurav
(Edited)
Photo of QuickBaseCoach App Dev./Training

QuickBaseCoach App Dev./Training, Champion

  • 51,436 Points 50k badge 2x thumb
Please list your tables and their relationships clearly indicating the One and the Many.
Photo of James Trory

James Trory

  • 656 Points 500 badge 2x thumb
Here's the relationship diagram, I hope this helps. Let me know if you have questions about it. 

(Edited)
Photo of QuickBaseCoach App Dev./Training

QuickBaseCoach App Dev./Training, Champion

  • 51,436 Points 50k badge 2x thumb
The question you posted if too complex to respond to on this forum.  I think it would take a GoToMeeting to work though your issue together.  Contact me via the information on my website if you would like one on one assistance.
Photo of Ⲇanom the ultimate (Dan Diebolt)

Ⲇanom the ultimate (Dan Diebolt), Champion

  • 26,522 Points 20k badge 2x thumb
> (1) Force text into date field ...  (2) prefer to keep all data contained in one column

It can be done with script and it can't be done natively