9 Replies Latest reply on Nov 28, 2012 2:09 PM by brian.curran

    Date format changing when syncing

    brian.curran

      Title

      Date format changing when syncing

      Post

           Hi,
           I'm using GoZync to transfer data from a Hosted file to a Mobile file. It's all going ok except that dates are being converted to USA format.

           Dates such as 05/08/2012 should be 05th of August but they are showing up as 08th of May after the transfer.
           Dates such as 17/08/2012 are being displayed as a '?'

           How can I get the Mobile file to display all dates using UK format?

           Thanks
           Brian.

        • 1. Re: Date format changing when syncing
          davidanders

          http://www.seedcode.com/pmwiki/pmwiki.php?n=GoZync.DateFormats

          GoZync

          Date Formats
               Dates don't seem to be zyncing correctly, but text fields are. What's going on?

               The scripts in GoZync represent all your data as "text" in the document we pass back and forth. Converting dates to and from text is the only place where FileMaker fails to respect "Use System Formats". In these cases FM uses the date format initialized when the file was created: if your file was created with UK dates, for example, it will conflict with GoZyncs file which were created with US dates.

               Fortunately this is easy to fix. Cloning a file (Save a Copy As / Clone) wipes this date information and resets it using the format of the machine which next opens the file.

               So making a clone of GoZyncConnector and Mobile (if you're using ours) should get you sorted. There is data in both files you'll still need, however. So after cloning, renaming, and opening you're new files, you'll want to import data from the uncloned versions into the following tables:

          GoZyncConnector: GoZync and Interface Mobile: PortalRows, GoZync, and Interface

          • 2. Re: Date format changing when syncing
            brian.curran

                 Thanks David, I obviously need to improve my Googling skills as well as FM! ;)

            • 3. Re: Date format changing when syncing
              brian.curran

                   A little more help is needed I'm afraid. Apparently, I can use SetField to transform the incoming text to a date in UK format but I don't know how to write it.

                   If [ $fieldName = Table::Date1 ]
                     Set Field By Name [ $fieldName ; //Calc to transform the text into dd/mm/yyyy ]
                   End If

                   The bit in red is where I'm stumped, any thoughts?

                   Thanks
                   Brian.

              • 4. Re: Date format changing when syncing
                philmodjunk

                     use the Get as date function.

                • 5. Re: Date format changing when syncing
                  brian.curran

                  Would this work?

                        

                  If [ $fieldName = Table::Date1 ]
                    Set Field By Name [ $fieldName ; GetAsDate (Table::Date1) ]
                  End If

                  • 6. Re: Date format changing when syncing
                    philmodjunk

                         If Date1 is of type Date, just use:

                    Set Field By Name [ $fieldName ; Table::Date1 ]

                         You can use GetAsDate if Date1 is of type text and in the correct/valid date format for your locality.

                    • 7. Re: Date format changing when syncing
                      brian.curran

                           Hi Phil,
                           The date in my Hosted file is dd/mm/yyyy but this is converted to text during the sync. I'm pretty sure it's arriving in my Mobile file as mm/dd/yyyy and any dates that have a day higher than 12 is being returned as an invalid month so the "?" mark is displayed.

                           So, Date1 is a date field but it's being populated by text. Those dates that look right are often the wrong way around so the 03rd of July is displayed as 07/03 (07th of March).

                           Not sure how to proceed now. Will I need to swap the dd and mm around somehow and then convert it to a Date?

                           Thanks
                           Brian.

                      • 8. Re: Date format changing when syncing
                        philmodjunk

                             I don't have sufficient detail on the gozync scripts to be able to suggest exactly how to do this. Somewhere, you have a step that is moving text into your date field and that's where the previously mentioned fix due to saving a clone is supposed to fix this. You may find that getasDate is needed at that point, but I can't tell you definitely whether or not that will work given the lack of info.

                        • 9. Re: Date format changing when syncing
                          brian.curran

                               Thanks for your help Phil, it all worked out with a some additional script steps in an existing script...