11 Replies Latest reply on Mar 14, 2013 5:23 AM by EoinWhite

    Calculating Fiscal Year Donation Total Within Portal

    user14691

      Title

      Calculating Fiscal Year Donation Total Within Portal

      Post

      I have a portal created in my database that tracks a user's donations to a foundation. I am trying to create a summary calculation in the portal that would take all donations from the fiscal year (7/1-6/30) and total them. Then based on that total amount, a different field would calculate what donor level they are for the fiscal year.

      I know that this will probably be a 2-step calculation, and am not finding anything in similar threads that have been able to help so far. Thanks in advance for your help.

      Picture_6.png

        • 1. Re: Calculating Fiscal Year Donation Total Within Portal
          philmodjunk

          Do you really want this "within the portal", or just below it by putting it in place of the question mark below the last portal row?

          • 2. Re: Calculating Fiscal Year Donation Total Within Portal
            user14691

            I don't believe it has to be within the portal, it can be underneath.

            • 3. Re: Calculating Fiscal Year Donation Total Within Portal
              philmodjunk

              Not all the records shown in the portal are from the current Fiscal Year. (The two dated in March fall in Fiscal Year 2010). You can either establish a second relationship to this file that only relates to the donor's records for the current fiscal year or you can add a one row filtered portal that shows the total for the current Fiscal Year.

              With the fiscal year specific relationship, a calculation field can use the sum function to compute the total fiscal year to date for the current donor. With a filtered portal (FileMaker 11 only), you can define a summary field in the portal's table and display it in a one row filtered portal.

              I prefer the first approach if the user needs to edit data in the portal that will modify this total. Using a filtered portal in this case needs extra scripting to force the total to update and even then produces a bit of "screen flash" that isn't an issue with the other approach.

              Either way, you need a calculation to identify the fiscal year for each record and also for the current date:

              Let ( D = YourTable::DateField ; Year ( D ) - ( Month ( D ) < 7 ) )

              will compute the fiscal year for your dontation records and:

              Let ( D = Get ( CurrentDate ) ; Year ( D ) - ( Month ( D ) < 7 ) )

              Will compute the Fiscal Year for the current date.

              In a portal filter expression, it'd look like this:

              Let ( D = PortalTable::DateField ; Year ( D ) - ( Month ( D ) < 7 ) ) = Let ( D = Get ( CurrentDate ) ; Year ( D ) - ( Month ( D ) < 7 ) )

              In a relationship, you'd define cFiscalYear in your donations table to use the first expression and cThisFiscalYear as an unstored calculation that uses the second. Then you can define this relationship:

              Donors::DonorID = DonationsFiscalYear::DonorID AND
              Donors::cThisFiscalYear = DonationsFiscalYear::cFiscalYear

              Then Sum ( DonationsFiscalYear::DonationAmount )

              will compute the total for the current fiscal year for the current donor.

              Note: DonationsFiscalYear would be a new table occurrence of Donations.

              • 4. Re: Calculating Fiscal Year Donation Total Within Portal
                user14691

                Ok, I went through your instructions, and at first it appeared to be working, but upon further testing it looks like my sum is not totalling correctly for the fiscal year. I am fairly new to filemaker, and the place that gave me the most trouble was the relationship, so I am including a screen shot of my relationships to hopefully help shed some light.

                • 5. Re: Calculating Fiscal Year Donation Total Within Portal
                  philmodjunk

                  If your layout is based on contacts, then cThisFiscalYear should be defined in contacts and the relationship using this field should link directly to the contacts table.

                  • 6. Re: Calculating Fiscal Year Donation Total Within Portal
                    user14691

                    The layout itself is based on contacts, but my portal is based on registrations contact ID, does this change anything? What it is now doing is summing only the donations I enter in July 2011.  Any other month or year (whether current fiscal year or not) does not get added to that total.

                    • 7. Re: Calculating Fiscal Year Donation Total Within Portal
                      philmodjunk

                      You need these relationships so that you see a current fiscal year total for the current contact:

                      Contacts::ContactID = RegistrationsFiscalYear::ContactID AND
                      Contacts::cThisFiscalYear = RegistrationsFiscalYear::cFiscalYear

                      Then you can use either a summary field defined in the data source table for ResgistrationsFiscalYear or a calculation field defined in Contacts with this expresssion: Sum ( RegistrationsFiscalYear::DonationAmount ) to compute and display a total for the current fiscal year.

                      Also, don't forget to define cThisFiscalYear as an unstored calculation field or it won't update properly.

                      • 8. Re: Calculating Fiscal Year Donation Total Within Portal
                        user14691

                        Thank you! Got it to work.

                        • 9. Re: Calculating Fiscal Year Donation Total Within Portal
                          user14691

                          Just out of curiosity, how would the ThisFiscalYearC change if you were to calculate the donation totals for, say, the fiscal year 2010?

                          • 10. Re: Calculating Fiscal Year Donation Total Within Portal
                            philmodjunk

                            ThisFiscalYearC computes the current fiscal year from your computer's system clock. If you want to be able to specifiy the fiscal year, use a global number field and enter the year or select it from a value list in this field.

                            • 11. Re: Calculating Fiscal Year Donation Total Within Portal
                              EoinWhite

                                   Hey there,

                                   I have just implemented the calculations from above (see below) and it worked perfectly - thanks PhilModJunk!  But am wondering how I may change it so that it works with several different fiscal years.  I am tracking pretty much the same thing as the original poster, but I need to track a number of different fiscal years.

                                   I'm guessing the '7' in the calculations below refers to July as per the original query. I tried creating a new number feild (FirstMonth) and then replaced the 7 with ( Table Name::FirstMonth ) but it did not work at all.  Any ideas?

                                    

                                   Let ( D = YourTable::DateField ; Year ( D ) - ( Month ( D ) < 7 ) )

                                    

                                   Let ( D = Get ( CurrentDate ) ; Year ( D ) - ( Month ( D ) < 7 ) )