7 Replies Latest reply on Oct 31, 2008 2:08 PM by ralvy

    Summary Fields question

    ralvy

      Title

      Summary Fields question

      Post

      I come from a DOS relational database manager (DataPerfect) that has a field function called Keep A Total. It allows me to configure a Number field to add/subtract its value to/from a Number field found in a different table. For instance, a field called Charge, found in the Transactions Table, might update the value found in the Case Charges field found in the Cases Table for a client's case, as well as update the value found in the Account Charges field found in the Account Table for a given client (each client has a single Account with multiple Cases, and each Case has multiple Transactions). All these charges fields would be real Number fields, not Calculation fields, so their values would be stored in the database and could be used for indexing if needed. So no calculations of totals take place when displaying a record in the Accounts or Cases Tables because those values are updated and stored when records in the Transactions Table are created or edited.

       

      The closest thing I see in FP9 is using Summary fields in the Accounts and Cases Tables, which calculate totals from values found in the linked tables. But this has an inherent problem, as far I can tell. Won't that Summary field recalculate every time an Accounts record or a Cases record is displayed?

       

      I imagine I need to work with a script on the Charge field in the Transaction Table, where, on each edit or creation, this script looks to see if the saved value in that field changed, and if so, adds/subtracts the difference to/from the corresponding fields in the other two tables.

       

      I haven't looked at scripts yet. I just wanted to see if there was a more automated way to handle this, like I have been used to with DataPerfect.

        • 1. Re: Summary Fields question
          TSGal

          ralvy:

           

          Thank you for your post.

           

          You can use a calculation field to total information in another table.   When you are defining a calculation, use the Sum() or Count() function, and reference the field in the linked table.  That will allow you to get a summarized value from the related records.

           

          The summary field summarizes records in the current table.

           

          TSGal

          FileMaker, Inc. 

           

           

          • 2. Re: Summary Fields question
            ralvy
               Hmmm...this sounds like I would be placing that option on the field that holds the total (e.g., in the Accounts Table). So I would have to make sure the field that contains the Charge (e.g., in the Transactions Table) somehow triggers the field that holds the total (in the other table) to update its value. I imagine I do this with a script.
            • 3. Re: Summary Fields question
              TSGal

              ralvy:

               

              Thank you for the clarification.

               

              It sounds like you are trying to do two different things.  Or, do you want to take a value from a file, adjust it one file, and then change that value back?  Maybe a realistic example of what you are trying to accomplish might make it easier to understand.

               

              TSGal

              FileMaker, Inc. 

              • 4. Re: Summary Fields question
                ralvy
                  

                Thanks. Here's my example, outlined in the original post, but hopefully made more clear here. Three tables:

                 

                Accounts

                Cases

                Transactions

                 

                One-to-many relationships going down that above list. Each Account has many Cases. Each Case has many Transactions.

                 

                There's a Total Charges field in the Accounts table as well as the Cases table. There's a Charge field in the Transactions table.

                 

                I want any change in the Charge field in the Transactions table to change the value in the respective fields in the Accounts and Cases tables when the record in the Transaction table is saved. I don't want the change in the respective fields in the Accounts and Cases tables to take place when I display them. Rather, I want them to be updated when saving a record in the Transactions table.

                 

                So the Total Charges in the Accounts and Cases tables are not Calculation fields. They're Number fields who's values are stored in the database like any other Number or Text field (Phone, SSN, etc.).

                 

                This is very easy to do with DataPerfect. Tell the Charge field in the Transactions table what fields to carry a total to, and it's done. It does the rest.

                 

                Is that clear?

                • 5. Re: Summary Fields question
                  ralvy
                    

                  Reading the Help screens and browsing one of the example databases that came with the trial download, the alternative seems to be defining the Total Charges fields in Accounts and Cases tables as Calculation fields, using the Sum() function to get totals from related records in the Transactions table. But there can be hundreds or thousands of records in the Transactions table for each of many Accounts. My newbie understanding of the Calculation field is that it will recalculate every time I display a record that contains it, and also recalculate everytime I run a report that accesses it. This seems unnecessarily time consuming.

                   

                  That's why I wanted to see how to use Number fields for Total Charges in the Accounts and Cases tables, and have their values updated whenever a subrecord in the Transaction table is saved.

                  • 6. Re: Summary Fields question
                    TSGal

                    If you only want a value changed when a transaction is saved, then you need to have some way of letting the other tables know that the transaction is saved.  This can be done with a button that executes a script where it shows the transaction is saved and the value is then copied to the other tables and updated.

                     

                    For example, the script may include:

                     

                    Set Field [Status; "Saved"]

                    Set Variable [$amount; Amount field]

                    Go to Layout [layout that contains case information]

                    Set Field [Case Amount Field; Case Amount Field + $amount]

                    Go to Layout [layout that contains Account information]

                    Set Field [Account Amount Field; Account Amount Field + $amount]

                     

                    Granted, this is a simplistic script, but it should point you in the right direction.

                     

                    TSGal

                    FileMaker, Inc. 

                    • 7. Re: Summary Fields question
                      ralvy
                         Thanks, TSGal. This looks like a good start.