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.

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.

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?

Please list your tables and their relationships clearly indicating the One and the Many.

Hi James,Mark is right. You can easily achieve this by creating a formula-text field.If([Shot Type]="Standard","See lifestyle",ToText(WeekdayAdd([To MRC], 5) )) So, this formula will show See lifestlye text if there is Standard type image and for rest it will show the Date field with 5 days added. I believe this will solve your first part of the problem.Thanks,Gaurav

Here's the relationship diagram, I hope this helps. Let me know if you have questions about it.

Force text into date field | Qrew Discussions

QuickBaseCoachD
Qrew Captain
8 years ago
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.
JamesTrory
Qrew Assistant Captain
8 years ago
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?
- GauravSharma3
  Qrew Commander
  8 years ago
  Hi James,
  
  Mark is right. You can easily achieve this by creating a formula-text field.
  
  If([Shot Type]="Standard","See lifestyle",ToText(WeekdayAdd([To MRC], 5) )) So, this formula will show See lifestlye text if there is Standard type image and for rest it will show the Date field with 5 days added.
  I believe this will solve your first part of the problem.
  
  Thanks,
  
  Gaurav
- JamesTrory
  Qrew Assistant Captain
  8 years ago
  Thanks Gaurav, it's a good idea and does work.
  
  Do you know if there's a way to format ToDate() so that it displays as text, eg. "Monday, Feb 26" instead of "02-26-18"?
- GauravSharma3
  Qrew Commander
  8 years ago
  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
QuickBaseCoachD
Qrew Captain
8 years ago
Please list your tables and their relationships clearly indicating the One and the Many.
- JamesTrory
  Qrew Assistant Captain
  8 years ago
  Here's the relationship diagram, I hope this helps. Let me know if you have questions about it.
- QuickBaseCoachD
  Qrew Captain
  8 years ago
  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.
_anomDiebolt_
Qrew Elite
8 years ago
> (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

Forum Discussion

Force text into date field

Related Content

Merging 2 Fields into a Formula-Text Field for Reporting Purposes

Pulling A date from anther form based on a text field name

Search for Multiple/Many Unique Asset IDs in text Field.

Forced to use new dashboards?

Consolidate Field Types