2 Replies Latest reply on Oct 12, 2013 11:15 PM by dperez@animas.net

    Fixing dates to ISO format during import

    dperez@animas.net

      Title

      Fixing dates to ISO format during import

      Post

           Hello,

           I've been trying to figure out how to set a date to a specific format yyyy-mm-dd. After setting up a custom date field format everything looks ok, but when I select the date field or export the data, the originally entered format shows up like mm/dd/yyyy.  I need to permanently fix the date format to yyyy-mm-dd .  When I gather data from other people they may enter the dates in a variety of different formats and still have a valid date, but when I import the data into my database I need it to convert the dates to be specific to what I need.  The reason for it to be in this format is because I'm collecting data from multiple sources, cleaning up mistakes and then exporting and submitting my data file to another agency.  The Date field is specific to their import.  I know I can do this in another program before I import it into my database, but If It could all be done within FilemakerPro 12, it would be a huge time saver for me.

           Thank you

           Daniel

      Screen_Shot.jpg

        • 1. Re: Fixing dates to ISO format during import
          philmodjunk

               Dates are stored as an integer recording the number of days from the date shown to 12/31/0000. The data format you specify in the Inspector only controls how this integer is displayed as a date it does not change the value of that integer. In terms of your own work with this date from within FIleMaker, that format specification should be all you need.

               It would appear that the key issue is getting this data exported as text in the desired format. When exporting records, there's an option to "apply current layout's data formatting". Have you tried using this option?

               If all else fails, you can set up a calculation field that returns text that converts your date into the proper format for export and then you export that calculation field instead of the date field:

               Year ( YourDateFIeld ) & "-" & Right ( "0" & Month ( YourDateField ) ; 2 ) & "-" & Right ( "0" & Day ( YourDateField ) )

          • 2. Re: Fixing dates to ISO format during import
            dperez@animas.net

                  

                 That worked Great!  Thank you!!!  

                 I did modify it a little bit and fixed a minor mistake at the end, where it expected  2 digits for the Day.  I ended up putting it into a script instead, but this is what I finally ended up with.  Thanks again.

                  

                 Let (
                  
                 [ x = STARS Cleanup Template::Entry Date ] ;
                  
                 Year ( x ) & "-" & Right ( "0" & Month ( x ) ; 2 ) & "-" & Right ( "0" & Day ( x ) ; 2 ))