Skip to main content
Question

Force text into date field

  • February 22, 2018
  • 0 replies
  • 94 views
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.
    This topic has been closed for replies.

    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
    • Author
    • Registered
    • February 22, 2018
    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.
    > (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
    Terms & ConditionsAccessibility statement