1 2 3 Previous Next 31 Replies Latest reply on Mar 10, 2016 10:24 AM by beverly

    Q: Using Calculation with Summary

    lcot17

      I am attempting to 'SUMIF' a table column (if it were excel - but it isn't).

       

      I want to create a calculation that SUMS ALL RECORDS THAT MATCH A CRITERIA


      Table to Calculate:

      TABLE NAME: TRANSACTIONS

      FromAmount
      Customer10
      Agent5
      Agent20
      Customer10
      Customer20
      Customer10

       

      What is the calculation formula to 'SUM' the TRANSACTIONS::Amount where the TRANSACTIONS::From = "Customer" ?

       

      Currently, in the Transactions table I have a summary field to 'SUM' the totals of the Amount column.

       

      In the table where I want the calculation to output, so far I have tried 'If', 'Sum', 'Case', 'GetSummary'.

       

      Can someone help??

        • 1. Re: Q: Using Calculation with Summary
          siplus

          you have at least 2 options:

           

          - go to your transactions layout, do a find with From = customer, grab the value of your summary field into a $$var and bring it to your context;

           

          - use ExecuteSQL( "SELECT SUM(Amount) FROM transactions WHERE \"FROM\" = ?"; "";""; transactions::customerID)

          • 2. Re: Q: Using Calculation with Summary
            lcot17

            Just so I understand both options:

            - go to your transactions layout, do a find with From = customer, grab the value of your summary field into a $$var and bring it to your context;

            As far I as know, I can only do this in the context of a script? How would I do this to output as a calculation / field?

             

             

            - use ExecuteSQL( "SELECT SUM(Amount) FROM transactions WHERE \"FROM\" = ?"; "";""; transactions::customerID)

            I'm not totally clued up with SQL Queries as of now. I'll take when you put and properly read up on it to try and understand the syntax and what exactly this does

            I believe it:

            • Selects the Amount column with the sum function
            • 'FROM' the transactions field
            • 'WHERE' is a 'criteria query'

            What I am not sure about, why \"FROM\" (what are the \ for?) - or is this just standard syntax for SQL?

            Why the ? or is this just to be replaced with the actually criteria I am searching for?

             

            Thank you

            • 3. Re: Q: Using Calculation with Summary
              coherentkris

              FROM is a reserve word in SQL so you have to explicitly tell the SQL engine to handle it as an argument for the WHERE clause. the \" is escape quoting the text to get the desired effect. If you didnt escape quote it then you would get a sql syntax error.

              This is needed because, according to the table, you have cols/fields named "From" and "Amount". As i said From is a reserved word.. i think Amount is also but not sure.

              The =? part tells the SQL engine to look for the missing argument for the = function at the end of the argument list.. in this case whatever value is in the field transactions::customerID at the time of execution.

              • 4. Re: Q: Using Calculation with Summary
                lcot17

                I understand!!

                So in theory, this is still valid syntax?

                 

                ExecuteSQL( "SELECT SUM(Amount) FROM transactions WHERE \"FROM\" = ?"; "";""; "Customer")


                If I just wanted to search for a specific "Customer" text string rather than I dynamic field?

                • 5. Re: Q: Using Calculation with Summary
                  lcot17

                  (The above worked ) - so thank you to both of you that helped me.

                   

                  Just one final question about the WHERE clause in the sql query. I am reading the FMP SQL syntax guide now but cannot seem to match up the query in the guide to this...

                   

                  Just like FMP guides explain things like: Sum ( field1 ; { field 2 ; field 3 ; etc } )

                  Can you explain the FROM clause in the above? (I understand the \" now though)

                   

                  \"FROM\" = ?"; "" ; "" ; "variable_to_search"

                   

                  Specifically, what are the empty "" for?

                  • 6. Re: Q: Using Calculation with Summary
                    siplus

                    the empty "";"" are specifiers for field and record separators. When they stay empty, the default values will be used (comma and paragraph). Example: "SELECT firstname, lastname FROM Clients";"";"" will give you John, Doe while  "SELECT firstname, lastname FROM Clients"; " ";"" will return John Doe.

                     

                    As usual, I advice to download and read Beverly's reference document from here.

                    • 7. Re: Q: Using Calculation with Summary
                      lcot17

                      Beverly's document is a good read. Thank you for showing me the link...

                       

                      I would just like to re-raise this question as I have another small query on it.

                       

                      At present - is this SQL query limiting its results to related records or is it just grabbing all records in a table. I believe the latter.

                       

                      How can I expand this query to just select related queries? I have tried a couple of things.. this is the latest that did not work:

                       

                      ExecuteSQL ( "

                      SELECT SUM(Amount)

                      FROM InvoiceFinanceLog

                      WHERE _invoiceIDfk = __invoiceIDpk

                      AND \"To\" = ?"; ""; "";

                      "Customer"

                      )


                      This is using the SUM function to sum the amount fields from the 'InvoiceFinanceLog' table where the To field = Customer. I added the WHERE/AND to try and match the invoice ID keys.


                      Thanks

                      • 9. Re: Q: Using Calculation with Summary
                        lcot17

                        Downloaded them.. thank you!

                        Very helpful looking down the query list and learning all of the functions...

                         

                        Could you possible direct me to which query list example I would be best studying to solve my question raised last night?

                         

                        Thank you beverly .. great resources!! Must have taken you some time!

                        • 10. Re: Q: Using Calculation with Summary
                          beverly

                          your field names should not start with "_", but if they do, you must escape-quote them:

                           

                          WHERE \"_invoiceIDfk\" = \"__invoiceIDpk\"

                           

                          but that may not be your only problem. how are your tables related (if any) in the RG (relationship graph)?

                           

                          While eSQL does not use the RG, you may need to make a JOIN in the query. And most of the time a query join is the same as would be in the RG relationship.

                           

                          NEI (not enough info),

                          beverly

                          • 11. Re: Q: Using Calculation with Summary
                            lcot17

                            That's a perfectly fair statement - I apologise.

                            Let me try and provide some more context for you...

                            Purpose: I have a 'Transaction Log' section in an invoice record. This is related to a 'InvoiceFinanceLog' table with a pk in the IFL table. For each invoice there are three involved parties where the money flows... the Customer > Agent > Company (me). I have a portal and form on the invoice to add a record or 'transaction in the IFL'. Here I select money From and To and define the amount and purpose of the transaction.

                            I then have calculation fields to calculate how much each party is owed/owe etc - enter the eSQL!

                             

                            Here is the relational graph section in question (SEE THE PURPLE):

                            Screen Shot 2016-03-09 at 12.50.28.png

                             

                            Collectively, I need a field for:

                            - Customer Balance (owe)

                            - Customer Paid (amount paid)

                            - Agent Balance (amount received)

                            - Agent Paid (amount paid)

                            - Company Received (amount received)

                             

                            Once I crack one - I can do all! I just am not sure how to make it all relational, or if my query was along the correct lines?

                             

                            I will also look towards doing it in your format of:

                            Let ( $query = " SELECT .... etc "

                            ExecuteSQL ( $query ; ... )

                             

                            Keeps it simple.

                             

                            As for the key field starting with _ (for fk) and __ (for pk) ... this was just a technique that I was taught through following various tutorials. If you have a better alternative suggestion, I am happy to hear.

                             

                            (Transaction log form on the invoice)

                            Screen Shot 2016-03-09 at 12.57.25.png

                            • 12. Re: Q: Using Calculation with Summary
                              beverly

                              As for the key field starting with _ (for fk) and __ (for pk) ... this was just a technique that I was taught through following various tutorials. If you have a better alternative suggestion, I am happy to hear.

                               

                              I understand. Many people started using the underscore for the start of names. However this breaks in other connections (especially external). The justification was to sort (alpha) these fields to the top of a field definition. And I understand that. However, I prefer the name of the field to be a name followed by _fk or _pk (just my preference), for the reasons of external connections.

                               

                              Even FM says this About naming fields:

                              https://www.filemaker.com/help/14/fmp/en/html/create_db.8.13.html#1081048

                               

                              I use this advice for all objects' names (not just fields) and in fact limit to alpha-numerics plus "_" (underscore) with an occasional space (" ") character. But never underscore, space or number at the beginning of anything named - these are bad for use with many other apps that might use the data, and as you see with ExecuteSQL().

                               

                              beverly

                              • 13. Re: Q: Using Calculation with Summary
                                lcot17

                                So if I rename all of my key fields to

                                keyname_pk & keyname_fk for example...

                                 

                                The following expression follows the correct syntax for SQL?

                                 

                                Let (

                                $query = "

                                SELECT Sum (Amount)

                                FROM InvoiceFinanceLog

                                WHERE invoiceID_fk = invoiceID_pk

                                  AND \"To\" = ? " ;

                                 

                                ExecuteSQL ( $query ; "" ; "" ; "Customer" )

                                 

                                )

                                 

                                At present it gives a lovely "?"

                                I am still not clear on how to only sum/show 'relational' records in this query. I know that it is the first WHERE statement because it is not explicitly showing where to look for the match fields. Advice?

                                 

                                Luke

                                • 14. Re: Q: Using Calculation with Summary
                                  lcot17

                                  Or..... reading about the JOIN functions how far off am I with this?

                                   

                                  Let (

                                   

                                  $query = "

                                  SELECT Sum (Amount)

                                  FROM InvoiceFinanceLog AS log JOIN Invoices AS invoice

                                  ON log.invoiceID_fk = invoice.invoiceID_pk

                                  WHERE \"To\" = ?

                                  " ;

                                   

                                  ExecuteSQL ( $query ; "" ; "" ; "Customer" )

                                   

                                  )

                                  1 2 3 Previous Next