Sum cannot selectively total related records. Your case statement returns either a sum of all related records or 0--which is not what you want here. To use Sum to total related records selectively, the relationship used must be such that it only matches to the records you want to total up. The other option is to use a summary field from the portal table and a portal filter that limits the records to those you want to total.
What do you want to see here?
Just the current total payments for the current year?
Or a list of total payments for each year starting with the current year?
Do you use FileMaker 11 (required for filtered portals)?
Is this the relationship you have?
Members::MemberID = Payments::MemberID
I have to accomplish a few things here. I need to look at the related payments and see if a member has paid this years dues and last years dues. As long as he does this he is in good standing. Some guys pay more than once a year so I have to add those up. Then others skip a year and pay for two years before they get suspended. Not correct but hey we are not here to be throwing guys out. so those guys would have a blank entry for 2011 and a bigger amount for 2012
So i am not sure how i want to do it other than maybe this:
1. add the known values of the current year and previous years dues. right now it 83 and 81.50 respectively,
2. compare a members payments to these amounts and see if there is a difference. if so try and evaluate what that difference is. if its short 83 then he probably owes this years, if 81.50 he owes last year, if 164.50 he owes both years.
some guys even owe for three years and again hey should be suspended but sometimes they are hurting for cash and we let them slide another year, depends upon th circumstance. Then when a guy becomes a member his first year he doesn't pay dues, and to top it off some guys are dual members and owe only half, because they pay elsewhere first.
I have FM 11
Here is a screen capture to help maybe. I had originally set up a CASE that looked to see if there was an entry for a specific year. If so then look at the next line, entry there? Yes, No, ok then evaluate. It works great until someone pays a second payment. Then it all falls apart.
Below was my crazy Case statement"
Case (GetLayoutObjectAttribute ("DuesYearField";"content";1;1) = (Year ( Get ( CurrentDate ) )+1 ); "Reevaluate in " & (Year ( Get ( CurrentDate ) )+1); DuesRate = "Remit"; "Not Applicable";DuesRate = "Not Applicable"; "Not Applicable";GetLayoutObjectAttribute ("DuesYearField";"content";1;1) ≠ (Year ( Get ( CurrentDate ) ) ) and GetLayoutObjectAttribute ("DuesYearField";"content";1;1) ≠ (Year ( Get ( CurrentDate ) )-1);" In Arrears For 2 Years"; GetLayoutObjectAttribute ("DuesYearField";"content";1;1) = (Year ( Get ( CurrentDate ) )-1);"Outstanding For " &(Year ( Get ( CurrentDate ) ));GetLayoutObjectAttribute ("DuesYearField";"content";1;1) = (Year ( Get ( CurrentDate ) )) and GetLayoutObjectAttribute ("DuesYearField";"content";1;2) ≠ (Year ( Get ( CurrentDate ) )-1);"Outstanding For " &(Year ( Get ( CurrentDate ) )-1);"Up To Date" )
Seems like the key calculation here is to sum up all dues owed, all payments recieved and to then compare the two totals. If total payments is greater than or equal to total dues owed, they don't owe any dues.
It looks like you have one record in Dues for every member for every year that they've been an active member (Possibly skipping the first year. they join...) Is that correct? A Script can cycle through all active members once a year and generate a new record in Dues, using their status and the data in DuesByYear to compute a DuesAmount to enter into Dues.
You can add a table for payments and link it in like this:
Member_data::MemberID = DuesPayments::MemberID
Then if Sum ( DuesPayments::Amount ) > Sum ( Dues::DuesYearlyAmount )
Your member does not owe dues.
You could also define summary fields sTotalDuesPaid in payments and sTotalDuesOwed in Dues to total these same two fields. Then you can compare sTotalDuesPaid to sTotalDuesOwed in the same fashion in the context of Member_Data to determine if a member is "paid up".
Note that with this approach, you can adjust or even forgive dues for a given year for a given member by changing the DuesYearlyAmount for a Dues record linked to their Member_data record.
Thanks. this gives me some good stuff to think about.
Hypotheticaly speaking. if i do go another direction from where I am right now I have over 450 entries that have the amount owed based on a calculation. As far as i can tell i can't change the data in the calculation field because it isn't modifiable. If i change that and go with your script idea is there a way to keep all of those entries and have that field populate the new script way?
When you change a stored calculation field to a data field, the current value of the calculation is entered as the value stored in the field.
Don't be so quick to not have a calculation for this. As long as you have separate records for the dues owed for each year for each member, the rest of the system works and the calculation method can still be an option. Here's an example:
Add a field named "adjustment" where you can enter a value when you want to forgive a member's dues for that year or adjust it to a different value from that returned by the calculation.
Change your calculation to be:
If ( Not IsEmpty ( Adjustment ) ; Adjustment ; //Put your current calculation here )