5 Replies Latest reply on Oct 30, 2012 2:01 PM by brian.curran

    Performing multple Find requests in Scripts

    brian.curran

      Title

      Performing multple Find requests in Scripts

      Post

           Hi,
           Are there any FM rules to specifying Finds in scripts? If I perform a find to filter 'active' records and then another find to look for a certain value within those filtered records it all works fine but if I put both finds in a script, the first one appears to be ignored.

           Thanks
           Brian.

        • 1. Re: Performing multple Find requests in Scripts
          philmodjunk

               The rules for manual and scripted finds are the same.

               If you enter find mode manually, specify "active" in a status field and perform a find, then return to find mode and perform a find for a certain value, the results of the first find will also be ignored.

               In both cases, you need to either include the criteria as part of the same set of find requests or to use the Extend Found Set or Contstrain Found Set options/script steps in place of Perform Find.

               You haven't indicated whether you want all active records that also have a specified value (and AND search) or all active records or that have a specified value, but both are possible via scripting or by manual finds.

               Here's the "And" version of performing a find:

               Enter Find Mode [] ---> clear the pause check box
               Set Field [ YourTable::Status ; "Active" ]
               Set Field [YourTable::ValueField ; 25 ]
               Set Error Capture [on]
               Perform Find[]

               This will find all records that have a status of "Active" and a value of 25.

               By modifying the details, you can also get an OR type find.

          • 2. Re: Performing multple Find requests in Scripts
            brian.curran

                 Superb, many thanks Phil :)

            • 3. Re: Performing multple Find requests in Scripts
              brian.curran

                   Just run into a little snag as the script developed:

              Enter Find Mode []
              Set Field [ Site::SiteActiveFlag ; 1 ]     ---> finds active sites 
              Set Field [SiteDetails::_cZoneLists ; 0 ]     ---> finds empty container fields using the _cZoneLists calculation field
              Set Field [KeyHolding::_cEscortService ; 0 ]     ---> this is supposed to find blank checkboxes
              Set Error Capture [on]
              Perform Find[]

              The checkbox looks to have a value of 1 when ticked but I'm reading posts where it can have a value of 0 or null if empty depending on whether it has been ticked or not previously.

              I can change the "0" to "1" to have the exact opposite to what I want but I can't figure out how to get the result I need...

              • 4. Re: Performing multple Find requests in Scripts
                philmodjunk

                     You also have another issue here in that you are specifiying criteria in two other related tables. This can produce results that you didn't expect due to how finds work in FileMaker. When you or a script specify search criteria for a related table, you are telling FileMaker: "Find all records in the layout's table, that have at least one related record with the criteria specified in the field in the related table. Once the find is performed, normal FileMaker rules for how related data is displayed on your current layout apply and the related record that resulting in the record being found may not even be visible on your layout.

                     What value exists in your check box field will depend on how you designed the user interface. If it is a single check box that enters the value one when checked, then it will either store a 1 (if box is checked) or be empty (box is not checked). It won't store a zero unless some script or user action specifically enters such a value or if it is a calculation field that returns a False or 0 value. (It's very unusual for a calculation field to be formatted as a checkbox these days, but it is done once in a great while...)

                     But, as noted earlier, the fact that this is from a related table complicates this as a find criterion (=) that finds for records with empty fields won't find a record if there is no related record at all. I suggest the following script modification:

                Enter Find Mode []
                Set Field [ Site::SiteActiveFlag ; 1 ]     ---> finds active sites 
                Set Field [SiteDetails::_cZoneLists ; 0 ]     ---> finds empty container fields using the _cZoneLists calculation field
                new Record/Request
                     Set Field [KeyHolding::_cEscortService ; "*" ]
                     ---> this is supposed to find blank checkboxes
                     Omit Record

                Set Error Capture [on]
                Perform Find[]

                     While in find mode, New Record/Request creates a new request, not a new record and Omit record will make it an "omit request". The asterisk finds for all records that have at least some data in the field, so we are omitting all records where there is at least one related record with data in the _cEscortService field.

                • 5. Re: Performing multple Find requests in Scripts
                  brian.curran

                       Thanks Phil, that works perfectly. I tried adding an 'Omit Record' step earlier but obviously it wouldn't work without the 'New Record/Request' step first. My checkboxes are all designed with a stored value of 1