1 2 Previous Next 15 Replies Latest reply on Sep 24, 2015 7:44 AM by beverly

    ExecuteSQL Multi-value comparison

    wsvp

      After spending an ENORMOUS amount of time trying to solve/research this issue, I am hoping someone here can either help or point me in the right direction.

       

      What I am basically looking to do, is establish a match in ExecuteSQL "EXACTLY" like we do with FileMaker's "=" Relationship operator, where ANY "SINGLE" value in Table A ( MAIN::zz_c_Format_SQL ) = ANY "SINGLE" value in Table B ( ACNT::zz_c_Format_SQL ) a result is returned, regardless of how many other values or the order of the values exist. Also it is an exact "value" match, but "not" a pattern match.

       

      While I am a beginner with SQL, I do understand that SQL does not work with carraige return delimited list values, as we do in FM, so I have created calculations to convert the lists to Comma delimited values, with each text value enclosed in single quotes. (thats what the fields named "zz_c_Format_SQL" do )

       

      Example...

       

      MAIN::zz_c_Format_SQL

       

      Value examples (any combination in any order could be selected):

      'Contact','Client','Vendor','Bank','Location','Active Vendor' ... etc

       

      ACNT::zz_c_Format_SQL

       

      Value examples (any combination in any order could be selected):

      'Contact','Client','Vendor','Bank','Location','Active Vendor' ... etc

       

      In a FileMaker Relationship using the "=" operator, "ANY" "value" match between the 2 Table/Field's will return a result.

       

      in ExecuteSQL I have not been able to achieve the same result.

       

      I have tried HUNDREDS of permutations using WHERE with LIKE, IN, =, using wildcards, also tried all different kinds of JOIN approaches. I have gone through hundreds of threads and references, and the most common thing that I have observed, are people recommending to NOT use fields with multiple values. I really hope this is not true, as it would make ExecuteSQL far less useful to me than I previously assumed it would be.

       

      ExecuteSQL ( "

      SELECT zz_c_Account_Name

      FROM ACNT

      WHERE

      ACNT.zz_c_Format_SQL IN ( ? )

      ORDER BY zz_c_Account_Name

      " ; "" ; "" ; MAIN::zz_c_Format_SQL )

       

      The above formula is one of hundreds that I have tried... it will only match if the ACNT::zz_c_Format_SQL field has exactly the same data as the MAIN::zz_c_Format_SQL. Even the order of the values has to be the same to get a result.

       

      Any help would be greatly appreciated.

        • 1. Re: ExecuteSQL Multi-value comparison
          erolst

          In fact, SQL (or: some SQL 'standards') features the seemingly rather esoteric ANY operator, which for a change is even supported in FMSQL, but this still doesn't do “EXACTLY” what you want …

           

          Two thoughts on this:

           

          1. Why not use a FileMaker multiline-key utility relationship in the first place?

           

          2. Or/And why not use a child table for CompanyCategory? (You will need it anyway as soon as you're asked to produce a “Companies by category” report and discover that a value list in a category field can't provide that …).

           

          Then you could again test for the presence of one value in a list, and write something like

           

          Let (

            myList = Substitute ( zzCategorySelector ; ¶ ; "','" ) ; // e.g. a checkbox-formatted field with selected categories

            ExecuteSQL ( "

              SELECT C.accountName

              FROM CompanyCategory CC

              JOIN Companies C ON CC.id_company = C.id

              WHERE

                CC.category IN ('" & myList & "')

              ORDER BY 1

              " ; "" ; ""

            )

          )

           

          Note the single quotes in assorted places, which are required for textual values.

          • 2. Re: ExecuteSQL Multi-value comparison
            wsvp

            1. Why not use a FileMaker multiline-key utility relationship in the first place?

             

            2. Or/And why not use a child table for CompanyCategory? (You will need it anyway as soon as you're asked to produce a “Companies by category” report and discover that a value list in a category field can't provide that …).

             

            Thanks erolst,

             

            Response to #1

            The reason I cannot use FileMaker's Relationship (in this case), is that this would require a specific relational architecture in place.  In this case the desired data structure is meant to be "completely" dynamic.  The Formula required to create the desired output will be "evaluated"..., and will be stored as "User" data.  In fact I may have no idea what the end user/support developer chooses to write for a specific query. In some cases it may not even be SQL.

             

            Response to #2

            In this case there is a a completely separate category structure, though it is generally not my preference to create a separate child table for a multi-value list, I do have a separate "Parent" Category Table, and calculation that concatenates the related categories at the items, in a way that reports can be grouped by a variety of categorical approaches.

             

            I do agree with you, that a separate table would make this one query approach a lot easier, however, this would be just one "dynamic" scenario among possibly thousands, and I do not want to create that much static infrastructure for this particular module.

             

            The example I listed was meant to be more of a simple way to ask the question.  At one point I had something very similar to the calculation you listed, I also looked at one you had posted in another thread, but it did not seem to solve the issue of the Multiple Values on both ends.  In addition The SQL "IN" operator does not seem to work as I expected, it seems to return different results, depending on the sequence of the values.  This is not like the FileMaker "=" Join.

             

            I am also trying to avoid some of the Multi-Nesting SELECT queries (a nested query for every value) that I have seen people post for similar (but not exactly the same situation) they look like a mess, they would require a recursive Custom Function, and I suspect they would perform horribly.

             

            Thanks again

             

            Mike

            • 3. Re: ExecuteSQL Multi-value comparison
              wimdecorte

              Perhaps you're trying to cram too much into one generic SQL statement.  You can just as easily generate a SQL statement dynamically based on the selected criteria.  The structure of a SQL query is easy to put together dynamically...

              • 4. Re: ExecuteSQL Multi-value comparison
                beverly

                Mike et al, the “multi-line” key that FMP ‘allows’ is certainly not ‘normal’ (in the CODD-sense <http://en.wikipedia.org/wiki/Codd's_12_rules>). And SQL db, while allowing multi-line (return separated) ‘lists’, does not have a way to “match” the way FMP does in the relationship graph.

                 

                Also, remember that simple finds in FMP use the default of “begins-with” so finding “ship” in a LIST of possible values: ship¶receive¶shipping¶… would find either the ship or shipping.  IF you need to search for a SINGLE value in a field, then you use “==ship“ (or exact match). If you need to find multiple values in a field, then you can use an “AND” search in the field (space delimited): “shipping receive” (order does not matter). If you need to find the exact order, then you use the words and the returns in the find criteria.

                 

                The relationship “find” or match would be exact and find only the “indexed value” of ship and not shipping. But this would return all records with the match, even if they have other return-delimited phrases in the field. That’s the ‘trick’ with the multi-line key.

                 

                The SQL “find” uses the WHERE clause with the comparison operators. “=“ is the Exact match and does not use the “begins-with” that FileMaker does. The LIKE and wildcards (%) for use with the WHERE is for loose searches, such as LIKE ‘ship%’ could find ship or shipping in my example. Because we have a “list”, the more accurate find would be LIKE ‘%ship%’ to find either ship or shipping (because of the return character before the shipping word). The WHERE clause used with IN, is for finding (OR search) any record that matches any of the values in the comma-delimited ‘list’. WHERE state IN (‘MI’, ‘CA’, ‘TX’)…

                 

                So, I don’t know if you need to use the ‘%value%’ (or other wildcards) in the SQL query to get what you want.

                 

                HTH explains a bit,

                Beverly

                • 5. Re: ExecuteSQL Multi-value comparison
                  wsvp

                  wimdecorte & Beverly, thanks for the help,

                   

                  While I have used a recursive, syntax, build structure for fixed items using HTML, CSS, SVG or JAVA etc..., in this case it would be very difficult to do.., While I am giving the user access, to a series of fields, to input calculation strings, (that will be evaluated,) I am not giving the user access to the Scripts or Database Schema.  Additionally, I will not have any idea what the end user decides to enter in those calcs (I will only be including examples and guidelines.)  I suspect that ExecuteSQL queries would be the most common, as they does not require relationships or context to return results.

                   

                  In many ways I am trying to avoid cramming a lot into a single SQL statement, I was hoping there was a "really" simple "Operator" that would work like FileMaker's.  As I am a complete amateur, when it comes to SQL, I only wanted to set up some simple examples, that could be selected or modified by the users. I wanted to leave the flexibility and power for someone with far more SQL knowledge than I, to better utilize the module.

                   

                  ................

                   

                  Beverly, the point you are making regarding the "Find" approach is exactly what I was concerned about.  It seems (at least to me) that SQL queries (both WHERE & JOIN) behave more like a FileMaker "Find Request" than FileMakers relationship structure.

                   

                  In this case the % wildcard does not work, because of the situation you stated.  If "Ship" was a value, I do "not" want it to be considered a match to "Shipping", as it is not an exact "value" match.  I did try using the LIKE with a few different wildcard approaches, without success.  The "IN" operator would probably function effectively on the request  side of the relationship (having multiple values). if the queried records "only" had "one" value per "queried" record/field, but it falls apart when the queried records have more than one value in a field.

                   

                  If all situations, where a multi-value field was present, had a corresponding table (and some due), I could use something like the JOIN approach erolst suggested (in some cases this can be done.)  However having a table for every situation, where multiple values in a field exist, would (in this case) result in hundreds of extra tables.  Perhaps this is standard operating procedure in the SQL world, but, FileMaker has handled multi-value fields very elegantly for decades.

                   

                  The point you make about the FileMaker Indexing, has me a bit curious.  Somehow FileMaker has accomplished (under the hood) what SQL may not have...  Establishing the match of "any" "exact value", on either side of the relationship, each of which has "multiple values."

                   

                  Perhaps this concept could be generated via a FileMaker or SQL calculation string.  The fact that the relationship performs well in FileMaker, would seem to indicate that they are "not" trying to re-calc one side based on the other.  Additional, if they "internally" used an approach like, running a recursive "=" find request on each value on one side of the relationship, this would make sense, but, I would have expected much more of a performance hit.

                   

                  Thanks for all the info.

                  • 6. Re: ExecuteSQL Multi-value comparison
                    beverly

                    Perhaps it’s a case of learning how FMP finds work with AND, OR, NOT (without actually using any values). Or finding out what values might be checked in a selection (in find mode) that can be translated to a SQL call.

                     

                    IF a value list (checkboxes) is:

                     

                          

                     

                    'Contact','Client','Vendor','Bank','Location','Active Vendor' … etc

                     

                    as you said in your first post.

                     

                    Then create a layout with the same fields (as globals) and the same formatting (a checkbox as needed). You have to convert the “selections” into SQL (if user checks ‘Contact’ AND ‘Vendor’, for example:

                     

                    WHERE field LIKE ‘%Contact%’

                    OR

                    field LIKE ‘%Vendor%’

                     

                    … would give you either match.

                     

                    WHERE field LIKE ‘%Contact%’

                    AND

                    field LIKE ‘%Vendor%’

                     

                    … would give you only those records where both match.

                     

                    In a find this is multiple requests for an OR search. For an “AND” search, I might look for ‘Contact’ and then Constrain the found set to look for ‘Vendor’. Or try the “Contact Vendor” search for “and”, but it will, too find records with any other value checked.

                     

                    You could even allow users to determine if they want all matches (OR) or only specific matches (AND).

                     

                    Can you explain more about what you expect the user to “find” (either by eSQl) or scripted find based on entries on global-field layout?

                     

                    Beverly

                    • 7. Re: ExecuteSQL Multi-value comparison
                      wsvp

                      Thanks Beverly,

                       

                      The "OR" approach you listed would probably work fine, in cases where the user was inputing a literal request, but would require some type of recursion to loop the "OR" requests in cases where the values queried will be in an existing field.  This would likely require some type of build structure to create the separate loops, along the lines of what wimdecorte was stating.  The hard part in this case, is that I have no idea "what" fields or tables will be looped, as everything in this module is pretty much defined by the end user.

                       

                      What I expect the user to find, is almost anything they want, from any table or field.  There is no real "Script" involved in this process, other than one script to set a Global field in a special Virtual List table, with the result of the calculation.  That VL Table has a series of user definable field and sub-field delimiters, which will place the data into a repeating field field array.  In addition there are also evaluation strings in every table that can be customized by the user.  The idea is to allow a completely customizable reporting or information structure that does not require ANY modifications to the Schema or Scripts.  Additionally all the formulas used to create the data are meant to be stored as user data... And "I know" most end users will not be writing the code themselves.  This would "likely" be written in by a qualified SQL or FileMaker developer.

                       

                      Even without ExecuteSQL, this approach allows an enormous flexibility in the data that can be retrieved, as well as how it can be displayed.  It also (and most importantly) allows the formulas to be save as data, just for that user.

                       

                      This module is just a tiny piece of a very large solution.  It is also just one of a few methods, that data will be retrieved and reported on.

                       

                      It is important for me to clarify, that this is not a product that is designed to be customized to a specific end user (that would be so much easier.)  All the development that I do, is typically called "Boxed Product" development.  Meaning it is designed to be sold "in mass", either wholesale or retail.  Thus there will "only" be "one" product for all users.  Thus I can never go into a users system and "put in" Schema, Layout changes or Scripting to accomplish what that user needs.  Instead what I have to do, is make sure they have a "method" to do what they need, without "any" changes to the schema, scripting or layouts.  This makes some of the simplest things a bit trickier.

                      • 8. Re: ExecuteSQL Multi-value comparison
                        wimdecorte

                        wsvp wrote:

                         

                        wimdecorte & Beverly, thanks for the help,

                         

                        While I have used a recursive, syntax, build structure for fixed items using HTML, CSS, SVG or JAVA etc..., in this case it would be very difficult to do..,

                         

                        I don't get that.  I've used that may times and I don't see an issue in your scenario.  Why would it be difficult here?

                         

                        You can give the users a FM interface to pick tables (TOs) and fields and WHERE criteria at will and still construct it as a viable FM SQL query.   I've shown this at devcon a few years ago, and it is the same principle that some of the SQL tools in the FM community are built on (see seedcode.com and modularfilemaker.org

                        • 9. Re: ExecuteSQL Multi-value comparison
                          wsvp

                          Thanks wimdecorte,

                           

                          I suspect that you are probably correct, but due to my very limited understanding of what SQL "can do" it is hard for me to creatively imagine the tools that will be needed.  I will be setting up a means for the user/programmer to get lists of the Tables and Fields.  I am sure this is something that will take a bit of time to grasp, as I am still trying to apply FileMaker logic to SQL.  I will see if I can track this down, this may give me a better idea of where you are coming from.

                          • 10. Re: ExecuteSQL Multi-value comparison
                            beverly

                            The Design Functions may also be of assistance for getting these kinds of objects (tables, fields).

                             

                            -- sent from myPhone --

                            Beverly Voth

                            --

                            • 11. Re: ExecuteSQL Multi-value comparison
                              wimdecorte

                              Beverly Voth wrote:

                               

                              The Design Functions may also be of assistance for getting these kinds of objects (tables, fields).

                               

                              -- sent from myPhone --

                              Beverly Voth

                              --

                               

                              Building on what Bev is saying,  in this year's Devcon preso I've shown how you can dynamically create SQL queries based on any layout the user is on.  The "RelationInfo" design function is a gem there...  it's been around forever and nobdy every really used it.... much.

                              • 12. Re: ExecuteSQL Multi-value comparison

                                You could populate virtual lists with the multivalue fields and its IDs and do the ExecuteSQL on them, see attached sample.

                                 

                                Otmar Kramis

                                • 13. Re: ExecuteSQL Multi-value comparison
                                  user19752

                                  SQL doesn't have word nor value concept in text.

                                  Using % in query cause disable indexed search, so I think here is not the place to use SQL if you don't want to change the data model.

                                  • 14. Re: ExecuteSQL Multi-value comparison
                                    thomasseidler

                                    totally agree with this, sql.in.clause by Farbice (FileMaker Custom Functions | Displaying Function sql.in.clause) does the job just fine for "StatusField" IN ("Complete","Incomplete") etc kind of thing, but as said above %Value% is a very borken way of doing things, cos as user19752 states it appears to disable indexed search and *hang*/fall over big time, if you are doing it on any decent sized data sets, unless you can considerably narrow down the set by compounding with other queries. So FM has a bit of a unique approach with that multi-line key thing! We like it, but SQL has nothing like it! I can't do anything like "%Blue%" IN ("FavouriteColorsMultiValueField"), which is a shame, but hey... Sorry if this is old, and if it's been fixed in F14 (i'm working in F12 currently) I will of course leap for joy and gladness at the prospect...

                                    1 2 Previous Next