6 Replies Latest reply on Jan 26, 2012 12:26 PM by philmodjunk

    How can I Summarize selected portal rows

    KevinO'Neill

      Title

      How can I Summarize selected portal rows

      Post

      Greetings

      I am looking to summarize selected portal rows. For example

      I have a members database. On each members page there is a portal that lists dues payments. Every year dues are (should be) paid. Some members want to pay in installments. i enter each payments based on the year.

      Payment 1...2012...AmountPaid...Check number... etc etc

      Payment 2...2012...AmountPaid...Check number... etc etc

      Payment 3...2012...AmountPaid...Check number... etc etc

      Payment 1...2011...AmountPaid...Check number... etc etc

       

      i want to add up only the 2012 payments. the field with 2012 is called Dues year. 2011 is Dues year-1. I tried to get it to comapre to a field called CurrentDuesYear which returns the current year 2012. Because the total needs to compare to a prescribed amount to return an Up to Date Status

      I tried this: Case ( Dues::DuesYear = CurrentDuesYear; Sum (Dues::Amount Paid);0) but it didn't work

      Any guidance would be appreciated

        • 1. Re: How can I Summarize selected portal rows
          philmodjunk

          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

          • 2. Re: How can I Summarize selected portal rows
            KevinO'Neill

            PhilMod

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

            _____________________________________

            • 3. Re: How can I Summarize selected portal rows
              philmodjunk

              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.

              • 4. Re: How can I Summarize selected portal rows
                KevinO'Neill

                PhilMod

                Thanks. this gives me some good stuff to think about. 

                • 5. Re: How can I Summarize selected portal rows
                  KevinO'Neill

                  PhilMod

                  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?

                  • 6. Re: How can I Summarize selected portal rows
                    philmodjunk

                    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 )