1 2 3 Previous Next 32 Replies Latest reply on Jul 16, 2016 10:47 AM by wintertj

    Equivalent of SQL SELECT IN operator in FM search

    teodysvd

      Hello.

       

      May I know if there is there an equivalent of SQL SELECT IN

      operator in native Filemaker search.

       

      Hope you can help me on this.

       

      Thanks much.

      Teody

        • 1. Re: Equivalent of SQL SELECT IN operator in FM search
          nicolai

          In find mode use Requests/Add new requests and add new parameter. You can add as many as you want.

          • 2. Re: Equivalent of SQL SELECT IN operator in FM search
            Mike_Mitchell

            Hello, Teddy. SELECT IN is roughly analogous to Constrain Found Set. You can perform this function either manually on an existing found set, or via the equivalent script step (FileMaker Pro 15 Help).

            • 4. Re: Equivalent of SQL SELECT IN operator in FM search
              beverly

              Go to Related Record?

              beverly

              • 6. Re: Equivalent of SQL SELECT IN operator in FM search
                jameshea

                Bev,

                 

                Good answer. And depending if the relationship is already created. Constrain may work if the select set has the records within them. Multiple find requests is often overlooked as well. We might as well add 'filtered portal' which allows for various filtering criteria to be added to an existing relationship.

                 

                James

                • 7. Re: Equivalent of SQL SELECT IN operator in FM search
                  beverly

                  Well, I guess I was comparing IN to pushing a list of values (id's, or anything) to a global field for example, then using the relationship (already set up) and GTRR. Good old multiline key. And filtering. But Constrain is good, too.

                  beverly

                  • 8. Re: Equivalent of SQL SELECT IN operator in FM search
                    wintertj

                    IN in SQL is basically a fancy way of grouping multiple OR's together for a given field. OR conditions in general in FileMaker take a little getting used to when you come from the SQL world but once you get the hang of it it's no problem. When you enter find mode on a given layout, you can only search for one value in a given field per find request. In order to do an OR (or a series of OR's which is what IN is for in SQL) you have to build a new request via the "Request" menu, menu option "Add New Request" BEFORE you click perform find. Each time you click "Add New Request", you'll be presented with a fresh new empty find page to add subsequent OR conditions to the same field as the previous request, and you can even traverse through (to modify or delete or simply look at) them all using the regular record navigation buttons which, while in find mode, aren't navigating records per say, but rather the multiple find requests.

                     

                    You can use scripting to build multiple find requests and then perform the find, to hide this complexity from your users.

                     

                    As for AND's, that is FileMaker's default. Anything entered in a field on a given find request in which two or more fields are searched is an AND. So, if you wish to combine an AND and an IN/OR, you'll have to remember to add the AND field on all requests.

                     

                    Example in SQL:

                     

                    SELECT *

                      FROM pet_inventory

                    WHERE gender = 'Male'

                        AND species IN ('Cat', 'Dog', 'Hamster')

                     

                    Example in FileMaker:

                     

                    Go to the pet_inventory layout, enter Find Mode, and on the first request enter Male for gender and Cat for species.

                     

                    Then, Add New Request, you'll stay in find mode, and the fields will be emptied, on this request, enter Male for gender and Dog for species.

                     

                    Finally, add a third find request, on this request enter Male for gender and Hamster for species.

                     

                    Now that you've got your three find requests go ahead and perform find.

                    1 of 1 people found this helpful
                    • 9. Re: Equivalent of SQL SELECT IN operator in FM search
                      beverly

                      yes! it is an "OR" search. the list of values in a global is also an OR match with any of the records in the other side of the relationship.

                      • 10. Re: Equivalent of SQL SELECT IN operator in FM search
                        Mike_Mitchell

                        I tend to think of it more as, "I got a set of records here, and I want to narrow that set to include those that also match this other set." Hence the constrain. IOW, more of an AND.

                         

                        But whatever works to get the concept across.   

                        • 11. Re: Equivalent of SQL SELECT IN operator in FM search
                          beverly

                          if there are other criteria, then the "IN" could be AND (+ any of the values), so:

                          x AND y AND z AND IN (1,2,3,4,...)

                          x AND y AND z AND ( 1 OR 2 OR 3 OR 4 OR ... - values of IN )

                          because any of the values can be returned, not ALL of the values (of the comma-delimited list need be returned .

                           

                          beverly

                          • 12. Re: Equivalent of SQL SELECT IN operator in FM search
                            Mike_Mitchell

                            Right. I was thinking in terms of two sets of records intersecting, like a Venn diagram. The IN represents one set (which is internally an OR), and the surrounding SELECT represents the other.

                            • 13. Re: Equivalent of SQL SELECT IN operator in FM search
                              dburnham

                              Mike,  your reply is accurate, so I would like to ask if you agree with me that in the dialog that allows you to "build" a series of Find and Omit queries, you can't drag the sequence the way you can with sort order dialogs, and a variety of other dialogs in FileMaker.

                               

                              I've always worked on the belief that Omit steps should generally follow the Find requests, and that's the way I compose them.  But if there is a need to add another Find step, or edit the sequence, it can only be done by first deleting everything from the bottom-up, and then rebuild them.  It's a nuisance that would be overcome if we had the ability to slide the sequence of the requests with these things, whatever they are called:

                              Screen Shot 1.pngWhen I have suggested this before in discussions like this, I have been called "wrong" to use that dialog, and instead I should use Enter Find Mode, Set Field and then use New Request and Set Field for each additional one, then Constrain as needed.  It just makes scripts longer than necessary.   If it's wrong to use that dialog, it begs the question:  why was it given to us in FileMaker 7 ?  Or perhaps, why has it been impossible to improve it over the last eight versions ?

                               

                              Do you have an opinion?

                               

                               

                              BTW - I should add that it's remarkable that a feature that was huge in earlier versions of FileMaker -- even before the Claris days -- still survives and works today, which is this:  if you successfully perform the Find or Constrain manually, then the script step you insert immediately will "remember" whatever was the most recent query, in the correct request order.  The same works for Sorting and some other commands too.  In the '80's that was considered a cool, fast way to write scripts and it was a selling point for FileMaker especially for people who couldn't write scripts and before Set Field was given to us in FileMaker 6.

                              • 14. Re: Equivalent of SQL SELECT IN operator in FM search
                                wimdecorte

                                dburnham wrote:

                                 

                                When I have suggested this before in discussions like this, I have been called "wrong" to use that dialog, and instead I should use Enter Find Mode, Set Field and then use New Request and Set Field for each additional one, then Constrain as needed. It just makes scripts longer than necessary. If it's wrong to use that dialog, it begs the question: why was it given to us in FileMaker 7 ? Or perhaps, why has it been impossible to improve it over the last eight versions ?

                                 

                                 

                                Do you have an opinion?

                                 

                                The script length is irrelevant; there is no noticeable speed penalty for adding 5, 10, 20 lines of code to a script and the upside is that it is much more readable and easier to maintain.

                                (there was a thread a few years ago about the speed of script execution and the weight of comment script lines - look it up; it has some good numbers on the speed of FM scripts).

                                 

                                As to why the "restore" feature is there; who knows.  Why does it not get better: because it is not important enough to spend time on I guess.  The "enter find mode" is preferred by most.

                                1 2 3 Previous Next