3 Replies Latest reply on Jul 7, 2010 2:11 PM by philmodjunk

    Script to limit found set to two or more criteria from current record

    BobBrown

      Title

      Script to limit found set to two or more criteria from current record

      Post

      I am using File Maker Pro 10 to store notes about research done on the Bible.  A single table is used to store the notes.  One field ("Book No" - a number field) uses a drop down list to select the proper book and stores the book number (1 through 66).  Another field ("Note Type" - a text field) stores one of the following words:

      Portion, Section, Part, Paragraph, Sentence, Phrase or Word

      Each of these words represents a hierarchical level siimilar to an outline, a book may have many portions, a portion may have many sections, etc.

      My goal is to limit all records in the table to those that match a particular book number and one or more entries in the Note Type field.  Here's an example:

      Show all records where Book No = Book No on the current record, and

      Constrain that found set to those where Note Type = "Portion" or "Section

      I don't want to hard code the book number since it will change frequently.  The Note Type text will be hard coded since I will have a button for each level of the hierarchy.

      My plan is to have a series of buttons labeled with the words shown above that will allow the user to collapse or expand the found set in the same way a word processor can do that with an outline.

      This is my first database in File Maker.  However, I'm comfortable with Microsoft Access and VBA.

      Thanks,

      Bob

        • 1. Re: Script to limit found set to two or more criteria from current record
          philmodjunk

          Warning: Filemaker is a different animal than MS Access and VBA. The relational DB concepts are the same, but what works for SQL in VBA often requires a very different approach in Filemaker.

          To find all Portion or Section records with the same book number as the current record:

          Set Variable [$BookNumb; Value: YourTable::BookNumber]
          Enter Find Mode[]
          Set Field [YourTable::BookNumber ; $BookNumb]
          Set Field[YourTable::NoteType ; "Portion"]
          New Record/Request
          Set Field [YourTable::BookNumber ; $BookNumb]
          Set FIeld[YourTable::NoteType ; "Section"]
          Set Error Capture [on]//suppresses the "no records dialog" if no records are found
          Perform Find[]

          There are variations of this technique possible, you can make the last step of the above script Constrain Found Set if you want to use the script to simply remove records from the current found set without including other records not in the found set. You can pass a list of your Note Types as a parameter and use a loop in this script to build the find requests--this would enable you to have one script for all your buttons and you'd just set up your buttons to pass the desired types as a parameter to the script.

          MS Access to Filemaker helpful hints:
          SubForms and SubReports are the same thing as a Portal in Filemaker. Found sets are similar but not identical to record sets and every "box" in the relationship graph is called a "table occurrence" and has it's own found set, current record and current sort order--which can be accessed via a layout that refers to it in the show records from box in Layout setup...
           

          • 2. Re: Script to limit found set to two or more criteria from current record
            BobBrown

            Thanks Phil.  I tried your suggestion and it worked great.  Do you have any recommendations for a book that covers scripting in detail or a developer's reference book?

             

            Bob

            • 3. Re: Script to limit found set to two or more criteria from current record
              philmodjunk

              There are several available, I've never acquired a book on filemaker as I've used the product since filemaker pro 2.5. You can find them listed on Amazon and check out the reader reviews.