7 Replies Latest reply on May 23, 2014 10:06 AM by TSGal

    Issue with date feild when in find mode.

    NonProfitEducationUser

      Summary

      Issue with date feild when in find mode.

      Product

      FileMaker Pro

      Version

      12

      Operating system version

      Windows

      Description of the issue

      When I do a search on a calculated date feild, it only gives me partial results. Please see the following post for all the steps I have taken to try to resolve this issue:

      http://forums.filemaker.com/posts/ae297968a5

      Steps to reproduce the problem

      Please see discussion board post link above.

      Expected result

      To be able to do a range date search based on a few months of the year (for monthly and quarterly reports).

      Actual result

      I can do some date searches, but not other searches. When I try to search for 11/*/2013, I get results from the 11th day of a month. (Have looked at reformatting all of the dates in many ways to try to resolve it already).

      Exact text of any error message(s) that appear

      No error message, or "no records found matching that critera" (when there should be results).

      Workaround

      Please see the linked discussion. No work arounds found, but some searches will give me results, and others won't. (But I can't run the searches I neeD).

        • 1. Re: Issue with date feild when in find mode.
          TSGal

               NonProfit Education User:

               Thank you for your post.

               Since finding records isn't working properly, either the Index for the date field is damaged, or the field is damaged.  Try these steps:

               1. Go into Manage Database, select the Date field, and click Options.  In the Options window, click the Storage tab, and change the Indexing from All to None.  Click OK, and close the file.  Reopen the file, and perform a Find that wasn't previously working.

               2a. If step #1 fails, then create a new Date field, name it similarly, click Create and click OK.  If the new field isn't automatically added to the Layout, then go into Layout mode and use the Field tool to temporarily place the field near the original field.

               2b. Go into Browse Mode, select all records, place your cursor in the new Date field, pull down the Records menu and select Replace Field Contents...

               2c. In the Replace Field Contents window, select the last option "Replace with calculated result".

               2d. In the Specify Calculation window, double-click the original Date field in the left column, and it should then appear in the bottom box.  Click OK to close the Specify Calculation window, and click Replace to replace the data.

               Your original date field information will now appear in the new date field.  Perform searches on this file to see if you can replicate the issue.

               Keep me posted with any progress.

               TSGal
               FileMaker, Inc.

          • 2. Re: Issue with date feild when in find mode.
            philmodjunk

                 TSGal, we already tried recovering the file--which also rebuilds indexes (I'm thinking that the indexes are hosed also). But he reported that the recovered file still had the same issue.

            • 3. Re: Issue with date feild when in find mode.
              TSGal

                   PhilModJunk:

                   My apologies.  I missed the post that a Recover was performed on a copy of the file.  Therefore, there is no need to follow step #1.  Instead, start with step 2a.  This will let me know if there is something wrong with the original date field.

                   TSGal
                   FileMaker, Inc.

              • 4. Re: Issue with date feild when in find mode.
                philmodjunk

                     I also advised that they add a new date field and use replace field contents to copy over the data into the new field.wink

                     That also did not fix the problem.

                • 5. Re: Issue with date feild when in find mode.
                  TSGal

                       PhilModJunk:

                       Even more embarrassing....
                        

                       NonProfit Education User:

                       Please send a clone of your database file as I will look it over and determine why the Find is failing.  Check your Inbox at the top of this page for instructions where to send the file.

                       TSGal
                       FileMaker, Inc.

                  • 6. Re: Issue with date feild when in find mode.
                    NonProfitEducationUser

                         Thanks to both of you for your help! Sending it now.

                          

                    • 7. Re: Issue with date feild when in find mode.
                      TSGal

                           NonProfit Education User:

                           I received your file.  Thank you.

                           I can definitely replicate the issue in FileMaker Pro 12.0v4 (Windows 7) and FileMaker Pro 12.0v5 (Mac OS X).  It appears the Aggregate value Max used with a Date field affects the search, where a wildcard search involving the month and year will instead search the day and year.  On the positive side, this works as expected in FileMaker Pro 13 (Windows and Mac).

                           Since no further development is scheduled for FileMaker Pro 12, I have made changes to your layout so that the calculated Date field automatically updates another Date field (via a Replace Field Contents), and I have replaced the original calculated Date field on the layout with the new Date field.  Whereas the original Script Trigger for that layout just sorted the Date field, I have changed it to call a new script that performs the replace and then sorts.  I have sent the file to our Technical Support contact, so you should receive the file shortly.  If you don't receive the file within one hour, please let me know, and I'll make sure the file is sent immediately.

                           TSGal
                           FileMaker, Inc.