8 Replies Latest reply on Nov 24, 2014 9:37 PM by dwelam

    Get sum from another table using SQL

    wfgclapp

      Trying to hack my way into SQL...

       

      I have two tables: Customers and Salesmen.

       

      Each Customer record has a number field called Cases and a field called sales_id

       

      sales_id is also the unique id associated with records in Salesmen.

       

      I need a Calculation field in Salesmen that is the sum of Cases for that salesman's customers.

       

      I.e. I need to total the Cases field in Customers, but only for customer records that match the sales_id in Salesmen.

       

      My Calculation field in Salesmen is called Tot_Cases and here's my calculation:

       

      Tot_Cases=ExecuteSQL("SELECT SUM(Cases) FROM Customers WHERE sales_id=?" ; " " ; " " ; Salesmen:sales_id)

       

      Pretty sure my hangup is not understanding the pieces of the WHERE statement. I've looked at several examples and tried various iterations but no joy.

       

      Can anyone spot what I'm doing wrong?

       

      THanks so much!

        • 1. Re: Get sum from another table using SQL
          ariley

          Use the custom function named sql.debug, it will help you understand what the problem is.

           

          Case is a reserved word in SQL, so you might want to put it in single quotes or use the question mark and name your table later.

           

          But you know you can actually just FileMaker to do this, right?

           

           

          Best regards,

           

          agnes b. riley

          filemaker and web development

          zeroblue

          download vCard

           

          TWO-TIME MAD DOG AWARD WINNER

          FileMaker Certified in 10 and 11 • Member, FileMaker Business Alliance

          T: 877 917-9079 . C: 917-660-7221

          Store | Blog | Facebook | Twitter | LinkedIn

          • 2. Re: Get sum from another table using SQL
            wfgclapp

            Hi Agnes,

             

            Not sure how to use sql.debug but I'm sure i can figure it out.

             

            And yes, I saw some examples of how Filemaker can do this, but the SQL 'seemed' simpler and I was interested in playing with it anyway. I'm very new to FM and never done any SQL.

            • 3. Re: Get sum from another table using SQL
              ariley

              Just Google it and you shall find it.

               

              If you’re new to FileMaker, learn to use that tool first, before getting into another language, but that’s just my two cents.

               

              I don’t know what cases refer to in your database, but make sure you learn the database basics. I would recommend the Training Series or the FM Bible to get started.

               

               

              Best regards,

               

              agnes b. riley

              filemaker and web development

              zeroblue

              download vCard

               

              TWO-TIME MAD DOG AWARD WINNER

              FileMaker Certified in 10 and 11 • Member, FileMaker Business Alliance

              T: 877 917-9079 . C: 917-660-7221

              Store | Blog | Facebook | Twitter | LinkedIn

              • 4. Re: Get sum from another table using SQL
                wfgclapp

                Uh, thanks for the reply I guess. Done all the above, but working on a lot at once. Wasn't looking for advice, just seeing if there was a simple answer. Clearly not.

                • 5. Re: Get sum from another table using SQL
                  Mike_Mitchell

                  Wasn't looking for advice, just seeing if there was a simple answer. Clearly not.

                   

                  In order to identify what you're doing wrong, it would be necessary to know what your result currently is versus what your expected result should be. Your question is not specific enough to give you a simple answer.

                   

                  Agnes has already made one suggestion - including the field name in quotes to escape any possible reserved word issue. This would solve a problem where you get a "?" result.

                   

                  Another possibility is that your calculation, in order to evaluate properly, needs to be unstored. Otherwise, it will evaluate once (when you close the Manage Database dialog) and store that result at that time, rendering any re-evaluation incorrect. This would be the case where your result returns a number, but does not update when you change the data in the related table.

                   

                  Other possibilities also exist. The correct answer will depend on what behavior you're seeing.

                  • 6. Re: Get sum from another table using SQL
                    wfgclapp

                    I should have been a little more specific, granted. Was hoping I had a syntax error or something easily spotted. Otherwise, I certainly don't mind (and get more out of) exploring on my own.

                     

                    Turns out the problem was indeed syntax but no one would have ever caught it from the code i gave. This is great... In order to be more clear I said one of my variables was sales_id  but in reality there is no underscore in my actual field name. I found out that spaces don't work well!!

                     

                    Arrggh.

                     

                    The sql.debug function is great! I've very appreciative to have run across it. Works very well.

                     

                    THanks to you both.

                    • 8. Re: Get sum from another table using SQL
                      dwelam

                      Try taking the spaces out from between the quotation marks.