11 Replies Latest reply on Oct 2, 2015 6:35 AM by Extensitech

    Scripting a Search for Records Using OR

    raykennedy

      It appears there is already and "AND" statement built into the search specification. Is there a way to search using "OR"? So for example logically it would read like this...

       

          weight < 3 AND weightType = "pounds" OR weightType="ounces" .....

        • 1. Re: Scripting a Search for Records Using OR
          erolst

          raykennedy wrote:

           

          It appears there is already and "AND" statement built into the search specification. Is there a way to search using "OR"? So for example logically it would read like this...

           

              weight < 3 AND weightType = "pounds" OR weightType="ounces" .....

           

          The AND is “built-in“ when you query different fields in the same query.

           

          For an OR, you need to define another query (request); e.g.

           

          Enter Find Mode

          Set Field [ weight ; 3 ]

          Set Field ( type ; "pounds" ]

          Duplicate Record/Request

          Set Field ( type ; "ounces" ]

          Perform Find

          1 of 1 people found this helpful
          • 2. Re: Scripting a Search for Records Using OR
            raykennedy

            Wow, that worked perfectly on a couple of tests. I thought for sure the duplicate record request would conflict with a the original request. Thanks.

            • 3. Re: Scripting a Search for Records Using OR
              beverly

              yes, the OR in find mode uses the New Request.

               

              The Extend (or the modify last find) also may help you make additional requests.

               

              beverly

              • 4. Re: Scripting a Search for Records Using OR
                erolst

                raykennedy wrote:

                I thought for sure the duplicate record request would conflict with a the original request.

                 

                Why would it? Your original description of

                 

                weight < 3 AND ( weightType = "pounds" OR weightType = "ounces" )

                 

                is shorthand for

                 

                weight < 3 AND weightType = "pounds"

                OR

                weight < 3 AND weightType = "ounces"


                which is exactly what the script does: one query with two criteria as AND, another query with different criteria as AND, both combined as OR.


                The Duplicate step in this scenario saves you one Set Field[] step.


                You could of course define a different set of criteria for the second request ( e.g. weight < 30 AND weightType = "ounces"), in which case duplicating the first one wouldn't be helpful.

                • 5. Re: Scripting a Search for Records Using OR
                  Extensitech

                  Not trying to self-promote here, but I get a lot of questions about advanced finds, even from FM users (clients and newer developers) that have used FM for years, so I wrote a blog some time back about Finds, just so I could stop giving the same recap over and over. :-)

                   

                  http://extensitech.com/power-finds/

                   

                  Hope this is helpful.

                   

                  Chris Cain

                  Extensitech

                  • 6. Re: Scripting a Search for Records Using OR
                    raykennedy

                    Wasn't sure if it would. I am use to MySQL statements where you have to repeat the call in OR statements so if I wanted to do something like this...

                     

                    color="blue" AND year="1965" AND weight=20 OR color="blue" AND year="1965" AND weight=15

                    So even if you change one value you have to repeat the other same values. I guess that is just how I think of things but i do realize that FMP is pretty sophisticated so it makes things easier for complicated queries. For me, it's just learning that and getting use to that.

                     

                    It worked well in this case too because I was also creating a query like below and all I had to do was repeat the weight with the new value so it didn't confuse itself.


                    weight < 3 AND weightType = "pounds"

                    OR

                    weight > 0 AND weightType = "ounces"


                    I was impressed how they kept them separate, thats the part I wasn't sure on how it would handle it.

                    Great information, helps me out a lot.

                     

                    One incredible thing about FMP is when I think there are limitations there always seems to be solution, just have to retrain my thinking on how to solve these based on how FMP works.

                     

                    This group is incredible in how it helps. I would really be stuck without you guys.

                    • 7. Re: Scripting a Search for Records Using OR
                      erolst

                      raykennedy wrote:

                      I was impressed how they kept them separate, thats the part I wasn't sure on how it would handle it.

                       

                      Think “data is structured in fields within records” and "criteria are structured in fields within queries" …

                      • 8. Re: Scripting a Search for Records Using OR
                        Extensitech

                        For those cases where you don't want to repeat a bunch of criteria, but aren't exactly duplicating, you may want to consider Constrain and Extend.

                         

                        Chris Cain

                        Extensitech

                        • 9. Re: Scripting a Search for Records Using OR
                          beverly

                          great blog, Chris! the link to the download file is "not found error".

                           

                          I did an article for Matt/ISO ezine, eons ago (before Constrain & Extend!).

                           

                          There are some tricks to make OR, AND & NOT requests and it's also important to understand the symbols/wild cards. But mostly that FM searches as "begins with" for every word in a field, in any order. So find "Jo Smith" would find:

                           

                          Smith, John

                          Joe Smithsons

                          Joan Ellen Smithy

                           

                          This is a good AND search in a single field, single request.

                           

                          beverly

                          • 10. Re: Scripting a Search for Records Using OR
                            beverly

                            Ray et al, in SQL it is perfectly legal (and preferred) to use parens to make the statement very clear (just as in FMP calcs):

                             

                            abc = ? AND ( def = ? OR ghi = ? )

                             

                            That can make all the difference in results!

                             

                            beverly

                            • 11. Re: Scripting a Search for Records Using OR
                              Extensitech

                              Thanks for catching that!

                               

                              We've fixed the link within the blog so you can download the demo file.

                               

                              Chris Cain

                              Extensitech