12 Replies Latest reply on Sep 26, 2011 8:51 AM by KBGF75

    find records in a date range

    KBGF75

      Title

      find records in a date range

      Post

              In my FMP7 file the Donations file contains records of donor gifts over a period of years. I want a script that will find all donation records in a specified date range. My approach has been to create a global field in the table, “Date_range_for_find”.  The user would first enter the date range in that field, then execute the script. 

            When this script can be made reliable, I plan to add more scripts that will use other search criteria but with the same date range. That’s why I chose to have the user specify the date range in its own field. This field is formatted for text, as I couldn’t make it work when formatted for date. A copy of the script is below.

           The script worked once, finding 259 records in a date range of 1/1/2009..12/31/2009.  Then I changed the date range field, but subsequent runs of the script seemed to find the same 259 records.

           Evidently the script (below) retains the initial date-range value, rather than using current contents of the date range field.  What am I doing wrong? Did it just memorize an earlier find command?

            -Al

       Enter Find Mode [ Specified Find Requests: Find Records; Criteria: Donations::Date of donation: "1/1/2009..12/31/2009" ]

      Go to Layout [ "Donation Data Entry" (Donations) ]

      Go to Field [ Donations::Date of donation ]

      Set Field [ Donations::Date of donation; Donations::Date of donation = Donations::Date_range_for_find ]

      Set Error Capture [ On ]

      Perform Find [ Specified Find Requests: Find Records; Criteria: Donations::Date of donation: "1/1/2009..12/31/2009" ]

      [ Restore ]

      Go to Layout [ "Donation Reports" (Donations) ]

      Set Error Capture [ Off ]

        • 1. Re: find records in a date range

          You are storing a find twice, in the enter fine mode and in the perform find mode. This overrides anything else you do. Uncheck the Perform Find checkbox in both steps.

          Better yet, re-enter the step with nothing checked just to make sure that it doesn't hang around for later problem causing.

          Go to Layout

          Enter Find mode

          Set field

          Perform find

          That's all you need. Don't check anything and uncheck anything that's checked.

          • 2. Re: find records in a date range
            KBGF75

            I made the changes, re-entering the Enter Find Mode and Perform Find steps. The script ends in the expected layout, Browse Mode, but with zero recods found. Here's the revised script:

            Go to Layout [ "Donation Data Entry" (Donations) ]

            Enter Find Mode [ ]

            Set Field [ Donations::Date of donation = Donations::Date_range_for_find ]

            Set Error Capture [ On ]

            Perform Find [ ]

            Go to Layout [ "Donation Reports" (Donations) ]

            Set Error Capture [ Off ]

             

            • 3. Re: find records in a date range
              raybaudi

               Set Field [ Donations::Date of donation = Donations::Date_range_for_find ]

              I can see TWO possible errors here...

              1) the step has to be: Set Field [ Donations::Date of donation ; Donations::Date_range_for_find ]
              2) the field Donations::Date_range_for_find must be a global field

               

              • 4. Re: find records in a date range

                And use insert calculated result rather than set field since the date range isn't a date but a range.  If that doesn't clear it up then we'll work on the date range.

                • 5. Re: find records in a date range
                  LaRetta_1

                  Set Field[] is still the best choice.  Set Field[] does not require that the field be on the layout like Insert Calculated Result[].  And setting a date with a text range works fine since vs. 7.

                  I take it that you don't have Advanced to watch script debugger?  Try this to assist your debugging -  add Pause immediately after the first Set Field[] and see what enters into the field.  Also, include this script step immediately after the Perform Find[] ... Show Custom Dialog [ Get ( LastError ) ].  Let's see the error message.  Then take that number, go to FM Help and search 'FileMaker Pro error codes' and see what the error says.

                  But I, like Raybaudi, suspect you didn't enter a Specify Target Field portion.  YOu have:

                  Donations::Date of donation = Donations::Date_range_for_find

                  ... and it should be only Donations::Date_range_for_find  in the calculation box.

                  • 6. Re: find records in a date range
                    KBGF75

                     Here's a modification that works:

                               Go to Layout [ "Donation Data Entry" (Donations) ]

                               Enter Find Mode [ Specified Find Requests: Find Records; Criteria: Donations::Date of donation: "1/1/2009..12/31/2009" ]

                               Go to Field [ Donations::Date of donation ]

                               Set Field [ Donations::Date_range_for_find ]

                               Perform Find [ Specified Find Requests: Find Records; Criteria: Donations::Date of donation: "1/1/2009..12/31/2009" ]

                    I think the key for me was LaRetta's suspicion that I didn't handle the Set Field step properly. Thanks to all for the advice.

                    As an aside, I see a specific date range above in the "printed" copy of the successful version. I don't understand why that particular range appears, since it's not the range that was tested successfully just before my copy-print of the script. But maybe I don't need to know, and I'm inclined to leave well enough alone.

                           -Al

                     

                    • 7. Re: find records in a date range
                      LaRetta_1

                      I suggest that you rethink this a bit, Al.  If you don't stop and understand what works and why then you will struggle with this same thing the next time you run into it.  Set Field[] is THE MOST IMPORTANT script step you will ever use and if you don't take the time to understand why it didn't work then you will go through this same headache next time. 

                      You do not need to enter that date range in both Enter Find[] and Perform Find[], as Jack Rodgers pointed out and you do not need both a Set Field [] to set the range AND stored range also within Perform Find[] - you have it listed three times instead of just once.  Let's walk through the logic again:

                      1) Users should type into a global text field to enter the range.  Personally, I would not allow this because they can type anything in text and FileMaker cannot validate that it is a proper date format nor that the start is before or equal to the end.  I would supply gStartDate and a gEndDate (two global DATE fields).  Then you can let FM native validations correct the user if invalid dates are attempted.

                      There is another added benefit to using two fields instead of making a User type elipses:  If you leave gStartDate blank, the minimum is assumed and if you leave gEndDate blank then no end date is assumed.  What enters into the search field (if gStartDate is empty) is:

                      "... 12/31/2009"

                      This translates to 'find all records less than or equal to 12/31/2009'.  Same is true reversed if no gEndDate and it enters as:

                      "1/1/2009..."

                      which translates to 'find all records greater than or equal to start date'.

                      It adds flexability to your finds, provides date validations, and is easier to implement (less scripting).

                      2) And now the Find script to attach to the button:

                      Go to Layout [ "Donation Data Entry" (Donations) ]
                      Enter Find Mode [ uncheck find request, uncheck pause ]
                      Set Field [ Donations::Date of donation ; gStartDate & ".." & gEndDate  ]
                      Set Error Capture [ On ]
                      Perform Find [ uncheck find requests - you don't want it here]
                      If [ not Get ( FoundCount ) ]
                      Show Custom Dialog [ "No records found for " & gStartDate & " through " & gEndDate
                      Halt Script
                      End If
                      ... put any script steps here if you want to do something with this found set before returning it to the User

                      Set Field[] script step has two buttons: 

                      1) Specify Target Field (the field you wish to set).  Make sure the field you wish to set is listed here.
                      2) Specify Calculated Result (the field or calculated value you want).  The calc box should only have gStartDate & ".." & gEndDate

                      :^)

                      • 8. Re: find records in a date range
                        KBGF75

                         You're right, I certainly should make the time to understand what works and why, and I'm grateful that you're taking the time to help me do so. I really didn't realize that I was entering the data range three times, which reveals my shallow depth of understanding. I need a bit of time to study your last posting, and I have several other commitments to meet before I can post again. I'll be back

                             -Al

                        • 9. Re: find records in a date range

                          One additonal thought:

                          I would not let users type a date or a date range into a global field since they will type it incorrectly...usually. I would use two global date fields with calendars assigned to ensure the date is correclty entered.

                          Use a Find Layout with two global fields and a Find button.You can go to this layout by any method you choose: button or menu selection

                          Find Layout Objects:

                          g_start_date with a calendar assigned using the Inspector
                          g_end_date with a calendar assigned using the Inspector
                          Button: Find with the script below
                          Button: Cancel which you assign to a layout or script of your choice

                          The steps are:

                          • Go to the Find Layout
                          • Enter the dates in two global fields
                          • Click on the Find button
                          • Or Click on a Cancel button which takes you to your preferred layout which you designate

                          To BEGIN your find you go to this layout and upon entering the field a calendar pops down to select the date. Select your date in both fields. I find setting both fields to not tab works best.

                          Both fields are the globals referred to above and both have calendars. This eliminates ALL typos for your user and does not require any technical knowledge of all of the cool Filemaker shortcuts.

                          The Find button performs the script noted above which I will modify just a bit after the perform Find step:

                          (perform find)

                          If(get(foundcount) = 0)

                          *** insert your error routine here
                          *** if the dates are in reverse order you can tell them using a dialog

                          elseif( get(foundcount) = 1)

                          *** go to a form layout

                          else

                          *** go to a list layout

                          end if

                           

                          One Problem: if the start dates are entered in reverse no records will be found but that is why the date fields are labeled start and end. This can be handled inside the script by telling the user to reverse the dates

                           

                          • 10. Re: find records in a date range

                            Well, I was self-inspired to create a blog page on this topic for new developers:

                            http://wp.me/p1AmGx-dC

                            Hope this makes it easier.

                            • 11. Re: find records in a date range

                              http://wp.me/p1AmGx-dC

                              Forgot to link it... sorry...

                              • 12. Re: find records in a date range
                                KBGF75

                                Thanks to Jack Rodgers for the helpful additional thought(s), which I'll digest and try to apply as time permits. FYI, I have kept this db for a small non-profit, starting with a flat file 12 yrs ago and going relational about six years ago. One colleague has write access via IWP, a couple of others read only.

                                     -Al

                                Jack Rodgers said:

                                One additonal thought:

                                I would not let users type a date or a date range into a global field since they will type it incorrectly...usually. I would use two global date fields with calendars assigned to ensure the date is correclty entered.

                                Use a Find Layout with two global fields and a Find button.You can go to this layout by any method you choose: button or menu selection

                                Find Layout Objects:

                                g_start_date with a calendar assigned using the Inspector
                                g_end_date with a calendar assigned using the Inspector
                                Button: Find with the script below
                                Button: Cancel which you assign to a layout or script of your choice

                                       etc, etc