7 Replies Latest reply on Sep 2, 2015 6:54 AM by rtolliver

    Find Script: Find, Omit and Sort



      Find Script: Find, Omit and Sort


      I'd like to get some ideas for the best method to create a find script that will perform; 1) a Find, 2) Omit specified records and then 3) Sort the resulting found set.  This script will be initiated by a layout script trigger "On Layout Enter".  

      I've written basic find scripts with no problem but my requirement for this new script requires a better understanding of how FileMaker Pro does Finds.

        • 1. Re: Find Script: Find, Omit and Sort

          The best method is the one that always works for you and your users.  It depends on your needs.  A scripted find or a dynamic find-same for Omit and Sort?

          Do you want to enter find mode, have the user enter the requirements, or do you want to go to a find layout and restrict or guide the user?

          Phil has many examples of scripted finds posted to a thread, plus I think they are covered in one or more of his Adventures in FileMaking series

          • 2. Re: Find Script: Find, Omit and Sort

            Finds aren't at this time covered in the Adventures series, but I do have a thread of scripted find examples: Scripted Find Examples

            A typical scripted find to do what you want has at least two find requests, the first finds your records and the second specifies which of those matching records to omit if they match additional criteria. Then you add a sort records script step after the perform find to sort them as desired.

            But of course, there are many variations possible of that basic outline including using constrain found set and extend found set to further refine the found set produced by an initial find.

            • 3. Re: Find Script: Find, Omit and Sort

              Steve - Right now the layout is for user display only, I only want specific data from the table occurrence associated the layout.  I currently have a layout script trigger "On Layout Enter" that performs the first find. I've been unsuccessful with adding criteria "Specify Find Requests" as well as script steps.

              Phil mentioned a second step to specify which of the matching records to omit and I understand this, but the question I have is where does this second step go?  Is it an additional find script step with omit criteria or is the omit criteria added to the original find step?


              • 4. Re: Find Script: Find, Omit and Sort

                Please open and read the recommended link. There are script examples there that do this using the new record/request and omit records steps.

                Another example shows how to use Constrain Found Set, which could also be used.

                • 5. Re: Find Script: Find, Omit and Sort

                  I have look at the examples provided in the link and I've tried several different scripts to try an achieve the goal (find, omit and sort) but none have worked.


                  I've attached a screenshot of one of the script attempts.

                  Screen Shot 2015-08-27 at 3.31.54 PM.png

                  • 6. Re: Find Script: Find, Omit and Sort

                    Adding the Omit Record step while in Find Mode will toggle the Include/Omit status of the current Find request – exactly the waxy it does in “manual mode”.


                    What your script above does is omit all records where both criteria are true.


                    If you want to find all record where criterion 1 is true, and from the resulting found set omit all records where criterion 2 is true, you need


                    Set Variable [ $id ; Table::boardNo ]

                    Set Variable [ Globals::xStatus ; "Sold" ]

                    Enter Find Mode

                    # do this before the layout change, so you don't load records unnecessarily

                    Go to Layout [ RetailViewForm ( T07Inventory ) ]

                    Set Field [ T07Inventory::boardNo ; $id ]

                    New Record/Request

                    # adds a new request

                    Set Field [ T07Inventory::HS ; Globals::xStatus ]

                    Omit Record

                    # toggles only this request to “omit”

                    Set Error Capture [ on ]

                    Perform Find

                    If [ not Get ( FoundCount ) ]

                      Go to Layout [ original ]

                      Show Custom Dialog [ "No matching records" ]

                      Exit Script

                    End If

                    Sort Records [ ]

                    … etc.

                    • 7. Re: Find Script: Find, Omit and Sort

                      Thanks for the help!