Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 841
  • Last Modified:

Field type varies between date and timestamp for different records

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?
  • 2
1 Solution
For purposes of uploading to another database that stores the date and time as a string, I would create two text fields, with an autoenter calculation that is based on the timestamp or the date field. And for te existing entries I would use the replace commend to initially fill out all the field for which data already exists.

Then I would export those fields rather than the timestamp with a length modifier.
MarkJulieAuthor Commented:
You just gave me a thought. If I create both a date field and a time field (which would occasionally be empty) to maintain valid formatting of the date and time portions for both Event_1 and Event_2, I could then GetAsText and concatenate the two back to a text string (for export and upload) resembling a timestamp if the the time is non null and a date if the time is null. The precision code guides the formatting at the upload to the other database.

Should this also work, or how would you modify?
This is the easiest way to convert variables that you later want to sync with another database. What you see as a result of the timestamp and the datestamp is not what is stored, so you must convert it to a string to share with an odbc source since it wants a string during import. Which ever you need to store the method id the same. Also importing a string into these fields in Filemaker will require some manipulation on the way in but will also work.

Good luck

Featured Post

Receive 1:1 tech help

Solve your biggest tech problems alongside global tech experts with 1:1 help.

  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now