6 Replies Latest reply on Apr 25, 2013 12:13 AM by diaf

    Search by date differs on locally hosted file VS Server hosted file?

    diaf

      Hi,

       

      I have a very interesting problem.
      Has developed a file locally in FileMaker Pro Advanced. Works excellent.
      Trying then to the hosts file on FileMaker Server Advanced.
      Now when I try to search in a date field, I get "No recods match this find criteria".
      When the file is run locally, the search works fine, but not on the server.

      I am running Filemaker Pro Advanced 12.0v3 and FileMaker Server Advanced 12.0v2.

       

      Anyone have an idea what could be wrong?

        • 1. Re: Search by date differs on locally hosted file VS Server hosted file?
          taylorsharpe

          Obviously this shouldn't happen.  I suspect your index is corrupted.  Unindex the field and then reindex it and see if it changes.  Confirm the format is the correct date format and not text or number.  You might want to recover the file.  Does the file "Verify" on the server?

          1 of 1 people found this helpful
          • 2. Re: Search by date differs on locally hosted file VS Server hosted file?
            diaf

            The field in quiestion is a unstored calculated field. Therefore, I suppose it's not an index error.

            I have checked and rechecked that the calculation returns a date.

            The file is in the normal state on server and do verify

             

            Stored/indexed datefield in the same table can be searched with the correct result.

            • 3. Re: Search by date differs on locally hosted file VS Server hosted file?
              wimdecorte

              Start by updating your FMS to v3 or v4 just in case there was a bug fix.

               

              Other than that: try the two date formats:  mm/dd/yyyy and dd/mm/yyyy to see if that makes a difference

              • 4. Re: Search by date differs on locally hosted file VS Server hosted file?
                LyndsayHowarth

                Have you checked that the server and the local machine are using the same timezone and localisation(1) settings?

                If the file was created on one machine then moved to a different machine with different local Date / Time formats then you will get a different result.

                 

                eg. I live in Australia where we use A4 paper and write todays date as 23/4/13 ie d/m/y.

                If I open one of the US-created template files, they have been set for US Letter which is wider and shorter than A4. I would have to adjust the layouts and tinker with print scripts etc to make the file useful.

                More critically, if I try to enter a date, I will have 23/4/13 rejected because it is not a valid US date. I could enter 1/4/13 but that would be turned into the 4th of January.

                You can ignore this and add calendars to your fields and format your fields to display the right date... but you are always going to have issues when you click in the field and see it the wrong way around.

                Sorting is obviously effected if for some reason FIleMaker has data in one format where the system is in another.


                How do you check this and fix it?

                1. Check your machines date and timezone and other International settings (currency symbols and all sorts of things can be different)

                 

                2. FIleMaker has had a menu setting which toggles for "Use System Formats" as well as the Script step to turn it on and off. I could only find it in the Scripts in v12 (called "set Use System Formats") not in the menus. This works but ultimately it is better for the file to inately use the system formats for everything it creates.

                3. I would export your data or a sample of it and view it in Excel or Numbers. What do they think the dates are? Change the date format for the data to YY-MON-YYYY ... comparing the data with what shows in the database... are they essentially the same dates?

                4. To reset a file to the System Formats as default, save the file as a Clone then import the data into the clone in its correct format.


                HTH

                 

                - Lyndsay

                 

                (1). Yes, the correct spelling according to the Apple Mac dictionary when referencing the "Language Localisation" Wikipedia page but pretty much unreferenced apart from the title of the page when you go to Wikipedia. Interesting 'cause they usually have (Brit.) for the alternative spellings... but this time it appears (according to the dictionary) that both spellings are correct but in different circumstances.

                • 5. Re: Search by date differs on locally hosted file VS Server hosted file?

                  Apart from the fact that searching in unstored fields is not a good idea,

                  how is the calculation done - i.e. from what sources?

                   

                  Winfried

                  1 of 1 people found this helpful
                  • 6. Re: Search by date differs on locally hosted file VS Server hosted file?
                    diaf

                    Ok, I solved it, but do not know what the real error was.

                    I know that a search in a unstored calculation is bad, but this search would only be done by a server script once every night so I thought that it should be fine.

                    Tried to update the server, but no luck there.

                     

                    The calculation vas made out of a Max( RelTable::DateField ), and when I look in the field I can see the correct value.

                     

                    What I now done instead is to store the value I am searching fore in a indexed field that I update just before the search. This is not perfect performace but it workes.

                     

                    Thanks All!