Discussions

 View Only
Expand all | Collapse all

Force text into date field

  • 1.  Force text into date field

    Posted 02-21-2018 22:36
    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.


  • 2.  RE: Force text into date field

    Posted 02-21-2018 23:25
    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.


  • 3.  RE: Force text into date field

    Posted 02-22-2018 18:04
    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?


  • 4.  RE: Force text into date field

    Posted 02-22-2018 19:56
    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


  • 5.  RE: Force text into date field

    Posted 02-26-2018 20:06
    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"?


  • 6.  RE: Force text into date field

    Posted 02-27-2018 06:59
    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


  • 7.  RE: Force text into date field

    Posted 02-27-2018 17:43
    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).


  • 8.  RE: Force text into date field

    Posted 02-27-2018 17:47
    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.


  • 9.  RE: Force text into date field

    Posted 02-27-2018 17:53
    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.


  • 10.  RE: Force text into date field

    Posted 02-27-2018 18:09
    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


  • 11.  RE: Force text into date field

    Posted 02-22-2018 19:26
    Please list your tables and their relationships clearly indicating the One and the Many.


  • 12.  RE: Force text into date field

    Posted 02-23-2018 17:15
    Here's the relationship diagram, I hope this helps. Let me know if you have questions about it. 



  • 13.  RE: Force text into date field

    Posted 02-24-2018 01:44
    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.


  • 14.  RE: Force text into date field

    Posted 02-23-2018 17:34
    > (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