Field type varies between date and timestamp for different records

Posted on 2012-08-25
Last Modified: 2012-08-27
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?
Question by:MarkJulie
    LVL 9

    Expert Comment

    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.

    Author Comment

    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?
    LVL 9

    Accepted Solution

    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

    How to run any project with ease

    Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
    - Combine task lists, docs, spreadsheets, and chat in one
    - View and edit from mobile/offline
    - Cut down on emails

    Join & Write a Comment

    Suggested Solutions

    Pop up windows can be a useful feature of any Filemaker database.  Though best used sparingly, they can be employed in a multitude of different ways, for example;  as a splash screen at login, during scripted processes to control user input, as pick…
    Conversion Steps for merging and consolidating separate Filemaker files The following is a step-by-step guide for the process of consolidating two or more FileMaker files (version 7 and later) into a single file with multiple tables. Sometimes th…
    how to add IIS SMTP to handle application/Scanner relays into office 365.
    Here's a very brief overview of the methods PRTG Network Monitor ( offers for monitoring bandwidth, to help you decide which methods you´d like to investigate in more detail.  The methods are covered in more detail in o…

    730 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    16 Experts available now in Live!

    Get 1:1 Help Now