8 Replies Latest reply on May 24, 2011 8:33 AM by philmodjunk

    Finding two conditions in script

    dicksmith

      Title

      Finding two conditions in script

      Post

      I have two related tables, Members and Records.  I want to find those records for which Members::status = "Active" AND Records::CMA_yesno = "no".  When I use script step Perform Find Members::Status:[="Active"] I get those just fine, and when I change the step to Records::CMA_yesno:[="no"] that works fine also.  But when I combine the two into a two-step Find statement resulting in Members::status = "Active" AND Records::CMA_yesno = "no" , then I get a null result.

      Same result when I used a one-step Find Members::status = "Active" followed by a Constrain Found Set Records::CMA_yesno = "no". 

      The layout I'm using is based on "Members". What am I leaving out?  Thanks for your help

      script.jpg

        • 1. Re: Finding two conditions in script
          philmodjunk

          Can you perform this find by hand and get the records you expect?

          Based on what you describe, what you are attempting seems correct, but it will only find records that have a related record with the specified value.

          I don't know if you are searching from a Members layout or a Records layout. If you perform this find from a members layout, you are asking FileMaker to find all members records where status = "Active" and which also have a related record in the Records table where CMA_yesno = "no".

          If you peform the find from the records menu, then you are searching for records from the Records table where CMA_yesno = "no" and which also have a related record in Members where status = "Active".

          • 2. Re: Finding two conditions in script
            dicksmith

            Yes, I get the desired result when I search manually. the search is done from a Members Layout.

            Here is the script:

            and here is the Find within that script:

            • 3. Re: Finding two conditions in script
              dicksmith

              Oops, sorry about the non-images.  Can't figure how to get these images in the post.

              • 4. Re: Finding two conditions in script
                philmodjunk

                Easiest is to use the edit link to upload the image to the initial message at the start of this thread. Or you can upload the image to a file sharing site and then post the link to the image into one of these replies to the original post.

                • 5. Re: Finding two conditions in script
                  dicksmith

                  Screenshot of script now posted.

                  • 6. Re: Finding two conditions in script
                    philmodjunk

                    Can't see what criteria you specify inside the Perform Find [Restore] step. That's one of the reasons why I'd write the script with this format:

                    Members::status = "Active" AND Records::CMA_yesno = "no"

                    Go To Layout ["Missing CMAs" (members) ]
                    Enter Find Mode [] //clear pause check box
                    Set Field [members::status ; "Active" ]
                    Set Field [Records::CMA_yesno ; "no" ]
                    Perform Find []
                    Sort [Restore ; no dialog ]
                    Print [restore]
                    Go To Layout [original layout]

                    This assumes that CMA_yesno is of type text--not a number with boolean formatting set up on your layout to display Yes if 1 and No if 0 or blank.

                    Once testing shows that it works correctly, you can add Set Error Capture [on] just before the Perform Find step to keep the "no records found" dialog from interrupting your script when there are no such records to find. You can use If [get (FoundCount) = 0] or If [Get ( LastError ) >. 0 ] immediately after the Perform find to check for no records found and show a message telling the user why no records were printed.

                    • 7. Re: Finding two conditions in script
                      dicksmith

                      Got it.  The problem was that CMA_yesno is a calculation field, but with the result set to Number, not Text.  Duh. 

                      I notice in your example that you use Enter Find Mode and then explicitly use Set Field to specify the Find conditions, instead of what I did, namely use Find with the conditions set in the Find dialog.  Your way displays the conditions as part of the script, whereas in my way those conditions are hidden.  But operationally I assume those two approaches are equivalent?  And thanks for the hints about error trapping; I guess I'm getting to the point in my Filemaker education where I can begin to pay attention to that. 

                      • 8. Re: Finding two conditions in script
                        philmodjunk

                        Yes, both options function the same. It's just that my method saves time when you revisit this script at some point in the future and don't have to open up another dialog just to see what criteria was used when the find was performed.