9 Replies Latest reply on Jun 10, 2010 12:42 PM by jringold

    calculating total by fiscal year

    jringold

      Title

      calculating total by fiscal year

      Post

      Hello,

       

      This is my first post to this forum so I will try to provide the appropriate information and I am grateful for your responses.

       

      Basic info.

       

      Filemaker version: 11

      Operating system: XP

      Database info.: Donors::DonorID = Donations::DonorID

      My level of experience: Filemaker newbie, competent with SQL and relational databases

       

      What I am trying to accomplish is similar to what I have read in this post:

      http://forum-en.filemaker.com/t5/Using-FileMaker-Pro/Calculating-within-a-date-range/m-p/65884/highlight/true#M51192

       

      I have set up a layout for donor details with a portal to related donations. That works fine. Though this page is not a report, I would like to show some summary data here.  I have created a summary field in the donations table which totals the donations, and that correctly calculates the total donations for that donor. Now I'd like to display the total donations for that donor for the current fiscal year. If I do this using global fields for the date range and setting up new table occurences and relationships as in the URL above, the users would need to change those dates every year. Is there a way to use the current year in this calculation? Or maybe another approach for adding the date criteria to this calculation?

       

      I hope this is clear and thanks for the help!

       

      -Jim

        • 1. Re: calculating total by fiscal year
          comment_1

          If this for display only, you can place another portal to Donations on the layout and filter it to show only records in the current year. The portal needs only one row. Place the same summary field from Donations in the portal.

          • 2. Re: calculating total by fiscal year
            jringold

            Thanks!

             

            -Jim

            • 3. Re: calculating total by fiscal year
              jringold

              Thanks for your help! OK, I am making progress, I now have another portal that correctly filters by date.

               A few questions have come up:

               

              1) If I add a new donation row or change a row via my first portal, the lifetime total and fiscal year total do not update. If I go to another record and then go back the totals are updated correctly. Is this a case where I should be using a "running total"? Or is there a better way to trigger the refresh?

               

              2) If I make a portal just one row with one field, I can't seem to bring up the portal setup dialog (the field seems to cover the whole portal, and I'm just double clicking on the field. With the other portals I double click between fields to select the portal rather than a field.) What is the right way to be able to edit the portal setup?

               

              3) How can I make the dates used by the filter always use the current fiscal year? For instance, today the current fiscal year is July 1, 2009 to June 30, 2010. On July 1, 2010 the current year would be July 1, 2010 to June 30, 2011.

               

              Much thanks!

              -Jim

              • 4. Re: calculating total by fiscal year
                comment_1

                1. You need to refresh the window - you could have a button for this, or use a script triggered by the update.

                2. Make the field a bit smaller? Or put it aside for the time you are dealing with the portal.

                3. You can calculate the fiscal year as =

                Year ( date ) - ( Month ( date ) < 7 )


                • 5. Re: calculating total by fiscal year
                  jringold

                  Thanks much for your help! Please pardon my slowness with FileMaker.

                   

                  1. OK, I made a script that uses "Refresh Window" and set that to be triggered by Record Commit, but that had no effect when I updated an amount (maybe the wrong choice on my part). So I also made a button for "Refresh Window". When I update an amount and push that button the first calculation for total amount refreshes, but not the second (filtered) total for fiscal year. When I hit the button a second time, the second total does refresh. What is the correct way to do this?

                  2. Thanks! Maybe I need to choose a fill color for the portal so that I can distinguish it from the background. Anyway I seem to have found the right place to click.

                  3. Do you mean that I should make a calculated field for fiscal year, or does this go in the filter criteria? Currently my filter criteria is Donations::Received_Date > Date ( 6; 1; 2009), but that (obviously) is not the way to automatically choose the current fiscal year's results.

                   

                  Thanks!

                  -Jim

                  • 6. Re: calculating total by fiscal year
                    comment_1

                    1. I'd have to look into it to be sure it's really necessary, but try Refresh Window [ Flush cached join results].

                     

                    3. You could have a calculation field for the fiscal year (esp.if you need this elsewhere), but you can also filter by:

                     

                     

                    Donations::Received_Date ≥ Date ( 6 ; 1 ; Year ( Get (CurrentDate) ) - ( Month ( Get (CurrentDate) ) < 6 ) )

                     

                    ---

                    EDIT: is it July or June? Earlier you said July, but your calc says June.

                    • 7. Re: calculating total by fiscal year
                      jringold

                      Thanks for your prompt and helpful replies!

                       

                      1. OK, that did the trick for the refresh. Thanks!

                       

                      2. OK, we are on the right track. To be clearer, the fiscal year ends on June 30. With the filter as you suggested, I am not catching a date such as 1/1/2010, but I am catching a date such as 1/1/2011.

                       

                      Thanks again!

                       

                      -Jim

                      • 8. Re: calculating total by fiscal year
                        comment_1

                         


                        jringold wrote:

                        To be clearer, the fiscal year ends on June 30.


                        Well, then it starts with July, not June. Change the 6 to 7 (both of them) and that should be it.

                         


                        • 9. Re: calculating total by fiscal year
                          jringold

                          Thanks! !!