12 Replies Latest reply on Sep 30, 2012 9:30 AM by schamblee

    Calculation to sum a sub-set of records in related table

    NithyaLakshmanan

      Title

      Calculation to sum a sub-set of records in related table

      Post

           Hello,

           I am a Filemaker Newbie. I have 2 tables: Donors and Donations. They are related by a join on Name and Address fields. And it is a 1:many relationship from Donors:Donations.

           Now I need to display on the donation entry form a field that summarizes the amount for a specific SchoolYear:

           CurrentYearTotal = sum(Donations Amount) where SchoolYear = "2012-2013". 

           How do I define this calculation field? I was able to define a calculation field on the Donors table = Sum(Donations::Amount). But how do I add the filter/where condition to only sum over records only for the current year. Thanks.

        • 1. Re: Calculation to sum a sub-set of records in related table
          schamblee

               Here is a sample app that may help you out

                

          https://www.sugarsync.com/pf/D9559058_790_729182087

          • 2. Re: Calculation to sum a sub-set of records in related table
            schamblee

                 I made a small update to the sample.  Also I forgot to mention that you should use a serialize idkey to link records instead of names and address.  Names and address can change, which will create problem down the road.  

                 Good Luck.

                  

            • 3. Re: Calculation to sum a sub-set of records in related table
              NithyaLakshmanan

                   Thanks. I checked out your sample. While I understand how you have calculated the summary in the Donations layout, I would really like to see the summary for the current school year in the DonorSample layout where the donations are being entered for each donor.  Could you suggest a way in which I can achieve this?

                   I would also like to keep the "current school year total" as a Calculated field on the Donor table as I would need to provide additional analytic reports on this field. Forr e.g. each Donor could be associated to a School so I can then get the split of "Current Year Donations" by School, and also Donor gets assigned to a level based on how much they have donated in that school year so this would be another calculated field running off a sub-set of records from Donations table. 

                    

              • 4. Re: Calculation to sum a sub-set of records in related table
                schamblee

                     I updated the sample app with all the features you mention above.  You select the current school year and school at the top of the layout.  You can select All schools. Use script triggers to switch between two layouts : All or the school.  The schools can be edit from the portal when you select the school there is a option to edit.  The levels can be changed also.  I setup a calculation / merge field for the donor levels.  Just change the calculation. The figures are all based on different table occurance. 

                      

                https://www.sugarsync.com/pf/D9559058_790_729182087

                • 5. Re: Calculation to sum a sub-set of records in related table
                  NithyaLakshmanan

                       Thanks.. Ok so what you have done is created a copy of the donations table and setup a relationship to filter only on the current school year, where the current school year is a global value and set via a pop-up fields on the donation entry form. This seems to be a pretty lengthy setup for a simple summary calculation. And if  I need to get summaries per school year and total for each school year displayed on the form, I have to create a copy of donations table for each year? Is this the only way to set this up in Filemaker?

                       I tried to replicate what you have done in my DB. I am using Filemaker Pro 11. But it does not seem to be working. I am using a list of values for the pop-up menu on the CurrentSchoolYear field (CurrentSchoolYear is a global field on the parent table) and expect the Total Amount to be updated to reflect the total for that year. But the total field is blank. Any idea on what I could be doing wrong?

                  • 6. Re: Calculation to sum a sub-set of records in related table
                    philmodjunk

                         You shouldn't need to copy the actual table, but just copy the reference to it found in Manage | Database | Relationships. Each box here is called a "table occurrence" and is a reference or pointer to a specific table. When you duplicate one of these using the button with two green plus signs, you only duplicate the reference to the table, not the actual table. This is how you use FileMaker to create multiple relationships between the same two tables.

                    • 7. Re: Calculation to sum a sub-set of records in related table
                      schamblee

                           When you consider all the different amounts being tracked it not really that bad of a setup.   You will get a good understanding of relationships. smiley
                           There is always more than one way to do things, with everything that you stated you wanted to track in your later post, no matter the way you go it still going to be lengthy. 

                           I started in the first example using a global field in a global table.  This lead to a problem with relationship because I didn't have a relation to the global table so I just moved to the parent table, which there was no reason for the field to be global. (Relationship in parent table)
                           No you don't have to create a copy of the donations table for each year and school.  This is the reason for two popup at the top of the sample app.

                           Each Total is based on the same summary field.  What changes the amounts is the table occurances.

                           Is your Total Amount field based on the correct table occurance.  The Total Amount field should be a summary field. Total of the amount paid. 

                           From Example:  The total in the header is based on the school year and the school.  (Can be individual or all schools) 
                           The school can be changed to a school name. I used gerenic names.  The table occurance is Donations By School_School year.

                           Under the portal is the amount paid by donor for current year.  You have to track this if you want to assign the donors to different levels.

                           I didn't create a sample report with all the individual schools but it shouldn't be to hard to create. The sample does track individual amount based on the popup selection. 

                           Another reason I went ahead and created a sample app because it hard to explain where someone  else can understand what going on.  Example always helps me to learn.


                            

                            

                              

                      • 8. Re: Calculation to sum a sub-set of records in related table
                        schamblee

                             Here is another free template for donations.  I haven't really looked at it but I understand it a good app. 

                        http://www.filemakerdonations.com/

                        • 9. Re: Calculation to sum a sub-set of records in related table
                          NithyaLakshmanan

                               S Chamblee  - Thanks. I was able to set it up the way you have. I appreciate your taking the time to create the app else it would have taken me quite some time to get this done.

                               So just to confirm - if I need to show a "YearbyYear donation history" on the Donor form i.e. show the Total per School Year, I  create a global for each school year on the Donor table, create a relationship to join using that year and with a separate donation table occurrence for each: "Donations for 2012-2013" "Donations for 2011-2012"  and so on - is that right? Thanks.

                          • 10. Re: Calculation to sum a sub-set of records in related table
                            schamblee

                                 You don't have to have a donation table occurrence for each school year.   Your donation table will have the school year the donations were paid. The donor table has the school year you select from your popup menu, so this field will change to whatever school year you select.

                                  You select the current school year from the top popup.  Means the current year that the totals you are viewing on screen.

                                   donorsample::Current School Years =  the select school year to view

                                  donations::schoolyear = The school year the donation was paid.

                                   The relationship is donorsample::Current School Year -< donations::schoolyear.

                                   You would have one table occurance -- I named the occurance in my sample app :  donations by school year

                                 If you add other options such as by school for Current school year then you would have another table occurance

                                 Occurance Name from sample app : Dontations By School_Current school year.

                                 This 2nd Occurance would have the above relationship plus relationship donorsample::school -< donations::school

                                 donorsample::school is the popup of the school that you want to view.

                                 donation::school is the school the donation was paid too and you select from the drop down list in the sample.

                                  

                                  

                                  

                                  

                            • 11. Re: Calculation to sum a sub-set of records in related table
                              NithyaLakshmanan

                                   Hello, I was not clear in framing my question earlier.

                                   I have to show the totals for each year side-by-side in separate fields so user can see the history. Having them select the year from the pop-up would only display one total at a time. The school year is not something the user would explicitly select. I have built the School Year global field in my donor table as a calculation that returns a fixed school year i.e. 2012-2013 for current school year.  As an example, the form would show all of these fields for each donor:

                                   Current Year (2012-2013) Total ______          2011-2012 Total ______         2010-2011 Total ______

                                   Since they would be all displayed simultaneously,  the current approach would require me to create a Donations table occurrence per year.

                                   Thanks.

                                    

                              • 12. Re: Calculation to sum a sub-set of records in related table
                                schamblee

                                     I didn't understand that you wanted to displayed all the figures at one time.  You're correct.