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.
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.
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 )
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.
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.
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.
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.