Help converting a 6 digit text field to a date.

  • 0
  • 1
  • Question
  • Updated 2 years ago
  • Answered
Hello all.  I have a text - multiple choice field that represents a date in the format yymmdd (i.e.161129 for November 29th, 2016).  I'd like to convert this text field to a date field.   When I change the field type to a date, all exiting values change to 01-01-1970.   I've tried creating a date formula field using the MID function to parse the values in the text field and put it in mm-dd-yyyy format and then ToDate to convert it to a date value.  However, once on my form, the dateforumla field is not editable.  I then used a form rule to move the dateformula field values into a new date field.   However, that form rule only executes when the form is opened in edit/add mode.  What is the best way to convert the existing text values into a date field that is editable on a form?
Photo of Tracy

Tracy

  • 40 Points

Posted 2 years ago

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

QuickBaseCoach App Dev./Training, Champion

  • 67,278 Points 50k badge 2x thumb
I'n confused.  What field is your source of truth for the date.  The multiple choice field or the manual field populated by a form rule that copies over the calculated date into a data entry date field.

Maybe what you want is to have three fields.

1. A multiple choice field which is always mirrored by that formula field as a date formatted formula field.

2. Then a second field which is a date override field.

Then a final field that you will use for your reporting which will be a formula date field use the date override field if its not null, else use the  formula date field which is a formula off that multiple choice field.
Photo of Tracy

Tracy

  • 40 Points
Can you please provide more info on what you mean by date override field?  Below is more info on what I currently have set up.

Source:  Multiple Choice text field (value ex:   161129)
Second field:  date formula that reformats the source field into a date but this field is not editable (value ex 11-29-2016)
Third Field:  Date field which is populated by a Dynamic Form Rule.  If the date formula field (second field above) is not empty then populate this third field with value of the data formula field. This field contains the same data in the same format as the second, but it's editable with a date picker.

The problem is that the Source field is currently is use and projects are being created with this field.  The third field will not be populated for existing projects unless the project is opened in edit mode as that activates the Dynamic Form rule.  So I don't know how to get the Source field converted for existing projects without opening each project one at a time.
Photo of QuickBaseCoach App Dev./Training

QuickBaseCoach App Dev./Training, Champion

  • 67,278 Points 50k badge 2x thumb
right, so use my suggested solution.  It is not dependent on form rules.