10 Replies Latest reply on Jul 30, 2012 10:05 AM by philmodjunk

    Using Checkboxes to do a find



      Using Checkboxes to do a find


      I'm a new user of FM Pro12 so pardon me if this is a stupid question.  I regularly need to search records that match several possible criteria within the same field or fields.  For example, let's say I need to find sales of several different products within several different sales districts.  From what I've read it seems like I would need to laboriously add a separate "find" request to extend my search each time (or alternatively do a "constain" request).  Is this really the case?  (I know I can save a complex find to reuse it, but my find requirements change all the time.)

      What I'd like to do is set up a special "find" layout with checkbox fields for my sales products and districts so that I can just check the products and districts I'm interested in, hit "find" and have FM return the subset of records that match any of the checked districts together with the checked products.

      Any help/clarification much appreciated!

        • 1. Re: Using Checkboxes to do a find

          Your two options for "OR" type finds (find record matching criteria 1 OR criteria 2 or...) are to produce multiple requests--which can be very easy to set up or to use EXTEND found set, not constrain found set.

          But what you describe can be done with scripting.

          Your find layout can use fields with global storage and then a script can use the data entered in these global fields to create the needed find requests.

          Say you define a global field named Globals::gStatus with checkboxes. One enters the value "active" and the other enters the value "pending".

          Your script might look like this:

          Go to Layout [Select layout based on the table you are searching where you want to display find results]
          Enter find mode [] //clear the pause check box
          Set variable [$K ; value: 1 ]
             Set Field [Yourtable::Status ; GetValue ( Globals::gStatus ; 1 ) ]
             Exit Loop If [$K > valueCount ( Globals::gStatus ) ]
             New Record/Request
             Set Variable [$K ; value: $K + 1 ]
          End Loop
          Set Error capture [on]
          Perform Find []

          gStatus must be set up with global storage or this script will not work.

          For more examples of set field steps that create find criteria see the sample script at the beginning of this thread: http://forums.filemaker.com/posts/ba7347f58a

          • 2. Re: Using Checkboxes to do a find

            Thanks for your prompt reply.  Alas, as a new user of FM with no scripting background I really cannot parse your script.  You say that it "can be very easy to set up" multiple requests.  Would that be an easier way to go?



            • 3. Re: Using Checkboxes to do a find

              The script, once it is created and working is very easy to use. Scripted finds are quite common in FileMaker solutions. I recommend that you invest the time and effort to learn scripting as creating filemaker solutions without scripting is like rowing a boat with only one oar.

              What I'm getting at with that "easy" comment is that you have severla options that appear in the Requests Menu (only visible while in Find mode) and their keyboard short cuts that can make this work pretty quickly once you are familiar with them.

              You can press ctrl/command-N while in find mode (ctrl in windows, command in Mac) and you get a new request. Press ctrl/command D and you can duplicate the request. Thus, you can enter all the criteria that you need to specify in all your requests, the duplicate the request and edit it to specify just the criteria that differs from the first request, repeating this duplication of find requests until you have each request that you need to perform a find.

              • 4. Re: Using Checkboxes to do a find

                Thanks for your comments.  For now I'll give the command D a try and see how that works.  I am clearly not getting the logic of the finds right at that moment but I think I'm getting there.  Learning scripting will have to be a longer term project alas.

                • 5. Re: Using Checkboxes to do a find

                  Re your scripting example, as I mentioned before I really have no idea how to parse it.  However, I am trying to follow your suggestion and see how difficult it might be to learn scripting.  In that context, I've been reviewing the use and meaning of global fields.  I don't understand exactly what you're suggesting with regard to the use of a global field for my checkbox categories.  As I understand it, a global field can only have one value across all records.  Using my example of a checkbox field to enter one or more of several districts and/or products, I don't see how that would work.  The only example I've come across suggests using a global field to record an invoice number or address that would be the same regardless of what records were retrieved.  That's clearly not the use I have in mind.

                  Also, do you have any suggestions about where to go to find out about scripting?  There's virtually nothing in the User's Guide.  (Or do you any consulting by any chance?Laughing)

                  • 6. Re: Using Checkboxes to do a find

                    Misha... Phil does have a consulting business and in my opionion is a Master of Data Base design and Filemaker use.

                    I am chiming in to help the with your scripting learning... you still need to understand them, make them your friend and get a feel of when to use them.

                    Scripting....  [or programing]

                    it is a way to tell the FMP program to do a repeating task, navigating or moving around your data base, or helping your "user" do optional actions.  Opions will vary on when to use them, but it never will launch a nuke missle.

                    The problem is first thinking out on paper what task you do and THEN translating to FMP scripting language.


                    Make a new blank FMP data base for your leaning and testing [you wont drown in shallow end of the poolWink] name it "Training".

                    1) Make only one TextField called "Greetings" and go to Layout mode to see FMP created a blank Layout with that text field in the body.

                    2) From FMP menu bar go to Sripts and select Manage Scripts sub menu which brings up a FMP window to manage all scripts and is empty.

                    3) On this Script window.. click the New button at the bottom left to get the "Edit Script" window and Highlite "New Script" as the default script name.

                    4) You will the type in a name to help your easily remember by that name what the script does... like "GreetTheClient" or "Say Greetings" or "My First Script"

                    5) On left side is the list of Scripting commands [or translation dictionary from your thoughts to FMP actions] you can scroll and see categories of commands.

                    6) Scrool down to the Fields Category and click on the first in list "Set Field".  It highlights ahd Move button. Click Move to put the first sequecial step in  your new sript named [ i like GreetTheClient ].

                    That "Script Step" is added to a list that is executed from top to bottom of your GreetTheClient, when you tell GreetTheClient to "Run". 

                    Set Field script step does:

                    Set Field [<table::field>; <value or formula>]
                    Select Specify target field or click Specify to specify the field whose contents you want to replace. If no field is specified and a field is selected in Browse mode or Find mode, that field is used.
                    For Calculated result, click Specify to define the calculation. In the Specify Calculation dialog box, type the calculation you want evaluated, or use the field list (on the left) and the functions list (on the right) with the mathematical and text operators to build the calculation.


                    Which is filled out useing the info at bottom of Edit script menu

                    So click on "Select" hightlight button to specify the field to change.  Up comes a window with just one field Named Greetings, click on Greeting and "OK"

                    7) See FMP fill out the info need in the Set Field command it should be "Training::Greetings".  Ponder what just happened.......  

                    8) You told FMP you want to change the contents of the Greetings field...  but only when the GreetTheClient script is Run.

                    9) Now FMP waits on what to Calculate [really any action allowed not pure math] so click on the Calculate "Specify" Button.

                    10) a new window that you may be accustomed with appears but we want to click on the "" operator button only.

                    11)You will get a "|" in the hightlight progamming box below and type "Good Morning!" [note at bottom Calculation must be Text since we are working with a TextField.]  Click OK.  See how FMP filled out the necessary parameters of the Set Script step on the Edit Script window?Cool

                    12) Now we want to Save your script programing, by closing the Edit Script window (depending on your FMP version] Click Save

                    13) You just programmed a simple script.... and it should be Listed in the Manage script winow for later editing if needed.

                    How to Perform[run] this Script....

                    1) In layout mode, create a new button in the Footer section of the Layout [I assume you know this part] up pops a Button Setup window and click on... guess what?   ..... Perform ScriptWink

                    2)Click "Specify" then select your new script name and hit OK and not how FMP filled out the info on Perform Srcipt in Bold at the top.  Click OK

                    3) Label your Button "Greet".  Go to Brose mode in "one record at a time" view. Create a new record... and and and...

                    Click on your new scripted Greet button...

                    What happened? [Misha nealy faints from the thrill of her overcoming scripting fears?]Surprised  I am joking... it is not that scary.

                    Last quick intro....

                    Lets Edit your Script...  you can do this in the Browse mode by Scripts | Manage scripts | Select script name | Edit....

                    1) This brings open your script to Change or Edit.

                    2) I want you to program your script to look like below...

                    3) Save Script and Hit Greet Button to note the "Top to bottom" execution of each script step.

                    4) Explore and have fun scripting...


                    • 7. Re: Using Checkboxes to do a find

                      Ooopps I miss named the script...Innocent


                      • 8. Re: Using Checkboxes to do a find

                        Thanks Jim!

                        About fields with global storage. Your information is correct on global fields and you can even find instructions that tell you that you can't search on a global field, BUT that is all beside the point.

                        If you go back and look at that script, You'll find that data entered by you into the global field is copied via set field steps into fields that are not global. Thus, the fact that you have entered the data into global fields does not keep this from working and in fact, the script example that I shared requires that these fields be global. Here's why:

                        Put two fields on a layout. Make one field a global field, the other a non global field. Enter some data into both fields. Now enter find mode. Which field still shows the data that you entered? The global field of course. This is why scripts that use data entered by the user to perform a find frequently use global fields to temporarily store the data to be used as search criteria.

                        This leads to another key observation when it comes to exploring scripts in FileMaker:

                        A very large percentage of the script steps have a corresponding menu option for performing the same action manually. Thus, when first starting out, you can work out how to do what you want manually, write down each step and then go to manage scripts and start hunting out the script steps that do the same thing.

                        • 9. Re: Using Checkboxes to do a find

                          Thanks to both Phil and Jim for your efforts and responses.  I'd love to be able to devote the time to figure out how this works and to teach myself the scripting.  My sense, from both of your answers, is that while scripting may be relatively simple, the particular implementation I want -- searching by selecting multiple checkboxes in multiple fields is not the place for a novice to start.  Accordingly I've found someone who can help me via guru.  We'll see how it goes. 


                          However, I have to say that I find it surprising that this capability isn't built into what's reputedly THE premier DB program.  Surely searching by and across multiple criteria should not require any scripting knowledge at all!  Heck, I was doing pretty much the same thing by checking boxes in various columnar sorts on a 35,000 record DB I was maintaining in Excel.


                          Thanks again for your help.

                          • 10. Re: Using Checkboxes to do a find

                             Surely searching by and across multiple criteria should not require any scripting knowledge at all!

                            It does not require scripting, but you asked specific questions for which the best way to do what you asked was to use scripting. As I have desribed earlier in this thread, you can also perform such searches manually. The scripting was to save time and make things more user friendly.