6 Replies Latest reply on Dec 14, 2012 11:47 PM by Gingernut

    Filemaker 12 date problems


      I will try and explaing this as best I can.


      Using Filemaker 12 and a converted file. Windows 7


      I am in UK using UK date formats


      I have converted a FM11 file to 12


      In a script I want to set the a date in one table from a date in another.


      Both are date fields with full 4 digit years in layouts.


      I have tried copy and pasting and setting the field and in both cases the date comes back with a ?


      I have used dataviewer to GetasDay(date) and also get a ? but it is a date field correctly formatted.

      If I GetAseText(date) I get 28/07/2011 (for example)


      I also tried Import as an alternative test and in this case the date "looks" correct but you cannot search on it and again copying and pasting gives an error revert field message.


      I used the dataviewer on the Pre12 (FM11) file and it does return the correct day on the same field so I think there may have been some issue in the conversion but cannot imagine why.

      Has anyone else come accross this or have a solution please its driving me insane.



        • 1. Re: Filemaker 12 date problems

          Please explain: How do you set the filed? Copy and Paste or Set Field?

          DataViewer showing GetAsDay(date) as "?" indicates that "date" is NOT a date.

          Remember that DataViewer variables as well as other variables are always text.


          Start testing with with

               GetAsNumber ( Get ( CurrentDate ) )

          which returns something like 734851.

          Check what numbers your other date values return.

          Avoid Copy and Paste, use Set Field.


          Once you have set your fields right, you can search like in numbers < ≤ = ≥ > date1..date2 .

          If you want to search for specific months or days you need separate calculated field.


          A Date, Time, or Timestamp can be treated exactly like a number. For many operations tis is the only reliable way.


          If you have still problems you may read <http://fmdiff.com/fm/locale.html> or you may send me the file with an exact error description (winfried@fmdiff.com).



          • 2. Re: Filemaker 12 date problems

            Thanks Winfried


            I managed to establish that although they were not showing there were spaces after the year. By creating a GetAsText field = TheAwkwardDate field I was then able to replace the text field with Left(TextField;10) and then use that field to re-set the TheAwkwardDate field. I could not apply the Left function directly to the date it did not work.


            The only way I realised there were spaces is that I went to edit TheAwkwardDate field and when I went to the end of the field although it showed the cursor immediately after the end of the year, it took an additional 3 backspaces to clear the last digit of the year.


            I then tried exporting to excel where I could see the spaces when I tabbed to the end of TheAwkwardDate field.


            Very odd and BTW it made no difference if I copied and pasted, set field or even imported the data I ended up with the same problem.


            I know that the GetAsDay(date) as "?" indicates that "date" is NOT a date ? suggests it is not a date but the field was formatted as a date and it looked like a date so was frustrating to find these pesky hidden spaces.


            Many thanks





            • 3. Re: Filemaker 12 date problems

              Gingernut wrote:


              I know that the GetAsDay(date) as "?" indicates that "date" is NOT a date


              Not exactly. It indicates that the value is not a valid date. This can happen when data is imported into a date field. For example, importing m/d/y data into a file using d/m/y date format will render some of the values invalid.


              The first thing you should do, IMHO, is find out which date format your converted file is using. Set the file to 'Always use file's saved settings' (under File > File Options > Text), format a date field to display data 'As entered', then insert the current date into the field.

              • 4. Re: Filemaker 12 date problems

                You all are talking about a function that does not exist: GetAsDay( )

                • 5. Re: Filemaker 12 date problems

                  Yeah, well - the GetAseText() function does not exist either, but I think the intention is clear.

                  • 6. Re: Filemaker 12 date problems

                    Oops and thanks as you say people knew what I meant to say

                    I am a long way from being an expert which is why I needed to post here and get help.

                    Thanks I have learned the hard way but at least I have learned :)