Field type varies between date and timestamp for different records
Posted on 2012-08-25
I have a 250 field database with about 5000 records to which about 100-150 records a month are added. Event_1 and Event_2 capture timed event data whose detail varies. The current setup takes timestamp input for the time of two events. Sometimes only the date of an event can be determined but not the time creating the need for that field to occasionally behave as a date field type. Assigned to these fields is a separate number field for event precision using 3 if it is a Date type and 5 if it is of type Timestamp. Biweekly, I have to export all fields of these records as .XLSX file, then format all the Timestamp fields to mm/dd/yyyy HH:mm (stripping the seconds off), then save as CSV for upload to a national database according to that vendor's constraints.
If there is a way to design Event_1 (and Event_2) to allow both Date and Timestamp formatted data depending on its precision? When I export, I need Event_1 with precision code 3 to be Date formatted and with precision code 5 to be Timestamp formatted.
Can I use a Container or Text field in this fashion where I type the date as mm/dd/yyyy and the timestamp as mm/dd/yyyy HH:mm and let .XLSX format maintain the data cast as a string?
I would prefer to scrip the formatting based on the precision code, but I don't know if FMPA can do that. E.g., could I script to take mm/dd/yyyy HH:mm with precision 3 (Date) and strip the time making it behave as a Date field type?