13 Replies Latest reply on Apr 23, 2013 9:08 PM by PatriciaSamuelsen

    Find Request



      Find Request


           I have tried everything I can think of to solve this problem, and I'm still not getting the expected results.  I hope someone can help me.


           I am running a Script to select all the enrollment records for students who are enrolled in classes with a targeted teacher.  Works perfectly.

           Now I want to modify the report so that I can re-run it for a previous month.  In other words I want to show students who have dropped the class now, but hadn't dropped it last month.

           I'm searching on a range of values in "Dropped Date".

           No matter what combination of Find, Omit, Extend, Constrain I use, I always end up returning a much larger set of records than I intended.  Obviously, the found set is getting restored somewhere.  If I could turn off the Restore option on a find request, I think it would work, but I can't figure out how that Restore option gets added.

           Can anyone help?






        • 1. Re: Find Request

               The restore option gets added by you when you add the step and click the specify button next to "specify find requests". If you don't specify criteria in a request, there is no restored criteria. I routinely leave that option unselected, perferring to enter find mode, use a series of set fields to set up the search criteria and then use Perform Find [] (note the empty brackets) to perform the find.

               I suggest that you list the specific methods you are using to find these records that aren't working for you.

               Off hand, specifying the teacher and a date or a date range in the date dropped field should find these records in a single fine request.

               See this thread for examples of scripted finds using set field: Scripted Find Examples

          • 2. Re: Find Request

                 Thanks for your help.

                 Here is the pertinent part of the report that works fine (finds all the enrollment records for students who are enrolled in classes with a targeted teacher.)

            Go to Layout [Enrollments" (Enrollments)}

                 Show All Records

                 Enter Find Mode []

                 Set Error Capture [On]

                 Set Field [Enrollments::TermID, $$TermID] (assigned above)

                 Set Field [Enrollments::Status, "Enrolled"]

                 Set Field [Enrollments::InstructorID, $$Target] (assigned above)

                 Perform Find [] 


                 No problems with that part.  It shows only the enrollments that have not been dropped to date.  The problem occurs when I want to add back in the records that have been dropped.  For example: three students dropped this month, but I want to rerun the report as it looked last month before they dropped.  (I'm just hard coding these dates in right now, just to get it to work.  Then I'll use user entered dates.)

                 I can either extend the found set from above to include:

                 Enrollments for the same term and teacher as above PLUS enrollments where:

                 Enrollments::DroppedDate > 3/31//2013 (to find records that have been dropped in April.  Since they were still enrolled in March, I want them to appear in the report.)

                 But how do I add the new found set to the old found set?

                 Or I can begin by omitting all the records where:

                 Enrollments::DroppedDate > 3/31//2013

                 and then constraining that found set to the term ID and the teacher ID I want.


                 The trouble seems to be that both of these options take two steps, and in between the steps I loose my found set some how, and my results include far too many records.

                 Any ideas?


            • 3. Re: Find Request

                   You'll need to figure out why your found set is changing. This isn't something that "just happens".

                   Perhaps you have a show all records step in your script where it shouldn't be.

                   From what I see here, there is no need for show all records anywhere in your script and if you have it after the find but before the extend, it will produce the results that you describe here.

              • 4. Re: Find Request

                     I have the show all records after I go to the layout and before I begin any finds so that I'm sure I'm beginning with the full set of records.  Once i begin the find commands, I don't have any more show all record commands.


                • 5. Re: Find Request

                       Here is at least part of the problem:  I want to search for a range of dates.  The only way I can see to do that is in the Find or Constrain or Extend commands where I can use a range  (...).  If I do that, though, it turns on the restore so I can't keep my found set in the next step.

                       BUT, if I use the set fields command to set up the search criteria, I don't think I can search for a range of dates.

                       Is that accurate?  How can I search for a range of date and not have the Restore option on?

                  • 6. Re: Find Request

                         Not pretending to know what the problem is, but if your script is performing a find, there is no need to show all records. The Find command will find what you need regardless of found set.

                    • 7. Re: Find Request

                           This is probably a really dumb question, but what does that "Restore" option actually restore?  I can't find an explanation anywhere.  It certainly can't undo the find or there would be no point in doing a find.



                      • 8. Re: Find Request


                             First, you can simplify your current script as follows:


                             Enter Find Mode []—DELETE THIS LINE

                             Set Error Capture [On]

                             Set Field [Enrollments::TermID, $$TermID] (assigned above)—DELETE THIS LINE

                             Set Field [Enrollments::Status, "Enrolled"]—DELETE THIS LINE

                             Set Field [Enrollments::InstructorID, $$Target] (assigned above)—DELETE THIS LINE

                             Perform Find []—SEE BELOW

                             Use the Specify Find Requests dialog to insert the three requests you are using; five lines of script are thus reduced to one.

                                  Next, use the Extend Found Set step, specifying the new records you want—this may involve repeating some of the requests in the initial Find step (eg. $$TermID and $$Target) but adding the additional request of records with a "dropped" date. This should add those reords with necessary matching criteria as your initial Find but also meeting the new criteria. If you simply included the "dropped" date in the Extend step you would add any student who had dropped any class.

                                  As to you question about the Restore option: this "restores" (in other words, uses) whatever criteria you have specified as detailed above.

                                  Hope that helps.




                        • 9. Re: Find Request

                               Can anyone point me to a comparison of the relative merits of these two ways of accomplishing a find?  Philmodjunk prefers to list all the search criteria.  Bill Elemor prefers to put them in the Find Request dialog.  

                               The only real difference I have encountered is that it makes it easier to read through the Script if the search criteria are listed.

                          • 10. Re: Find Request

                                 You have stated one of two reasons why I prefer not to store criteria inside the script step--it makes it simpler to review the criteria used in the find. The second reason is that all too often at least some of the criteria is not the same every time, but changes each time due to user entered data in fields. While I could use a variable inside the stored criteria, since I have to add a set variable step to assign a value to the variable, I might as well keep it as set field and still be able to read the script without double clicking the script step to open a dialog just to check the criteria used.

                                 I suggest posting your actual script.

                                 To post a script to the forum:

                            1.           You can upload a screen shot of your script by using the Upload an Image controls located just below Post A Answer.
                            3.           You can print a script to a PDF, open the PDF and then select and copy the script as text from the opened PDF to your clipboard for pasting here.
                            5.           If You have FileMaker Advanced, you can generate a database design report and copy the script as text from there.
                            7.           If you paste a text form of the script, you can use the Script Pretty box in the Known Bugs List database to paste a version that is single spaced and indented for a more professional and easier to read format.
                            • 11. Re: Find Request

                                   This is what I need to do:

                                   Select * from Enrollments as e where e.TermID=1 AND e.InstructorID=9 AND e.DropDate > 0 AND e.DropDate < "3-1-2013"


                                   The problem is that I can set fields for TermID and InstructorID, but not for the DropDate.

                                    When I use the Specify Find Request  for the date criteria as below, I get  a union of the four criteria instead of an intersection.




                              • 12. Re: Find Request

                                     My screen shot didn't come through, but here are the Find Requests:

                                Action                  Criteria

                                     Find Records     Enrollments::TermID [=$$TermID]

                                     Find Records     Enrollments::InstructorID [=$$Target]

                                     Find Records     Enrollments::DropDate [=01/01/1980]

                                     Find Records     Enrollments::DropDate [<03/01/2013]

                                • 13. Re: Find Request

                                       Finally found the solution.

                                       THanks for your help.