12 Replies Latest reply on Jan 6, 2017 12:55 PM by fmpdude

    Basic SQL - aggregateing (sum) fields

    MaxEh

      Let me preface by asking to be excused for the long windedness.  (FMP 14A on a Mac 10.11)

       

      I am trying to implement some of my first SQL calculations. I have 3 tables Projects, Invoices and Budgets. Each Project is assigned to one Budget; each Project can have multiple Invoices, currently invoices are only assigned to one Budget, there are multiple Budgets.

       

      Budgets are assigned each year, when I started I was told there would only ever be 3 budgets - well we all know that story. Currently at 9. the Budget key is based on a year e.g 1516 or 1617 etc plus a Budget bucket for description sake A, B, C etc. so the key looks like 1516A or 1516B or 1617A etc..

       

      Invoices are added in Projects through a portal which sets both a fk_ProjectID and fk_Budget (fkBYT)

       

      What I need to do is track all invoices (aggregate sum) based on a Budget year bucket e.g. all 1516 Invoices in Budget bucket A or 1516A  to use in reports so Project coordinators/planners and financial coordinators can see how much is remaining in a given budget bucket as the year progresses.

       

      I have implemented something similar in my Invoices table using summary and getsummarry calculations but those are for relatively static needs ( originally 3 items currently 5 and this hasn't changed for over 10 years). The number and type of  Budget buckets will tend to change by year and by how the client wants data reported so I need something less onerous to implement.

       

      I thought I could do this in my Budgets table where I create one record for each year and bucket combination (that was the 3 and now 9   and that I could use ExecuteSQL to gather dynamically a total into a YTD field.

       

      This is what I have come up with but obviously I am not understanding the syntax (my forehead is considerably flatter since the holidays started).  I have read many of the references found in this form to help me get started but I am missing something (likely obvious). [I did create a successful Select Distinct query to trim an over bloated drop down list which was a timesaver]

       

      Basic relationships are:

       

      Budgets::pkBYT --< Inv|BYT::fkBYT (BYT is Budget Year Type 1516A or B or C etc)

       

      Budgets::pkBYT --< Proj|BYT::fkBYT

       

      Projects:pk_ProjectID --< Invoices::fk_ProjectID

       

      A Project ID is based on the year and if a second project is required a B, C, D etc is added to the ID and can then be assigned a different budget buck if required. Part of the project ID is a building number so an id would look like 1516-01A-1055 for the first project 1516-01B-1055 for a second project at the same building that could be assigned to the same or different budget. The same building could have another project 1516-35A-1055 which could also be assigned into the same or different budget bucket. The 01 is based on standard codes for the type of work the project entails. This system works very well. My problem is the budget allocation.

       

      The following is set up in my Budgets table in a calculation field, unstored. Obviously I am not understanding the syntax (my forehead is considerably flatter since the holidays started).  I have read many of the references found in this form to help me get started but I am missing something (as mentioned likely obvious). I did create a successful Select Distinct query to trim an over bloated drop down list which was a timesaver. (Power to the user, headache to the developer!!)

       

      ExecuteSQL (

       

      "SELECT SUM Inv|BYT::Invoice Amount

      FROM  Inv|BYT

      WHERE Inv|BYT::fkBYT = pkBYT"

       

      ; "" ; "")

       

      I have tried adding GROUP BY but all I end up with is the same "?", I have also tried using the Invoices table instead of Inv|BYT which if I understood from my reading should not matter (or is that where I am obviously going wrong?) Am I missing some "" or ; ...  I looked at the examples and saw use of the Let function - haven't gone down that path yet.

       

      Some of the Budget bucket names may have spaces in them e.g. 1516Some Bucket if that could be an issue.

       

      Am I on the right track or is there another way to do this? ( and I know with FMP there is always another way!!)

       

      Help!

       

      Max

        • 1. Re: Basic SQL - aggregateing (sum) fields
          user19752

          You need () for function, " for identifier containing special characters, and separator for table/field in SQL is dot.

          How about

           

          ExecuteSQL(

          "SELECT SUM(\"Invoice Amount\")

          FROM  \"Inv|BYT\"

          WHERE \"fkBYT\" = ?"

          ;"";""; pkBYT)

          2 of 2 people found this helpful
          • 2. Re: Basic SQL - aggregateing (sum) fields
            Johan Hedman

            Here are some useful links to learn Execute SQL in FileMaker

            FileMaker 12 - Learn about ExecuteSQL - Soliant Consulting

            1 of 1 people found this helpful
            • 3. Re: Basic SQL - aggregateing (sum) fields
              fmpdude

              There's a really good Lynda.com course on Filemaker database design, but it sounds like you're beyond that.


              To get SQL working quickly, I would recommend you use a SQL tool like RazorSQL, which will connect to your LIVE actual Filemaker database (which must be open) yet this tool will give you real SQL error messages, SQL content assist, nice huge edit windows where you can see and muck with your data, etc., and a lot more. And, it's relatively inexpensive. Free eval period. Once you get your SQL working, THEN move it back into FMP and add in parameters, if needed, to the last part of the ExecuteSQL statement.

               

              Missing a field in a GROUP BY expression is often a reason you'll get a "?. If you're like me, using a tool like Razor may really help reduce your frustration.

               

              Here's an example where I purposefully goofed a GROUP BY. RazorSQL gives me this actual error when I try to run the query against my zipcode FileMaker database:

              (This helpful error would be a "?" in the "Data Viewer")

               

              I use a similar tool to Razor when I do SQL in FMP. That tool helps me cut down my time and frustration quite a bit. Plus, with Razor you can see what you're doing better -- adjust fonts, sizes, windows sizes, placement, etc., so it's easier on the eyes.

               

              Here's a simple query (note if you browse the whole table, you can edit the fields right on the screen itself):

               

              There's even a RegEx search of the query results in the tool.

               

              Auto SQL field lookup AS YOU TYPE:

               

               

              Plus you can export to all kinds of formats right from your query on the toolbar.

               

              Highly configurable.

               

              HOPE THIS HELPS.

              • 4. Re: Basic SQL - aggregateing (sum) fields
                MaxEh

                Thank you User 19752.

                I think I was missing the ( ) and the use of .  I have also taken the time to get rid of the "|" in my T.O. names. Also pondering  the Invoice Total field name and debating whether to change that as well but knowing/remembering I need to delimit is likely OK too. I had played around with the delimiters but perhaps not long enough.

                 

                Johan - I will need to go back and see if the Soliant link was one that I reviewed. I know I looked at Beverly' however as Hercule Poirot tends to say perhaps I didn't use my grey cells enough.

                 

                fmpdude something to look into.  I'll start by working on the other resources first. I'm retired and on a budget that shrinks every year. Most of my work is done 'for fun' for not-for-profits - wages are in coffee and conversation

                • 5. Re: Basic SQL - aggregateing (sum) fields
                  fmpdude

                  Sounds good.

                   

                  If you find you do a lot of SQL, $95 for a perpetual license isn't very much compared to a typical FMP plug-in.

                   

                  Plus that particular tool (RazorSQL) will work with any database that supports JDBC (all of them) and with it you can even sync and copy data from one database to another should you need to.

                   

                  It's good to have options when and if you need them and Razor is by no means the only option to enhance SQL development in FMP.

                  • 6. Re: Basic SQL - aggregateing (sum) fields
                    user19752

                    SQL dot delimiter is used as

                    \"Inv|BYT\".\"Invoice Amount\"

                    (back slashes are for FM text literal syntax, not SQL itself)

                    I wrote this first, but removed table name before post it since there is only one table in the SQL.

                    1 of 1 people found this helpful
                    • 7. Re: Basic SQL - aggregateing (sum) fields
                      beverly

                      Table names are not required at all ( except in the FROM clause ), if all columns are uniquely named (including multiple tables)

                      table1.field1, table2.field2

                      can be

                      field1, field2

                      because they are uniquely named.

                      However,

                      t1.field1, t2.field1

                      needs the table name or alias because field1 is named the same in both tables.

                       

                      This is one of the reasons I avoid similar names when designing dbs (FM or SQL).

                       

                      Sent from miPhone

                      1 of 1 people found this helpful
                      • 8. Re: Basic SQL - aggregateing (sum) fields
                        siplus

                        ExecuteSQL("SELECT SUM(field)....

                         

                        is 10 times slower in my book than doing

                         

                        Let ([

                         

                        sql = ExecuteSQL("SELECT field....

                         

                        evalthis = Substitute(sql; ¶; "+") ];

                         

                        Evaluate(evalthis)

                        )

                         

                        which is 10 times slower than fetching the recordID's with ExecuteSQL, feeding them to a global and getting a summary field in the source table through a relationship based upon global = ID in the related tbl.

                         

                        YMMV.

                        1 of 1 people found this helpful
                        • 9. Re: Basic SQL - aggregateing (sum) fields
                          MaxEh

                          Siplus

                          Timely (pun intended) message. For the past few of days I have been playing around with \" delimiters  . table field separators etc just trying to sink the syntax into my head so that I can just type it in. I have a second field that I use to try out various variations to see what happens.

                           

                          I originally had the field as a calculation but that was so slow that I thought I was doing something wrong. After some thought and this form I changed it to a calculated text field (do not change is unchecked) - much faster!! and livable in my solution for now (client only!) as I only need it on one report layout. This will be running on a server in the near future.

                           

                          I will now add your suggestion to the mix and try and grok that a bit better. I think the Let statement might be easiest for me to understand and implement and then work on the relationship side of it.

                           

                          Thanks again for the suggestions - and to all the others who replied as well. I really appreciate having a form with regular participants with the skills to answer the questions as convoluted as they are sometimes.

                          • 10. Re: Basic SQL - aggregateing (sum) fields
                            fmpdude

                            I think you meant 1/10th as fast since you can't be any slower than 1x (100%) of the main value.

                             

                            ----

                             

                            In any case, I did a quick test and my results don't jive with my understanding of your post. It's possible I didn't understand your posting and so my posting here is just to better understand how FMP works not to argue with your posting.

                             

                            (I'm trying to learn from the experts here.)

                             

                            ------

                             

                            Here was my test.

                             

                            I have a simple 500,000 record table with randomly-generated names (same data in FMP and MySQL).

                             

                            Using MySQL as the baseline, I did this query:  Select * from names where "name" LIKE '%lr%'

                            MySQL was 0.26 seconds each time I ran this query.

                             

                            --------------

                             

                            FMP using ExecuteSQL directly (same query):

                            =======================

                            7 seconds initially

                            3.5 each time seconds thereafter

                             

                            ----------------------------------------

                             

                            FMP using LET (same query):

                            Let ([

                            sql = "Select * from TheNames where FullName LIKE '%lr%'" ;

                            result = ExecuteSQL(sql ; "" ; "")

                            ];

                            result

                            )

                             

                            3.5 seconds each time.

                             

                            ===================

                             

                            Again, I probably missed your point entirely (perhaps you were mainly discussing how to optimally assemble a query, then execute it?) but it was interesting since I've been trying to figure out how to get better SQL performance out of FMP.

                             

                            I also fiddled with the eval but since I wasn't substituting anything, and it was super tough to get it working with all the \" needed (all the "?" in the meantime), I just tried the basic LET query above but from my testing, it's really the same so I'm missing your point, clearly.

                             

                            I'm keeping this test solution handy for possible SQL improvements from any replies on where I might have misunderstood your posting's intent above.

                             

                            Thanks in advance.

                             

                            ------

                             

                            (I've attached my "names" DB - randomly generated using "Randommite" data generator, as CSV, should you like to compare or verify my results.)

                            1 of 1 people found this helpful
                            • 11. Re: Basic SQL - aggregateing (sum) fields
                              siplus

                              Yeah you probably missed my post entirely because the key word was AGGREGATING. Like in SUM().

                               

                              And yes, walking to my office IS 10 times slower than going there by train, which IS 3 times slower than going there by car. So Walking there is 30 times slower than going there by car. You just need to read to the end, establish what the unit is, then read backwards to see the %.

                              • 12. Re: Basic SQL - aggregateing (sum) fields
                                fmpdude

                                I guess that's one way of looking at it.


                                Expressions like 30x slower used to drive my math professors nuts (well, one of them, anyway) in grad school and I'm sure back in the day they would love to argue the point with you. In any case, I understood what you mean.

                                 

                                Enjoy.