1 2 Previous Next 26 Replies Latest reply on Jan 15, 2015 11:32 AM by WayneGreen

    Problem finding fields containing dates

    WayneGreen

      Summary

      Problem finding fields containing dates

      Product

      FileMaker Pro

      Version

      11.0v4

      Operating system version

      OS X Yosemite Ver 10.10.1

      Description of the issue

      In Find mode, I cannot find date fields when entering values such as:

            26/12/2014

      even though the visual date displayed in the field is exactly the same.  It doesn't matter whether I am trying to find records in a script or directly in a table layout.  Can you offer any advice?  Thanks,

      Wayne

        • 1. Re: Problem finding fields containing dates
          philmodjunk

          Sounds like your field is of type text instead of type date.

          For examples of scripted finds--including a number of examples using dates and date ranges, see: Scripted Find Examples

          • 2. Re: Problem finding fields containing dates
            WayneGreen

            Thank you for your quick response, "Phil".  I have checked and ensured that the field is Date type (strict data type: 4-digit year date).  I have even highlighted and copied a value from one of the fields and used it in the manual Find procedure.  I have tried it with System Format On and Off.  Same frustrating results.  Although I have created work-arounds for these date searches, I would really like to know why my system doesn't work like everyone else.  As a Canadian, I always set System Formats [Off] and enter my Date field values as dd/mm/yyyy.  Could the problem lie here?

             

            • 3. Re: Problem finding fields containing dates
              philmodjunk

              How was this file created? How was this data entered into this field?

              If you create a brand new file, with just one date field, manually enter some dates and then test by performing some finds with different manually entered dates, does it work?

              I've seen some issues crop up related to system settings, but usually it was with data that was imported into the field in an unusual fashion such as importing the dates into a text field that was then redefined as a date field...

              • 4. Re: Problem finding fields containing dates
                WayneGreen

                An interesting point.  I did import records to this table from an earlier file.  So I tried the test you proposed.  I create a new table in the file with just a Date field (strict 4-digit, etc.) and a Number field.  I populated 10 records with dates from 1/12/2014 to 12/2/2015, in the form dd/mm/yyyy.  Strangely, the only 3 records I could find contained the date 1/1/2015.  All the other date Finds result in no records found - even 1/2/2015 couldn't be found.  Thoughts??????????

                • 5. Re: Problem finding fields containing dates
                  philmodjunk

                  It looks like there is some kind of locality issue between the settings of your computer, FileMaker and your file.

                  If you try entering search criteria in the mm/dd/yyyy format, does it find the date, but displayed in dd/mm/yyyy format?

                  In other words, if you enter 12/02/2014 as your criteria, does it find 02/12/2014?

                  There are date formatting options that you can specify for each date field on your layout in the inspector. This setting controls what you see when the cursor is not in the date field (as happens when you are in the process of entering/editing a date). But what you see while you enter data and whether FileMaker interprets the first digits as that for the month or the day is controlled by the locality settings in your computer and FileMaker.

                  The actual data stored in the field is really an integer representing the number of days from 12/31/0000 to the date shown in the field...

                  • 6. Re: Problem finding fields containing dates
                    WayneGreen

                    Well somethings improved.  Firstly, I checked my iMac settings and found my Region was incorrectly set.  I have changed it back to Canada.  My inspector display settings are almost always set to "25 Dec 2014".  Changing it to "As entered" seemed to make no difference to the test below.  I am aware of the region/locality settings in OS X, but where are the locality settings in FileMaker?

                    Secondly, in trying several Finds on my data I have found that the wildcard search sometimes returns good results.  For instance I can Find all the 2015 dates using * * 2015.  It does not work for * * 2014 however.  * 1 2015 and * 2 2015 both worked.  1 1 2015 worked, but 1 2 2015 did not.  I couldn't find any of the 2014 dates with wildcards.  1 1 2015 and 1/1/2015 both work, but no other full digits numbers work.

                    I tried reversing the dates.  2015 1 1 worked, but 2015/1/1 resulted in the standard “25/12/2003” (four-digit year) required" error message.  2015 2 1 did not work, but 2015 2 * did.  Once again, nothing in 2014 ... worked.

                    Interesting note:  when returning to the date field after pressing <Cmd>R, the "Find" field always contained more text than I last entered. For instance the following values was found:  * * 2014 * 12 2015 *

                    Here is my test table:

                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                   
                    DateNumber
                    1/1/201411
                    1/12/20145
                    1/12/20146
                    26/12/20141
                    26/12/20147
                    1/1/20152
                    1/1/20153
                    1/1/20154
                    1/2/201510
                    12/2/20158
                    12/2/20159

                     

                    • 7. Re: Problem finding fields containing dates
                      TSGal

                      Wayne Green:

                      Thank you for your posts.

                      FileMaker Pro uses the system settings for the date.

                      Try searching for:   2014

                      Do all 2014 dates appear?

                      Search for:   2015

                      Do all 2015 dates appear?

                      Search for:   2/2015

                      What values display?  This should display all dates from 1/2/2015 to 28/2/2015.

                      Format the dates for "25 Dec 2014".  Make sure the appropriate dates are displayed.

                      Create a button to display "Show Custom Dialog" and use the function Get (CurrentDate).  This date will display in the region the file was created.  Does it display 6/1/2015 or 1/6/2015.

                      TSGal
                      FileMaker, Inc.

                      • 8. Re: Problem finding fields containing dates
                        WayneGreen

                         

                        More confusion.  I tried creating a new file from scratch.  I created the same data records and ran the Finds.  All worked as advertised.  In the new file I was unable to Set Use System Formats manually, but did set it alternately On & Off via an Opening Script.  Neither setting changed the outcome.  I had to insert the Date field in the format YYYY/MM/DD regardless of how the field was formatted for display.  The Show Custom Dialog displayed the CurrentDate as 2015-01-07.

                        Next I tried importing the newly created table into one of my existing files.  The import showed the Date field displayed as "?".  On activating the field (cursoring to it) the text displayed as "01 1 2014".  No Find options worked.

                        As mentioned, I have previously imported data into my current file - and always had problems with the Dates showing up correctly.  Could this indicate a problem with the import process?

                        Perhaps you should also be aware that in the past I have switched the OS X Region setting from Canada to US and back again on several occasions as I travel to the US for extended periods.

                        Please know I appreciate your help with this matter.  I hope to upgrade to FM Pro 13 Advanced in the near future and would like to minimize as many problems as possible during the file conversion process.

                         

                        • 9. Re: Problem finding fields containing dates
                          TSGal

                          Wayne Green:

                          Date fields store the contents internally as a number.  The formatting determines how that field displays the dates.  Therefore, I would like to see the two database files - the main file and the secondary file from where you are importing.  I can then examine why the import of dates from the secondary file is failing.  Check your Inbox at the top of this page for instructions where to send the files.

                          TSGal
                          FileMaker, Inc.

                          • 10. Re: Problem finding fields containing dates
                            WayneGreen

                            Hi TS,

                            I have forwarded the files you requested, via regular email.  Please let me know what you find.  Thanks,

                            Wayne

                            • 11. Re: Problem finding fields containing dates
                              TSGal

                              Wayne Green:

                              Files have been received.  Thank you.

                              The "Original" file is definitely damaged.  If you create a new database file with a Date and Number field and import, the information from the "Date" file displays correctly.  If you create a new Date and Number field in the "Original" file and import, it continues to fail.  Recovering the file does not fix the issue.

                              Go to your last backup file and see if the problem persists.  If the data displays properly, then import the newest data from the "Original" file so your backup is up to date.  If the data still doesn't import properly, then go to an older backup file.  If you don't have a backup file that works properly, let me know, and I'll help rebuild the file.

                              TSGal
                              FileMaker, Inc.

                              • 12. Re: Problem finding fields containing dates
                                WayneGreen

                                Unfortunately, there is no way to recover the file.  Although I maintain back ups, I have been experiencing these Date Find problems for many months and the data in the file changes almost daily.  I have been able to script work-arounds for the Finds, so it hasn't been a pressing issue.  However, to move forward and to prepare for the transfer to FM 13, I need to address the problem now.

                                Can you forward the advice on how to rebuild the file?   Thanks,

                                Wayne

                                • 13. Re: Problem finding fields containing dates
                                  TSGal

                                  Wayne Green:

                                  A rebuild involves creating a database, the fields, the relationships, the forms, etc.

                                  It's my end of day here, so I'll spend a couple of hours tomorrow starting the rebuild and then sending the file to you.

                                  TSGal
                                  FileMaker, Inc.

                                  • 14. Re: Problem finding fields containing dates
                                    TSGal

                                    Wayne Green:

                                    I have worked on-and-off today rebuilding a new file.  All fields have been created.  Most of the layouts have been created.  A good start on the scripts.  This is a good starting point.  I have sent the file to our Technical Support contact, so you should receive an email from him shortly.  If you don't receive the file within one hour, please let me know, and I'll make sure it gets sent to you immediately.

                                    TSGal
                                    FileMaker, Inc.

                                    1 2 Previous Next