1 2 3 Previous Next 34 Replies Latest reply on Jun 9, 2017 11:36 AM by BruceRobertson

    Scripted Find Examples

    philmodjunk

      Title

      Scripted Find Examples

      Post

           Folks frequently ask how to perform finds in scripts where data specified by the user is used to create the needed find criteria. Such scripted finds can be much more user friendly than a manual find. This post presents the basic script I recommend for this purpose. At the end of this post I have also listed multiple examples of how the set field step can be used to create various find criteria.

           This script assumes that global fields have been placed on a layout where a user entered or selected data to be used as find criteria. This layout can be displayed via the New Window script step to be a small modal dialog where the user enters or selects criteria.

           The script will fail if you do not use fields with global storage specified in Field Options. Since global fields can be accessed from any layout and script in your file, such fields are often defined in a Globals table to better keep track of global fields used in the file.

           Go To Layout [Select a layout based on table being searched]
           Enter Find Mode [] ---> clear the pause check box
           Set Field [YourTable::YourField ; Globals::gField]
           Set Error Capture [on] ----> Keeps error dialog from interrupting script when no records are found
           Perform Find []
           If [Not Get ( FoundCount ) // no records were found ]
               Show Custom Dialog ["No records were found by this search."]
           End IF

           Other Set field steps you can use:

           Records with a date within a range of dates:
           Set Field [YourTable::YourDateField ; Globals::gDate1 & "..." & Globals::gDate2]

           Records with a certain date or older:
           Set Field [YourTable::YourDateField ; "<" & Globals::gDate ]

           Today's Date:
           Set FIeld [YourTable::YourDateField ; Get ( CurrentDate ) ]

           5 days from today:
           Set Field [YourTable::YourDateField ; Get ( CurrentDate ) + 5 ]

           Where a field is empty (Field should not be from a related table):
           Set Field [YourTable::YourField ; "="]

           Where a field is NOT empty:
           Set Field [YourTable::YourField ; "*"]

           Field equals exact text specified:
           Set Field [YourTable::YourField ; "==" & Globals::gField ]

           Word in field exactly matches specified text
           Set FIeld [YourTable::YourField ; "=" & Globals::gField ]

           Word in field ends with text specified:
           Set FIeld [YourTable::yourField ; Globals::gField & "*" ]

           Word in field starts with text specified:
           Set Field [YourTable::YourField ; Globals::gField ] ---> no wild card needed this is the default behavior for find criteria

           Records that do NOT have text specified:
           Set FIeld [YourTable::YourField ; Globals::gField]
           Omit Record

           Omit Record when in Find Mode is the same as clicking the Omit record button in a manual find. In multi-request finds, omit request should be created last as they modify the found set produced by the other requests.

           That barely scratches the surface, but should give you some ideas on some of the myriad ways you can specify find criteria in a script.

           Note for newbies creating a set field step for the first time:
           When Setting up Set Field, there are two Specify buttons that must be clicked. To get Set Field [Table::Field ; Expression], add set field to your script and click the first button (specify target field). Select Table::Field from the list of fields. Do not click the specify button next to the repetition box. Click OK to close this dialog box. Now click the lower specify button (calculated result) and create the expression to the right of the semicolon (;). Do not try to type in the semicolon.

      NOTE: This thread is now more than 3 months old. New comments posted to it no longer pop it up in Recent Items and thus are unlikely to be seen by others. If you were referred here from another thread, post any questions/comments there. Or you can start a new thread and include a link to this thread in your posted comment.

        • 1. Re: Scripted Find Examples
          philmodjunk

               Here is a more sophisticated example. Let's say we have a global field, Globals::gFruitList, formatted as a check box list with the values: Apple; Orange; and Pear. If the user clicks Pear, find all records with Pear in the Fruit field, but if the user clicks both Pear and Apple, find all records with Pear OR Apple in the fruit field:

               Method 1:

               Set Variable [$FruitList ; value: Globals::gFruitList ]
               Enter Find Mode []
               Loop
                  Set Variable [$K ; Value: $K + 1 ]
                  Set Field [YourTable::Fruit ; GetValue ( $FruitList ; $K) ]
                  Exit Loop If [ $K > valueCount ( $FruitList ) ]
                  New Record/Request
               End Loop
               Set Error Capture [on]
               Perform Find[]

               Method 2:

               Set Variable [$FruitList ; value: Globals::gFruitList ]
               Enter Find Mode []
               Set Variable [$K ; Value: 1 ]
               Set Field [YourTable::Fruit ; GetValue ( $FruitList ; 1) ]
               Set Error Capture [on]
               Perform Find[]
               Loop
                  Set Variable [$K ; Value: $K + 1 ]
                  Exit Loop If [ $K > valueCount ( $FruitList ) ]
                  Enter Find Mode []
                  Set Field [YourTable::Fruit ; GetValue ( $FruitList ; $K) ]
                  Extend Found Set []
               End Loop

               Clearly the script for the first method is shorter/simpler, but sometimes the extend found set option is easier to visualize when working with a complex set of criteria in a scripted find.

               Note: In both scripts, you do not have to use the variable $FruitList, you could instead modify the GetValue calls to be:

               GetValue ( Globals::gFruitList ; $K)

               and the call to valueCount would become:

               valueCount ( Globals::gFruitList )

               But tests have shown that using the variable like this makes for slightly faster execution of the script.
                

          • 2. Re: Scripted Find Examples
            ScottOplinger

                 PhilModJunk,

                 Thanks! It works when I use:

                 Enter Find Mode [Pause]

                 Perform Find []

                 If [Get (FoundCount) >1]

                 Go To Layout ["Personnel List" (Personnel Records0]

                 End If

                 And I've attached it to a button on my layout for my boss who is not as techy as I am. I may create a dialogue box later that will allow the user to enter just the last name. We'll see.

            • 3. Re: Scripted Find Examples
              j_morris

                   Phil -

                   When I create a Globals table, how should it be linked to the table I want to perform the find on?

              • 4. Re: Scripted Find Examples
                j_morris

                     Actually I figured out what I was doing wrong. I forgot to set the fields in the Globals table to global storage.

                • 5. Re: Scripted Find Examples
                  Fred(CH)

                        

                       
                            Omit Record when in Find Mode is the same as clicking the Omit record button in a manual find.
                        
                       Stunning…oui How many times i was searching this tip ???
                        
                       Thank YOU very much Phil !!!
                        
                       Bye, Fred (a five years experienced FileMaker's dev.)
                        
                  • 6. Re: Scripted Find Examples
                    miw

                         PhilMod:  I can make this work when it is only one field I am searching on.  However, when I try to include two or more field to search on I do not get the proper results.  Here is my script:

                          

                         Enter Find Mode []

                    Set Field [ Country::Country ; "*" & Country::FindCountry & "*" ]

                    Set Field [ Country::Industry ; "*" & Country::FindIndustry & "*" ]

                    Set Field [ Country::Region ; "*" & Country::FindRegion & "*" ]

                         Perform Find []

                          

                         The Country::FindCountry, Country::FindIndustry, and Country::FindRegion fields are my global fields.  The script executes after entries are made (if any) in those fields and a button is pushed to execute the find.  What am I missing?

                    • 7. Re: Scripted Find Examples
                      philmodjunk

                           @MIW,

                           What do you consider "proper results"?

                           If the country, Industry and Region fields are text fields...

                           If the current layout is based on the Country table...

                           Then your script should find all records where the fields contain the text specified in Findcountry, and, FindIndustry, and Findregion. It won't work that way if your layout is based on a different table occurrence than Country. And if you want to find all records with a specified country or Industry or region, you'll need to do this differently.

                           You may also need to modify each step to include the == find operator:

                      Set Field [ Country::Country ; "==*" & Country::FindCountry & "*" ]

                      • 8. Re: Scripted Find Examples
                        philmodjunk

                        NOTE: This thread is now more than 3 months old. New comments posted to it no longer pop it up in Recent Items and thus are unlikely to be seen by others. If you were referred here from another thread, post any questions/comments there. Or you can start a new thread and include a link to this thread in your posted comment.

                        • 9. Re: Scripted Find Examples
                          philmodjunk

                          Now that this thread has been moved to the new system, comments posted here will once again move the thread to the top. But it's still a good idea to reply back at the original thread if someone referred you here to see these examples.

                          • 10. Re: Scripted Find Examples
                            tinhx7

                            Hi,

                             

                            Method 1:

                                 Set Variable [$FruitList ; value: Globals::gFruitList ]
                                 Enter Find Mode []
                                 Loop
                                    Set Variable [$K ; Value: $K + 1 ]
                                    Set Field [YourTable::Fruit ; GetValue ( $FruitList ; $K) ]
                                    Exit Loop If [ $K > valueCount ( $FruitList ) ]
                                    New Record/Request
                                 End Loop
                                 Set Error Capture [on]
                                 Perform Find[]

                             

                            I don't understand why we create new record/request if we just want to find Pear and/or Apple from the checkbox list.

                            Can somebody explain?

                             

                            Thanks.

                            • 11. Re: Scripted Find Examples
                              erolst

                              tinhx7 wrote:

                               

                              I don't understand why we create new record/request if we just want to find Pear and/or Apple from the checkbox list.

                              You're looking for records where the field contains pear or apple, and multiple requests are executed as an OR search.

                               

                              You can try this out manually; if you enter Find mode and check both 'pear' and 'apple', you'd be looking for all records where both values are present (AND).

                               

                              But if you enter Find mode, check 'pear', then press ctrl/cmd-N to create a new request, check 'apple' (the exact same process as in the script) ... well, try it out.

                              • 12. Re: Scripted Find Examples
                                beverly

                                The OR search is a New Request (not a new record) in FileMaker, when you are in Find mode.

                                This is sort-of the SQL:

                                WHERE fruit = 'apple'

                                OR fruit = 'orange'

                                (two requests!)

                                 

                                 

                                Sent from miPhone

                                • 13. Re: Scripted Find Examples
                                  philmodjunk

                                  Note: when window is in Find Mode, New Record/Request creates a new request, it does not create a new record.

                                   

                                  Think of how a manual find works. If you want all records with "strawberry" OR "kiwi", you:

                                   

                                  enter find mode

                                  enter "strawberry" into the field

                                  Select "New Request"

                                  enter "kiwi" into the field

                                  perform find

                                   

                                  The loop generates the needed multiple requests.

                                   

                                  If you put Kiwi <return>strawberry into the field in a single request, you will get all records that have kiwi AND strawberry in the field. Records that have just "kiwi" or just "strawberry" would not be found.

                                  • 14. Re: Scripted Find Examples
                                    tinhx7

                                    I got it now.

                                    I confused between new record and new request.

                                    I thought we create new record during the search.

                                     

                                    Can the user still search for both or all fruits if none of the items are check?

                                     

                                    Thanks guys.

                                    1 2 3 Previous Next