Regular
Posts: 46
| Is yours a membership organization? Is membership renewed annually? If so, how do you update Donor Status and Renewal Date? I’ve seen several attempts to do it with SmartLists and the new GW2008 update functionality, but I’ve yet to see those attempts go past experiment to result in a repeatable, reliable, efficient process. Would you like Status update to be automatic? Consistent? Reliable? Have donors also automatically go to “Lapsed” if they don’t renew by the end of the grace period? Read on…
The problem is that most organizations have moved from calendar year or fiscal year membership to “rolling” renewals. That is, your first renewal date is calculated from the date you join, and the next renewal date is based on the last renewal. Often there are grace periods and other complexities to the calculation. Even if the org’s board has approved a formal membership Policy, it’s unlikely that ease of implementation factored into its formulation.
GiftWorks doesn’t do calculations in SmartLists or in the update functions, so you’ll have to “work under the hood” on the .GDS with MS Access or a similar database tool. Here’s a general outline of how to do it:
1. You probably want to mark all the members of a household as “Active Member”/”Lapsed Member” (or whatever you call them) together. That is if the dues payment comes to the household, its Affiliates should also be made Active and the Renewal Date bumped. To do that, you need a queries that group all members of a household together and combine them with “singletons”.
Household members:
SELECT [donor_donors_1.display] AS household_display, [donor_donors_1.id] AS household_id, [donor_donors.display] AS donor_display, [donor_donors.id] AS donor_id
FROM (donor_donors LEFT JOIN donor_donor_relationships ON donor_donors.id = donor_donor_relationships.FKRelatedToId) LEFT JOIN donor_donors AS donor_donors_1 ON donor_donor_relationships.FKDonorId = donor_donors_1.id
WHERE (((donor_donors_1.profile)=3));
Singletons:
SELECT [donor_donors.display] AS household_display, [donor_donors.id] AS household_id, [donor_donors.display] AS donor_display, [donor_donors.id] AS donor_id
FROM donor_donors LEFT JOIN jls_household_members ON donor_donors.id = jls_household_members.donor_id
WHERE (((jls_household_members.donor_id) Is Null));
Combined:
SELECT jls_household_members.household_display, jls_household_members.household_id, jls_household_members.donor_display, jls_household_members.donor_id
FROM jls_household_members
UNION SELECT jls_nonhousehold_members.household_display, jls_nonhousehold_members.household_id, jls_nonhousehold_members.donor_display, jls_nonhousehold_members.donor_id
FROM jls_nonhousehold_members
ORDER BY household_display, household_id;
2. Next, we need the most recent Dues payment date by any household member (or self for singletons) for each donor. 1001 is the Fund key for Member Dues:
SELECT jls_combined.household_id, Max(CDate([actualDate])) AS DuesDate
FROM jls_combined INNER JOIN (donor_gifts INNER JOIN donor_gifts_payments ON donor_gifts.id = donor_gifts_payments.giftId) ON jls_combined.donor_id = donor_gifts.donorId
GROUP BY jls_combined.household_id, donor_gifts_payments.FKFund
HAVING (((donor_gifts_payments.FKFund)=1001))
ORDER BY jls_combined.household_id, Max(CDate([actualDate]));
Followed by the actual Dues Amount:
SELECT jls_combined.household_id, jls_combined_1.donor_id, donor_gifts_payments.actualDate, donor_gifts_payments.actualAmount
FROM jls_combined AS jls_combined_1 INNER JOIN ((jls_combined INNER JOIN jls_dues_date ON jls_combined.household_id = jls_dues_date.household_id) INNER JOIN (donor_gifts INNER JOIN donor_gifts_payments ON donor_gifts.id = donor_gifts_payments.giftId) ON (jls_dues_date.DuesDate = donor_gifts_payments.actualDate) AND (jls_combined.donor_id = donor_gifts.donorId)) ON jls_combined_1.household_id = jls_combined.household_id
WHERE (((donor_gifts_payments.FKFund)=1001) AND ((donor_gifts.FKcampaign)=9 Or (donor_gifts.FKcampaign)=1000 Or (donor_gifts.FKcampaign)=1002))
ORDER BY jls_combined.household_id, donor_gifts_payments.actualDate;
3. We need the base renewal date for each household or singleton:
SELECT jls_combined.household_id, Max(CDate([renewalDate])) AS CombinedRenewalDate
FROM jls_combined INNER JOIN donor_donors ON jls_combined.donor_id=donor_donors.id
WHERE (((donor_donors.renewalDate) Is Not Null))
GROUP BY jls_combined.household_id;
4. We calculate how many months have elapsed since the last renewal date and the dues payment to see if the payment is in or out of the grace period:
SELECT jls_combined.household_id, jls_combined.donor_id, jls_most_recent_dues.actualDate, jls_most_recent_dues.actualAmount, jls_renewal_date.CombinedRenewalDate, IIf(IsDate(jls_renewal_date!CombinedRenewalDate),Month(CDate(jls_renewal_date!CombinedRenewalDate)),99)-Month(CDate(jls_most_recent_dues!actualDate)) AS MonthDiff, IIf(IsDate([CombinedRenewalDate]),Month(CDate([CombinedRenewalDate])) & '/' & Day(CDate([CombinedRenewalDate])) & '/',Month(CDate([actualDate])) & '/' & Day(CDate([actualDate])) & '/' AS MonthDay, CInt(Year(CDate(jls_most_recent_dues!actualDate))) AS BaseYear
FROM (jls_combined INNER JOIN jls_most_recent_dues ON jls_combined.household_id=jls_most_recent_dues.household_id) LEFT JOIN jls_renewal_date ON jls_combined.household_id=jls_renewal_date.household_id
GROUP BY jls_combined.household_id, jls_combined.donor_id, jls_most_recent_dues.actualDate, jls_most_recent_dues.actualAmount, jls_renewal_date.CombinedRenewalDate, IIf(IsDate(jls_renewal_date!CombinedRenewalDate),Month(CDate(jls_renewal_date!CombinedRenewalDate)),99)-Month(CDate(jls_most_recent_dues!actualDate)), IIf(IsDate([CombinedRenewalDate]),Month(CDate([CombinedRenewalDate])) & '/' & Day(CDate([CombinedRenewalDate])) & '/',Month(CDate([actualDate])) & '/' & Day(CDate([actualDate])) & '/', CInt(Year(CDate(jls_most_recent_dues!actualDate)))
HAVING (((jls_renewal_date.CombinedRenewalDate)<DateAdd('m',18,Date())))
ORDER BY jls_combined.household_id, jls_combined.donor_id, jls_most_recent_dues.actualDate;
5. At last we have all the pieces we need, and we can calculate the next renewal date:
SELECT jls_combined.donor_id, jls_month_diff.actualDate, jls_month_diff.actualAmount, jls_month_diff!MonthDay & Switch(jls_month_diff!MonthDiff>12 Or (jls_month_diff!MonthDiff>=-6 And jls_month_diff!MonthDiff<=6),jls_month_diff!BaseYear+1,jls_month_diff!MonthDiff>6,jls_month_diff!BaseYear,jls_month_diff!MonthDiff<-6,jls_month_diff!BaseYear+2) AS NextRenewalDate
FROM jls_combined INNER JOIN jls_month_diff ON jls_combined.donor_id=jls_month_diff.donor_id
ORDER BY jls_combined.donor_id, jls_month_diff.actualDate;
…and the new Status:
SELECT donor_donors.id, IIf(CDate(donor_donors!renewalDate)>DateAdd('m',-4,Date()),IIf([actualAmount]>=100,16,13),12) AS NewStatus, donor_donors.renewalDate, donor_donors.FKstatus
FROM donor_donors INNER JOIN jls_most_recent_dues ON donor_donors.id = jls_most_recent_dues.donor_id
WHERE (((donor_donors.renewalDate) Is Not Null) AND ((IsNull(([donor_donors].[FKstatus])))<>False)) OR (((donor_donors.renewalDate) Is Not Null) AND ((donor_donors.FKstatus)<>14 And (donor_donors.FKstatus)<>15));
6. But Access doesn’t have an internal scheduler, so to do the actual update, it’s necessary to use an external VB Script file:
' constants
Const adOpenStatic = 3
Const adLockOptimistic = 3
Dim Start
Dim lReturn
Dim sSQL
Dim iCount
Dim dRenewalDate
Start=Time()
'WScript.Echo "Opening connection"
Set conn = CreateObject("ADODB.Connection"
conn.Open _
"Provider= Microsoft.Jet.OLEDB.4.0; " & _
"Data Source=GiftWorks.gds"
'WScript.Echo "Getting Individuals with Recent Dues"
set rs=CreateObject("ADODB.Recordset"
rs.open "SELECT * FROM jls_next_renewal_date", conn, adOpenStatic, adLockOptimistic
'WScript.Echo "Found " & rs.RecordCount & " new renewal dates"
If rs.RecordCount > 0 Then
rs.MoveFirst
Do UNTIL rs.EOF
dRenewalDate = rs("NextRenewalDate"
sSQL = "UPDATE donor_donors " & _
"SET renewalDate = #" & rs("NextRenewalDate" & "#" & _
" WHERE donor_donors.id = " & rs("donor_id"
'WScript.Echo "Updating Donor " & sSQL
conn.Execute sSQL
iCount=iCount + 1
rs.MoveNext
Loop
'WScript.Echo "Updated Renewal Dates for " & iCount & " donors"
End If
rs.Close
rs.open "SELECT * FROM jls_update_status", conn, adOpenStatic, adLockOptimistic
'WScript.Echo "Found " & rs.RecordCount & " donors with updatable status"
If rs.RecordCount > 0 Then
rs.MoveFirst
Do UNTIL rs.EOF
sSQL = "UPDATE donor_donors " & _
"SET fkstatus = " & rs("NewStatus" & _
" WHERE donor_donors.id = " & rs("id"
'WScript.Echo "Updating Donor " & sSQL
conn.Execute sSQL
iCount=iCount + 1
rs.MoveNext
Loop
'WScript.Echo "Updated Statuses for " & iCount & " donors"
End If
rs.Close
'wscript.echo "Start " & Start & " Stop " & Time()
7. You can run that script manually before running SmartLists, Reports, or Mailings that rely on Status or Renewal Date. But it’s easy to forget, so the final automation step is to use the Windows Task Scheduler to fire it as often as you like. If the GW database is on a server, you could even run it every minute, which would make it appear to be more-or-less real time.
If you’ve got a better way to do this, please let me know, because obviously this is a completely bletcherous Rube Goldberg approach, and a real nuisance to alter (or maintain when MR changes the database!).
Edited by jschuman 2008-10-22 8:48 PM
|
