8 Replies Latest reply on Dec 4, 2013 2:48 AM by philmcgeehan

    Since hosting on FMS12 my dates are returned in different format

    philmcgeehan

      Title

      Since hosting on FMS12 my dates are returned in different format

      Post

           I had a script that worked, where I could select a month from a pop-up list and it would display a list of records from that month.

           Originally it would set $filterdate to DD/MM/YYYY
           Then search for all the records within that month.

           Then I purchased FMS12 and now access the database over the LAN via our server.

           But, now it sets the $filterdate variable to MM/DD/YYYY
           now when it searches for the date it doesn't get the correct results, because the Month and Date are mixed up.

           I'm in the UK and I want the format to be DD/MM/YYYY
           My computer and the Servers date settings are set to DD/MM/YYYY

           Is there any other date setting somewhere that I have missed, or do I need to swap the DD and MM around in my script?

        • 1. Re: Since hosting on FMS12 my dates are returned in different format
          philmodjunk

               What script steps do you use to set $Filterdate to a date?

          • 2. Re: Since hosting on FMS12 my dates are returned in different format
            philmcgeehan

                 Set Variable [$filterDate ; table::g_FilterDate]

                 g_FilterDate is taken from a Value List showing the calculation field c_MonthYearNAME

                 c_MonthYear = GetAsDate ( "01/" & Month ( DateTransaction ) & "/" & Year ( DateTransaction ) )
                 c_MonthYearNAME  = MonthName (DateTransaction) & " " & Year (DateTransaction)

                 then I Perform Find ==$FilterDate on c_MonthYear

            • 3. Re: Since hosting on FMS12 my dates are returned in different format
              philmodjunk

                   If you just enter a date manually into DateTransaction, can you enter it correctly using DD/MM/YYYYY format?

                   If so,

                   Try using the Date function instead of GetAsDate to see if you get different results.

                   Also, check the data type for g_FilterDate and the result type of cMonthYear to see if they are set for text or date.

                   Another way to define cMonthYear is this way:

                   DateTransaction - Day ( DateTransaction ) + 1

              • 4. Re: Since hosting on FMS12 my dates are returned in different format
                philmcgeehan
                     

                If you just enter a date manually into DateTransaction, can you enter it correctly using DD/MM/YYYYY format?

                     Yes, I can only input a date in the DateTransaction field in the DD/MM/YYYY format.

                     

                Try using the Date function instead of GetAsDate to see if you get different results.

                     If I use the Date function, it still returns MM/DD/YYYY.
                Date ( Month ( DateTransaction ) ; "01" ; Year ( DateTransaction ) )

                     

                Also, check the data type for g_FilterDate and the result type of cMonthYear to see if they are set for text or date.

                     g_FilterDate was set to text.
                     cMonthYear is a calculation resulting in a date.

                     I changed g_FilterDate to date, but it still behaves the same way.

                     Also I redefined c_MonthYear to the way you suggested, much neater solution, thanks.

                     It seems to be all correct until the pop-up menu. So I had a thorough play around, and as you can see from the attached image; if I sort the value list by the first value it works as I want it to and the dates are in the correct format and order smiley however, if I sort by the second value it switches the DD and MM around sad.

                     I want to sort by and hide the first value, but I can't do that can I?

                • 5. Re: Since hosting on FMS12 my dates are returned in different format
                  philmodjunk

                       A basic bit of info on dates that you may or may not know:

                       Date fields store a "date" as an integer counting the number of days from 12/31/0000 to the current date. The way that value is displayed, MM/DD/YYYY or DD/MM/YYYY being just two of many options is a display setting set on the Inspector's data tab for a particular field object on a specific field. The Date() function ignores all locality settings and enters the correct integer value for the date parameters passed to it, which is why I suggested experimenting with it to see if it made a difference here.

                       But how the text you type in as a date into a date field is interpreted--whether the first digits are the month or the day is determined by the computer's locality settings and the options you select in File Options on the Text tab. And there are known issues that crop up both for server and client in this area. The one that I recall--data entered in a file with one locality option specified and then opened on a different computer with different options, doesn't seem to apply here.

                       It appears that the value list is entering the text form of the date reformatted in the wrong format and thus you get the wrong or even an invalid date in your field. In storage options, is that first field indexed?

                       Ultimately we may need to do a work around where the value list enters November 2013 type text into a field and your script uses a calculation to compute the corresponding date to enter into the date field as search criteria.

                  • 6. Re: Since hosting on FMS12 my dates are returned in different format
                    philmcgeehan

                         Thanks for the information, I was familiar with parts, through my use in MS Excel.

                         Yes, c_MonthYear is an Indexed field (All) and
                         c_MonthYearNAME is indexed (Minimal (automatically create indexes as needed)).

                         At the moment, I have placed a label over the pop up menu, so it appears to display c_MonthYearNAME (November 2013), and I've left the value list to display both fields:
                         (i.e.
                         01/09/2013 September 2013
                         01/10/2013 October 2013
                         01/11/2013 November 2013
                         ).

                         Which isn't as nice as I'd like, but it works as I'd expect.

                         Is there a way I can sort the Value List chronologically and have it display the Month Name and year?

                    • 7. Re: Since hosting on FMS12 my dates are returned in different format
                      philmodjunk

                           Each thing I can think of for a value list comes up short on one limitations or another. Only option I can think of would be to replace the value list with a portal where you set up the fields in the portal to be buttons that use a script to insert the date from the field into your global date field.

                           Hmmm, just thought of one option. If you've confirmed that the value list is entering data as MM/DD/YYYY instead of DD/MM/YYYY, you might be able to use an OnObjectValidate trigger to take the data entered and swap the first two portions of the date around to get the correct date.

                           And since this worked before you hosted from Server, this would appear to merit a bug report over in Report an Issue. I suggest that you do that and save some typing by including a link to this thread. The tech support staff that monitor that section may have suggestions on what to check that I don't.

                      • 8. Re: Since hosting on FMS12 my dates are returned in different format
                        philmcgeehan

                             Thanks for your help Phil.

                             I've posted it in Report an Issue - I'll wait and see if anything comes from it.