4 Replies Latest reply on Jan 3, 2014 10:20 AM by philmodjunk

    Stop calculating at the end of a year

    CaitlinMcKelvie

      Title

      Stop calculating at the end of a year

      Post

           I have kind of a complicated question, and I am not sure if it is possible to do this in FileMaker. I am trying to calculate the percentage of active members that make a donation to our organization during a year. This is the important information from my database:

           Donations:

             
      •           Member Unique ID- Links it to the Member
      •      
      •           Date of donation
      •      
      •           Year of donation (calculated by the function year (donationdate))

           Members

             
      •           Member Unique ID- Links to the donation
      •      
      •           Active (Yes or No)
      •      
      •           Date Created

           I also have a global date field that I use with a drop down to select a year. (glob::year)

           We are constantly adding members and changing members from active to inactive. What I am trying to do is calculate the percentage of members that were active in 2013 that made a donation. However, I don't want this number to change in 2014 if we add more active members, or make members that were active in 2013 when they made the donation but are now inactive.

           I don't have a problem with creating the calculation to give the percentage of active members that have donated during the year based on the information that is in the database right now. 

           Field A= Execute SQL ("Select Count (Distinct Member Unique ID) from Donations where year = ? and Members::Status = 'Active'"; "";""; glob::year)

           Field B= Execute SQL("Select Count (*) from Members where Status = 'Active'"; "";"")

           Field C= Field A/ Field B

           The problem is that the numbers from 2013 change in 2014. Is there a way to get the number for 2013 to stop changing when 2014 starts? I am fine with creating a calculation field for each year if necessary, but I am not sure how to go about doing this. Thanks for your help!

        • 1. Re: Stop calculating at the end of a year
          philmodjunk

               It would seem that your tables lack any way to record which years that a member was active and which that they were not. You'd need some method to list the years for which the member was active. The simplest method for that would be to add a related table linked to members by your member iD field with a number for recording the year that the member was active.

               When you first create a new member record, a script could also create a new record in this "history" table with the current year. When you click a button or run a script to update a member's status, that process would also need to create a new record n the history table with a new year recorded in that number field.

          • 2. Re: Stop calculating at the end of a year
            philmodjunk

                 Just thought of another option. You could add a status field to donations and it could look up (copy) the member's current status. That would record the member's status at the time that they made the donation.

            • 3. Re: Stop calculating at the end of a year
              CaitlinMcKelvie

                   I considered the second solution earlier, but it doesn't take care of the number of active members that there are. How would I be able to do your first option, and have the year update automatically in the related record? So if a member was active in 2012, 2013, and 2014, nothing would have to be done to automatically put it in. This seems like the best solution to me.

              • 4. Re: Stop calculating at the end of a year
                philmodjunk

                     Put an "update status" button on your membership layout that does this:

                     Set Variable [$Year ; Year ( Get ( CurrentDate ) ) ]
                     If [ IsEmpty ( FilterValues ( List ( History::Year ) ; $Year ) ) // check to see if status for current year has been logged already ]
                        Set variable [ $MemberID ; Membership::Member Unique ID ]
                        Go To Layout [ "History" (History)]
                        New Record/Request
                        Set Field [History::Year ; $Year ]
                        Set Field [History::MemberID ; $MemberID ]
                        Go to Layout [original layout]
                     End If

                     And a variation of the expression in the IF function could be used in a conditional format expression to show that the member is or is not currently active.