1 2 Previous Next 18 Replies Latest reply on Dec 22, 2016 9:04 PM by beverly

    Two Tables and Multiple Fields for ExecuteSQL()

    RobertWard

      I am trying to get my Executesql() calculation to evaluate a count as part of a reporting structure of "active" records based on a request date and type of service from a portal. My parent table (Client_Layout) has my status record field, named STATUS. My portal, located on my parent layout, contains a Date and a category field named Services. Since my fields are in two different tables, I am unsuccessful in getting this to work. I have tried using the UNION clause to join the two tables and two Where clauses, but no success. I have tried using the HAVING clause but no success either. Here is how my calculation looks now: Somewhat strangely I have two Select statements. This was just an attempt at trying something else out.

       

      ExecuteSQL (

       

       

                                    "Select STATUS, Select Count(SERVICE)

                                     From CLIENT_LAYOUT UNION JAIL_PORTAL

                                     Where STATUS = 'Active'               

                                     Where SERVICE = 'Visit'  and \"DATE\" >= ? and \"DATE\" <= ? " ; "" ; "" ; $$YTDFrom ; $$DateTo )       

       

       

      This calculation does work when I don't include the second table and the first Where clause where status = 'active'. But I need to combine

      these two tables together where the status is equal to "active"

       

      Appreciate any help. Thanks.

        • 1. Re: Two Tables and Multiple Fields for ExecuteSQL()
          erolst

          RobertWard wrote:

          I am trying to get my Executesql() calculation to evaluate a count as part of a reporting structure of "active" records based on a request date and type of service from a portal. My parent table (Client_Layout) has my status record field, named STATUS. My portal, located on my parent layout, contains a Date and a category field named Services. Since my fields are in two different tables, I am unsuccessful in getting this to work.

          Are you saying that you only want to consider records in the second table that are related to the layout record? The simplest way to do that is usually to add the primary key to the WHERE clause (in effect replicating the FM relationship definition):

           

          ExecuteSQL ( "

            SELECT STATUS, COUNT(Service)

            FROM JAIL_PORTAL

           

            WHERE

              STATUS = ? AND

              SERVICE = ? AND

              \"nameOfForeignKeyField\" = ? AND

              \"DATE\" BETWEEN ? AND ?

           

            " ; "" ; "" ;

            "Active" ; "Visit" ; CLIENT_LAYOUT::primaryKeyField ; $$YTDFrom ; $$DateTo

          )

          1 of 1 people found this helpful
          • 2. Re: Two Tables and Multiple Fields for ExecuteSQL()
            beverly

            UNION is like multiple queries concatenated.

            You need JOIN which sets up the relationship. The connectors on your graph are NOT any help with ExecuteSQL (context agnostic).

             

            You need this (get the example files as well as the PDF)

             

             

            Sent from miPhone

            1 of 1 people found this helpful
            • 3. Re: Two Tables and Multiple Fields for ExecuteSQL()
              fmpdude

              Trying to get a query working in the "Data Viewer" is like pulling teeth with a jackhammer!

               

              Aside from no query assist, the brain-dead "?" result "messages", FMP's "Data Viewer" is simply  not productive for SQL development or really even "data viewing" in it's teeny tiny scroll-able area  (And no data "editing" in "Data Viewer").

               

              Until FMP implements an actual "Data Viewer" in some future version, I would recommend you check out "RazorSQL" which is a "real" SQL tool that will connect to your LIVE FileMaker database (and any other JDBC-enabled database ....basically ALL OF THEM). Razor will also copy/sync data from one database type to another (that useful features isn't supported in FileMaker, but that doesn't disqualify Razor as an excellent SQL tool for FMP).

               

              Using Razor, or a tool like it, you'll get live query assist, actual error messages, a nice large resizable area where you can edit/view your results.

               

              Then, once you get your query working, and a two-table join is simple to do with the right tool, move the query back to FMP and add your replaceable parameters and such.

               

              So, in summary, using a tool like Razor will:

               

              1. Reduce your FMP SQL creation frustration by A LOT

               

              2. Drastically increase working SQL creation productivity. (I usually save about 75% of the time using Razor)

               

              3. See actual error messages so you quickly see what's wrong. FileMaker is very picky about GROUP BY expressions as one example

               

              4. Have fully scroll-able windows where you can adjust everything.

               

              5. Export and other options are built right in.

               

              6. Have productive SQL and data work-space where you can work effectively.

               

              And ... Razor and tools like it are just fun to use. And you can try Razor for free for 30 days I think.

               

              I'm really not trying to sell you on Razor itself (I don't use it), but Razor or a tool like it will be like a breath of fresh air doing any SQL development in FMP. For myself, I use a Java IDE with a "data panel" that basically does the same thing as Razor, but not quite as nice as razor.

               

              HOPE THIS HELPS.

              • 4. Re: Two Tables and Multiple Fields for ExecuteSQL()
                David Moyer

                Hi,

                here's the FM SQL reference ...

                https://fmhelp.filemaker.com/docs/15/en/fm15_sql_reference.pdf

                Regarding joins, TO's and the relationship graph see "Using the ExecuteSQL function" in chapter 1.

                • 5. Re: Two Tables and Multiple Fields for ExecuteSQL()
                  beverly

                  Love the link to the SQL reference, however it gets a little blurred with all things SQL for FM. ExecuteSQL() doesn't always follow everything in that guide. Just a caveat!

                  beverly

                  • 6. Re: Two Tables and Multiple Fields for ExecuteSQL()
                    David Moyer

                    understood.  I was specifically pointing to these two paragraphs: 

                     

                    The FileMaker Pro ExecuteSQL function lets you retrieve data from table occurrences named in the relationships graph but independent of any defined relationships. You can retrieve data from multiple tables without creating table joins or any relationship between the tables. In some cases, you may be able to reduce the complexity of your relationships graph by using the ExecuteSQL function.

                     

                    The fields you query with the ExecuteSQL function do not have to be on any layout, so you can use the ExecuteSQL function to retrieve data independent of any layout context. Because of this context independence, using the ExecuteSQL function in scripts may improve the portability of the scripts. You can use the ExecuteSQL function anywhere you can specify calculations, including for charting and reporting.

                    • 7. Re: Two Tables and Multiple Fields for ExecuteSQL()
                      beverly

                      Thank you for the clarity!

                      beverly

                      • 8. Re: Two Tables and Multiple Fields for ExecuteSQL()
                        fmpdude

                        And, as I recall due to the fact that FM SQL cannot do SQL that doesn't reference a table in the Relationship Graph, so even something simple like getting the current date requires a hack (MUST HAVE A FROM CLAUSE when other SQL environments don't require that).

                         

                        (on my list.)

                        • 9. Re: Two Tables and Multiple Fields for ExecuteSQL()
                          beverly

                          Oh yeah! must have a table (table occurrence on the graph) with at least one record (no data needed!), as a query to an empty table also does not return results.

                           

                          beverly

                          • 10. Re: Two Tables and Multiple Fields for ExecuteSQL()
                            RobertWard

                            Thank you everyone for all of your suggestions! Ideally, I would be looking for something like this:

                            ExecuteSQL (

                                                          "Select Count(SERVICE)

                                                           From JAIL_PORTAL, CLIENT_LAYOUT

                                                           Where SERVICE = 'Intake' and STATUS = 'Active' and \"DATE\" >= ? and \"DATE\" <= ? " ; "" ; "" ; $$YTDFrom ; $$DateTo )

                             

                            This does give me a resulting count, but an incorrect one. I did resolve this by creating a second status field in my related table (Jail_Portal) to read the status field from my parent table (Client_Layout)  (e.g. case ( client_layout::status = "active" ; 1 ; 0 ). This brings my status field into the Jail_Portal table with my other fields so that it's all under a single table. A common practice I believe. And it works perfectly! A minor cost for adding a calculation field, and one a do not prefer to keep repeating since it should all be handled natively in FQL.

                            • 11. Re: Two Tables and Multiple Fields for ExecuteSQL()
                              beverly

                              correct, it should be handled by the FQL. you did not recreate your JOIN (relationship) in the query that you had on the graph. had you done that, you would not have needed to create the calculated field to bring the related data. that's one of the basics of using the ExecuteSQL() - the need to not create extraneous relationships or "tunnel-through" calculations!

                               

                              I have not seen the posts online and may have missed the information that a screen shot would provide.

                              Others who have answered should have provided the JOIN for you.

                               

                              beverly

                              • 12. Re: Two Tables and Multiple Fields for ExecuteSQL()
                                erolst

                                If you want to find records that have certain properties AND are related to the context record (and you're not referring to fields from a third table), you can simply add that relationship as an additional predicate to the WHERE clause, using your already existing primary and foreign key fields.

                                 

                                Please refer to my earlier post where this has been discussed - if you're asking for assistance, it's not helpful to ignore the one you receive.

                                 

                                RobertWard wrote:

                                This brings my status field into the Jail_Portal table with my other fields so that it's all under a single table. A common practice I believe.

                                It is not, since you can access and reference related fields as well as 'native' ones. (There are exceptions for certain summary and multi-hop sort requirements.)

                                 

                                Bringing everything "under a single table" very much defeats the purpose of having an RDBMS.

                                • 13. Re: Two Tables and Multiple Fields for ExecuteSQL()
                                  David Moyer

                                  the quote from the FM SQL guide in my post #6 suggests not needing "joins".  It's not required for a successful query.  But you need them if you want to look at data across relationships.  The rest of the quote is very informative - should be read over and over.  Here's a sample of creating a join within a single statement:

                                  ExecuteSQL(

                                    "SELECT Score FROM Scores

                                       JOIN Classes ON Scores.fk_ClassID = Classes.pk_ClassID

                                       WHERE Classes.SortOrder = ? AND Scores.fk_StudentID = ? AND Scores.TestDate = ?";

                                    "";

                                    "";

                                    TO::data1; TO::data2; TO::data3

                                  )

                                  • 14. Re: Two Tables and Multiple Fields for ExecuteSQL()
                                    erolst

                                    For a single query, I find a JOIN overly complicated; you need a JOIN for stuff like this:

                                     

                                    ExecuteSQL ( "

                                      SELECT T.type, COUNT(*)

                                      FROM Incidents I

                                      JOIN Types T ON Types.id = Incidents.id_type

                                     

                                      WHERE I.id_carrier = ?

                                     

                                      GROUP BY T.type

                                      ORDER BY T.type

                                     

                                      " ; "" ; "" ; Carrier::id

                                    )

                                     

                                    i.e. from the context of a first table, read (and summarize) data from a second table while resolving keys to pull in related data from a third (Made-up example, not tested ..)

                                    1 of 1 people found this helpful
                                    1 2 Previous Next