6 Replies Latest reply on Aug 10, 2013 6:47 AM by Padster

    ExecuteSQL - One to Many Concatenation

    Padster

      Hi All,

       

      I wonder whether anyone might be able to help me with a bit of a problem that I am having. I'm trying to achieve something, but to be honest, don't know if it is even possible within FM ExecuteSQL, based on whether to commands are actually there.

       

      For a basic Run down, I have three tables, Customer » Connections » Connection Variables. Customers can have multiple Connections and each Connection can have multiple variables, creating a One to Many » One to Many relationships.

       

      The first task, is to export each connection based on a set of criteria, active Connections per customer. This is simple enough with a nice simple ExecuteSQL select. I have no problem with this.

       

      CustomerIDConnection_ID
      A1
      A2
      B3
      B4
      C5
      D6

       

      The Second task is to identify the Variables related to a specific connection, with parameters, this again is nice and simple to get a list of variables; that comes out like this:-

       

      Connection_IDConnection_Var
      1ItemABC
      1ItemDEF
      1ItemGHI
      2ItemJKL
      2ItemMNO
      3ItemPQR
      4ItemSTU
      4ItemVWX
      5ItemYZA
      6ItemBCD

       

      The tricky bit, that I am trying to do, is have this returned a single table, without returning a row for each Connection_Var, but still returning a Row for each Connection. So the Output would look like this;

       

      CustomerIDConnection_IDConnection_Var's
      A1ItemABC, ItemDEF, ItemGHI
      A2ItemJKL, ItemMNO
      B3ItemPQR
      B4ItemSTU, ItemVWX
      C5ItemYZA
      D6ItemBCD

       

      Is this anything that anyone has attempted, and found a solution for, staying with ExecuteSQL? I have a couple of ways that I can resolve the problem by creating a field in the Connection table that hold the related foundset of Variables ready to be called upon by the SQL Select, but I would like to keep this tidily with the Select if possible.

       

      I have found a T-SQL that is designed to complete the same task, but I can't seem to get this to replicate into FM. http://stackoverflow.com/questions/194852/concatenate-many-rows-into-a-single-text-string

       

      Regards,

       

      Pad

        • 1. Re: ExecuteSQL - One to Many Concatenation
          greglane

          Hi Pad,

           

           

          I was experimenting with a technique the other day that would probably work, but it's difficult to code. The idea is to have an ExecuteSQL function that returns some quoted data along with one or more new ExecuteSQL function(s) in each row. The result is passed through the Evaluate function to give the final result. So, an expression like this:

           

           

          ExecuteSQL("SELECT '\"<tr><td>' || id1, id2, '\" & ExecuteSQL(\"SELECT description FROM table2 WHERE id2=' || id2 || '\",\"\",\",\") & \"</td></tr>\" &' FROM table1";"</td><td>";"") & "\"\""

           

           

          Returns this:

           

           

          "<tr><td>1</td><td>1</td><td>" & ExecuteSQL("SELECT description FROM table2 WHERE id2=1","",",") & "</td></tr>" &

          "<tr><td>1</td><td>2</td><td>" & ExecuteSQL("SELECT description FROM table2 WHERE id2=2","",",") & "</td></tr>" &

          "<tr><td>1</td><td>3</td><td>" & ExecuteSQL("SELECT description FROM table2 WHERE id2=3","",",") & "</td></tr>" &

          "<tr><td>2</td><td>2</td><td>" & ExecuteSQL("SELECT description FROM table2 WHERE id2=2","",",") & "</td></tr>" &

          "<tr><td>2</td><td>5</td><td>" & ExecuteSQL("SELECT description FROM table2 WHERE id2=5","",",") & "</td></tr>" &""

           

           

          and then this:

           

           

          Evaluate(ExecuteSQL("SELECT '\"<tr><td>' || id1, id2, '\" & ExecuteSQL(\"SELECT description FROM table2 WHERE id2=' || id2 || '\",\"\",\",\") & \"</td></tr>\" &' FROM table1";"</td><td>";"") & "\"\"")

           

           

          Returns this (formatted to be more readable):

           

           

          <tr>

                    <td>1</td>

                    <td>1</td>

                    <td>a,aa,aaa</td>

          </tr>

          <tr>

                    <td>1</td>

                    <td>2</td>

                    <td>b,bb,bbb</td>

          </tr>

          <tr>

                    <td>1</td>

                    <td>3</td>

                    <td>c</td>

          </tr>

          <tr>

                    <td>2</td>

                    <td>2</td>

                    <td>b,bb,bbb</td>

          </tr>

          <tr>

                    <td>2</td>

                    <td>5</td>

                    <td>e</td>

          </tr>

          • 2. Re: ExecuteSQL - One to Many Concatenation
            davidhamannmedia

            The problem is that you can't really do subselects with FileMaker's ExecuteSQL() – I already requested it as a feature and we all should :-).

             

            You could do it in two steps though. Get the first two columns with ExecuteSQL() (a regular JOIN) and then loop through the result (for each line) to attach the string of variable names.

            • 3. Re: ExecuteSQL - One to Many Concatenation
              ch0c0halic

              Pad,

               

              A couple of suggestions.

               

              For the field separator use ", ". Leave the record separator as "" (it defaults to return). This should give you the output you want.

               

              Add a Join between task 1 and task 2. where task1.Connection_ID = task2.Connection_ID.

               

              or

               

              add a GROUP BY CustomerID, Connection_ID

               

              One of these should get you want you want.

               

              We all need training on this exciting capability. I suggest you read up on SQL commands and options. Also, come to DevCon. There are 3 or 4 sessions about the FMP ExecuteSQL() command. Check out the various ExecuteSQL() web sites published by FMP developers.

              1 of 1 people found this helpful
              • 4. Re: ExecuteSQL - One to Many Concatenation
                steve_ssh

                Hi Pad and Greg,

                 

                To add an additional perspective on the technique Greg posted:

                 

                There was a while when I was experimenting a bunch of this sort of evaluation on the output of ExecuteSql, and I found it worthwhile to sometimes define a custom function which would perform the real work at the heart of the evaluation.

                 

                 

                 

                To illustrate, a variation on Greg's post that uses a CF would look as follows:

                 

                   Calculation That Returns String To Be Evaluated:

                 

                       ExecuteSQL( "SELECT 'FrmRow( ' || id1 || ', ' || id2 || ' ) &'  FROM table1"; ""; "" ) & Quote( "" )

                 

                 

                   Definition of Custom Function:  FrmRow( ID_01, ID_02 )

                 

                       "<tr><td>" & ID_01 & "</td><td>" & ID_02 & "</td><td>" &

                 

                       ExecuteSQL( "SELECT description FROM table2 WHERE id2= ?"; ""; ","; ID_02 ) &

                 

                       "</td></tr>"

                 

                 

                 

                Drawbacks:

                 

                  - It adds an additional layer of abstraction to the solution.

                 

                  - Extra overhead times the number of returned rows to invoke the CF (though, in practice, I never noticed this as a problem)

                 

                 

                Advantage:

                 

                  It can shorten the length of the ExecuteSQL output, which may allow you to squeeze in more rows before hitting the upper bound on the length of data that can be supplied as an argument to Evaluate.

                 

                 

                 

                Subjective Advantage:

                 

                  To my eyes, the factoring of the code into the CF allows me to read, debug, and tinker with the code more easily, as it avoids the level of detail attention required to carefully escape quotes properly when crafting the string that will be evaluated.  Note the easy-to-read quality of the sub-query in my example above.  This clarity becomes more apparent/important as the amount of complexity of the evaluation string increases, e.g. if the sub-SQL statement gets more complex, or the sub-query output requires special formatting, etc..

                 

                I put this under "subjective advantage", because I doubt everyone would agree with me on this; though we all strive for code-clarity, we all read code per our own set of eyes, experience, and brain-wiring.

                 

                 

                 

                 

                Sample File:

                 

                I'll attach a thrown-together sample file which, I believe, illustrates Greg's post, as well as a variation which utilizes a custom function per my description above.  It should be attached to this post.

                 

                 

                 

                 

                Finally:

                 

                In the sample, I did not build anything for robustness against field renaming.  Adding that extra (and, I believe, essential) step will make things more complex, and I can see a possibility where this will increase the complexity of the CF-based technique moreso than the non-CF version, though I think this would really depend on the methodology used for protecting against schema name changes.

                 

                Moreover, the code sample I've got assumes numeric values in the ID fields.  This can, of course, be modified to allow for string output, but, if you want to account for the possibility of the strings in the output containing quotes chars, then you've got more work in front of you to really lock things down well.

                 

                Hope you find this perspective helpful.

                 

                @ Greg.  Thank you for all the great stuff you post here.  Whenever I see your name on a post I know I'm about to learn something cool.

                 

                Kind regards,

                 

                -steve

                1 of 1 people found this helpful
                • 5. Re: ExecuteSQL - One to Many Concatenation
                  greglane

                  Thanks Steve, I really appreciate your comments. Your CF approach certainly makes things easier to follow. It just dawned on me earlier today that wrapping ExecuteSQL with Evaluate allows us to apply any FileMaker function or CF to single columns within the SQL result. That opens up a lot of cool possibilities that I want to explore.

                   

                  Greg

                  • 6. Re: ExecuteSQL - One to Many Concatenation
                    Padster

                    Hi All,

                     

                    Thank you for all the helpful advice, I'm going to work with a get GetFoundSet () CF in the middle table to start with, while I get my head around all of your suggestions.

                     

                    I'm not ha huge fan of XML formatted data, but I'm sure that I will soon overcome that, as it is a better way.

                     

                    Pad