13 Replies Latest reply on Mar 7, 2013 10:17 AM by philmodjunk

    Calculating Totals Between Related Tables

    wrobinson

      Title

      Calculating Totals Between Related Tables

      Post

           Hi everyone,

           I'm trying to use the 'aggregate' function to add values from related tables, but the calculation does not seem to work. The values I am trying to add come from three separate tables, and this is what the calculation looks like from the Current Table...

           EveCredSum = Sum ( Evening_Checklists::EveningCredTotal ; Morning_Checklists::MorningCredTotal ; Afternoon_Checklists::AfternoonCredTotal )

           Also, is there a way that, when one record is created it can be automatically copied into another table? Without using the portals?

           Best,

           Winslow

        • 1. Re: Calculating Totals Between Related Tables
          philmodjunk

               Assuming that each of these "CheckLists" relationships link to multiple records en the "total" fields are number or calculation fields, use:

               Sum ( Evening_Checklists::EveningCredTotal ) + Sum ( Morning_Checklists::MorningCredTotal ) + Sum ( Afternoon_Checklists::AfternoonCredTotal )

          • 2. Re: Calculating Totals Between Related Tables
            wrobinson

                 Hi PhilModJunk,

                 Unfortunately, that did not work. See the attached photo for how I am doing the relationships. Maybe I need to rework their linkages? They show up as related tables when setting up the calculation, but then the calculation doesn't work (no data appears).

                 As a bit of background, each of the Credit Checklist Scores are totaled using a Summary function in their respective tables (Evening_Checklists, etc) - should these be a straight calculation instead? Can Filemaker perform calculations on Summary fields? Ultimately I want graphs of all the data that update live as the scores evolve. 

                 In sum, I want a way to collect the data from each category (Evening, Morning and Afternoon), in one table/one graph.

                  

            • 3. Re: Calculating Totals Between Related Tables
              philmodjunk

                   If EveningCredTotal, MorningCredTotal ; and AfternoonCredTotal are summary fields, you do not need the sum function, but your original expression should have worked. It's the equivalent of

                   Evening_Checklists::EveningCredTotal + Morning_Checklists::MorningCredTotal + Afternoon_Checklists::AfternoonCredTotal

                   But these cannot be summary fields as you have used them as match fields in your relationship and this is not posible for summary fields.

                   Thus I am not sure what you mean when you say "each of the Credit Checklist Scores are totaled using a Summary function in their respective tables"

                   I can't see all the details of your relationships in the screen shot, but what I see does not look correct. I predict that if you put these three fields on your DataHome layout, they will be empty.

                   In what table did you define EveCreditSum?

              • 4. Re: Calculating Totals Between Related Tables
                wrobinson

                     I was able to set up additional tables which equaled the same totals in the summary columns, and then add these together using a calculation on another table - works great!

                     I'm wondering now though how I can display this data in a more specific way, maybe using portals and charts. The challenge is to be able to display data for one of nearly 20 groups (maybe byeach group having its own table), whereby when a checklist is completed in either Evening, Morning or Afternoon tables, the chart updates with the current data related that group as defined within the Evening, Morning or Afternoon tables. I tried the Lookup function and want to sort by 'Group Name', but am unsure what the calculation should look like to have the data available on each group's page (table).

                     Can I sort by the field 'Group Name' and only this record associated with Group X update a chartportal automatically? If so, please help show me how!

                • 5. Re: Calculating Totals Between Related Tables
                  philmodjunk

                       There is not enough detail in what you have posted to suggest an answer.

                       In particular when you speak of giving each group "its own table", I have to wonder if these are truly separate tables defined in Manage | Database | Tables or separate Tutorial: What are Table Occurrences? defined in Manage | Database | Relationships.

                       If I understand your descriptions here, I would not use separate tables, but I might use separate table occurrences. I've already noted that something in your original description of the problem and the relationships you've defined that doesn't quite add up--more explanation is needed just on that point and now you have changed that design to further make this unclear.

                  • 6. Re: Calculating Totals Between Related Tables
                    wrobinson

                         Great feedback - here's a simplification. I created summary fields that total checklist scores from other fields. Then I created caculation fields equal to these so that I could then add them on another table (thanks to your previous post, I found I could not do this with summary fields).  This photo should help illustrate. While I'm sure there is a more streamlined way of achieving this, this solution works (although I'm open to others). 

                         To be explicit about my current challenge: each checklist has one of many groups that checklists could be entered for, in three possible tables (Evening, Morning and Afternoon - each as a separate table defined in Manage Database | Tables). I would like to create a layout for each group that displays the data associated with that specific group. 

                         For example, if Group A completes 3 checklists in the Evening, 3 in the Morning, and 3 in the Afternoon, I would like a chart that automatically updates, displaying the data for just Group A (column charts summarizing all 9 checklist scores). Of course, I would also need the same charts for Groups B, C, D, etc...

                         I like the idea of various table occurances rather than separate tables. Is this how can I create charts by unique value within a field? Precisely: how can I create a chart by a specific group's name (record), knowing that this data comes from separately defined tables?

                    • 7. Re: Calculating Totals Between Related Tables
                      philmodjunk

                           What's missing here are the current, defined Relationships between the tables involved. I need that info also. I am assuming that this has changed since your original post. If not, let me know and I'll review the original screen shot of the relationships.

                           Keep in mind that I only know what you tell me. After that I have to start guessing...

                      • 8. Re: Calculating Totals Between Related Tables
                        wrobinson

                             For sure - here is my latest attempt. 

                             The source table for the yellow tables is GroupA. The pink (Evening), orange (Morning) and green (Afternoon) tables have their own self-named source tables.

                             Looking foward to your thoughts.

                        • 9. Re: Calculating Totals Between Related Tables
                          philmodjunk

                               First comment is that I truly do not see a need for separate occurrences for the three types of check lists, let alone separate tables for each. That forces you to do everything in triplicate when this probably is not at all necessary to get the results that you need. It also makes pulling this data together all in one layout more complex than is necessary.

                               Instead, a single table of checklists with a field that identifies whether or not that specific record is "evening", "morning" or "afternoon" makes it possible to work with groups of records that are restricted to that one type of check list record.

                               But I still have questions about what you currently have in place nere.

                               You posted a list of field definitions earlier. In what table are those fields defined?

                          • 10. Re: Calculating Totals Between Related Tables
                            wrobinson

                                 An excellent point on tripling the work - clearly, this is my first go-round. I'll hone it down to one table for all three, and have a value list where the user can select Evening/Morning/Afternooon. How then would I have different charts for Evening/Morning/Afternooon? Different table occurances?

                                 To answer your question, the field definitions are defined in the Evening_Checklists table (as are duplicate fields for Morning and Afternoon).

                                  

                            • 11. Re: Calculating Totals Between Related Tables
                              philmodjunk

                                   Since a chart can be based on a found set, you may be able to use one chart with different found sets.

                              • 12. Re: Calculating Totals Between Related Tables
                                wrobinson

                                     How do I set this up? Using a caculation within the chart engine?

                                • 13. Re: Calculating Totals Between Related Tables
                                  philmodjunk

                                       Much depends on the type of chart you need to produce. Since you haven't described the data you want to chart nor the type of chart, I can't really say.

                                       You have data source options that can specify values in a field separated by returns, a set of related records such as you might list in a portal, or the found set of records currently present. The last option also allows you to use summary fields and record sorting to plot one point on your chart for each group of records with the y value determined by the value computed in the summary field.