6 Replies Latest reply on Jun 4, 2017 10:27 AM by amsc

    Date formats and importing

    amsc

      One of our users recently imported data into our FM system with his computer set to European date formats (dd/mm/yyyy) (our FM system is setup with mm/dd/yyyy, not dd/mm/yyyy).  I was fortunate that the issue was caught almost immediately and we could recover from a backup taken just a few minutes before the import.  (As an aside, FM Cloud's every 20 min snapshots saved me!)

       

      I haven't been able to figure out how to prevent this from occurring again.  I've tried the script step "set use system formats" to both on and off.  I've also tried using the menu option "file options" --> data entry.  Neither of these methods seem to resolve my issue. 

       

      Here's the flow of data:

      The data is initially created on other systems (not FM) with mm/dd/yyyy dates (they are exported as csv and excel files).  These csv and excel files retain the dates in mm/dd/yyyy formats.  Our FM database then imports this data via script steps.  If the import occurs on a client computer with european dates (dd/mm/yyyy) the dates are imported as dd/mm/yyyy into our mm/dd/yyyy date system  (i.e. June 1, 2017 comes in as January 6, 2017). 

       

      We are using FM Cloud with a mixture of FM 15 and FM 16 clients. 

       

      I'm certain I'm overlooking something straight forward.  Any help will be greatly appreciated!

       

      Alex

        • 1. Re: Date formats and importing
          TomHays

          You describe that the source dates are always "mm/dd/yyyy" but that your FileMaker sometimes misinterprets this as dd/mm/yyyy when the client computer is set to use dd/mm/yyyy format.

           

          One way is to change your FileMaker import script to import to a new Text field ImportDateText (or any other Text field that you can spare).

          After importing, set the real date field using

           

          Let([

          DL = Substitute(ImportDateText; "/"; "¶")

          ];

          Date( GetValue(DL;1); GetValue(DL;2); GetValue(DL;3))

          )

           

           

          -Tom

          • 2. Re: Date formats and importing
            philmodjunk

            Sounds like the dates are being imported as text. This would be expected for a csv import, but not necessarily for import from Excel, but that may be a result controlled by the other application's export process. There's nothing in the text that will tell FileMaker which date format was used so only the current settings on your FileMaker file would control whether the first part of the date text represents the day or the month. Note that 01/02/2017 can be interpreted as either January 2nd or February 1st and there's nothing in the text itself to tell you which is correct.

             

            You may need to import into a "staging table"--an added table in FileMaker set up solely for "preprocessing" imported data where you check for and correct such issues. FileMaker has quite a few text manipulation functions so setting up a calculation field in the staging table that converts text from dd/mm/YYYY into mm/dd/YYYY can be pretty simple to do, especially if the same delimiting character (such as "/") is always present in the imported data. Once you've used the staging table to fix the issue, you can use either import records or a looping script to import the data into the final table where you want it.

            • 3. Re: Date formats and importing
              amsc

              Thank you Philmodjunk!  That is what I was afraid of.  I'm fortunate in that my data is landing in a temp table for other processing.  I should be able to use the get (currentdate) and extract the day and month to figure out if the imported data is in the "correct" format.  Is there a more straightforward way to query the client computer's regional format within filemaker?

              • 4. Re: Date formats and importing
                Devon Braun

                Instead of a staging table consider a staging field a la "ImportedDate" working in concert with an additional DateFormat flagging field that defines which format, mmdd vs ddmm, was received plus a calculated or auto-entered ActualDate.  That structure could help you with after-the-fact corrections and uses a non-destructive approach to importing / cleaning.

                 

                As for determining the intended date format of the source, consider a number of approaches:

                - Files often embed file creator, file type, even text encoding meta data, accessible (on MacOS, for instance) via AppleScript and CLI, which FMP can be coded to retrieve.  Similar for PC.

                - If you're getting your data imports from a limited array of repeat sources, you may discover each employs a naming or file path, file extension convention idiosyncrasy you can use to reliably interpret the intended date formatting.  If so, consider adding SourcePath and/or SourceFile fields, so that every imported record knows its origin.

                - Least techy -- consider a simple prompt prior to import asking the origin, date format, or whatever cue might answer the date format question prior to import.

                • 5. Re: Date formats and importing
                  TomHays

                  amsc wrote:

                   

                  Is there a more straightforward way to query the client computer's regional format within filemaker?

                   

                  This is a Script method that works in FileMaker 11 to determine the date format used by that client.

                  There may be an easier way in later versions of FileMaker.

                   

                  Put a text field MyTextField on a layout.  I use a utility Global field.

                   

                  Write a script

                   

                  Go to Layout ["Scripting"]

                  Insert Calculated Result [Select; MyTable::MyTextField; Date(12;30;1978)]

                  Go to Field []

                  Set Variable $dateFormatString; Value Substitute(MyTable::MyTextField; ["30"; "DD"];["12";"MM"];["1978";"YYYY"])]

                  Set Field [MyTable::MyTextField; ""]

                  Exit Script[Result: $dateFormatString]

                   

                   

                  $dateFormatString will usually be either "MM/DD/YYYY" or "DD/MM/YYYY" but it can be any other format provided by the localization.

                   

                   

                  A similar technique can be used to discover what decimal symbol is used by that localization (usually "." or ",").

                   

                  InsertCalculatedResult [Select; MyTable::MyTextField; 314/100]

                  Go to Field []

                  Set Variable[$decimalPointSymbol; Value:Middle(MyTable::MyTextField; Position(MyTable::MyTextField; "3"; 1; 1) + 1; 1)]

                  Set Field [MyTable::MyTextField; ""]

                   

                   

                  -Tom

                  • 6. Re: Date formats and importing
                    amsc

                    Thank you!