By: Laurie
NPSP can do ANYTHING you want it to (mostly). It is because of this that out of the box it pretty much precisely does nothing. Here is a mini project that I just completed for my client. It’s a pretty simple idea. There are some caveats to that and two ‘unhappy’ cases where this simply won’t apply. But it will work for 95% of people.
Project Specifics:
- Client gives two types of memberships.
- Each membership ‘track’ has levels.
- All levels come with a quantity and a discounted OR free event.
- Membership levels are automated based on membership giving amount.
- Membership end date is auto-populated based on gift date.
- Client has a separate system for tracking event tickets.
- Separate system users need to be able to quickly verify benefit entitlement.
- Separate system users need to be able to quickly ‘redeem’ the benefit entitlements at the time of event ticket.
- Potential Issues: People often times will upgrade their memberships – so membership data isn’t stagnant in a time period. Sometimes memberships are extended for various reasons.
Step 1 – Create new object
Name: Benefit Redemption
Fields:
Account (Master-Detail), Benefit Redemption ID (AutoNumber), Date of Visit (date), Is this a current year redemption? (CheckBox), Point in time benefit begin date (date), point in time benefit end date (date), point in time membership level (Text), quantity redeemed (Whole Number), redeemed by (Text), redemption notes (Text Area), ticket number (text).
Step 2 – Create fields on Account object
Name: Account
Fields: Available Benefits (Formula, number), Current Year Benefit Redemptions (Rollup Summary, Sum Benefit Redemption), Lifetime Benefit Redemptions (Rollup Summary, Sum Benefit Redemption), Waive Membership Benefits (Checkbox), Entitlement Quantity (Formula, Number), Entitlement Type (Formula, Text).
Step 3 – Create Workflows
Workflows scare the bejeeus out of me – simply because it’s like magic running rampant in the background (read: I can’t see it and feel like I can’t control it). Once I got over that hurdle, I’ve been having quite a fun time with them. * Remember these are for two membership programs. Most places (I would imagine) would have only one, so edit accordingly.
Workflow A – BR Current Determination True
Because every redemption will not happen in the current year – and the current year varies by each membership, a formula field would just mess things up. This was my work-around for that.
If formula evaluates to true, update the “Is this a current year redemption” True.
Date_of_Visit__c <= Account__r.npo02__MembershipEndDate__c &&
Date_of_Visit__c >= Account__r.npo02__MembershipEndDate__c - 365)
||
(Date_of_Visit__c <= Account__r.XX_MembershipEndDate__c && Date_of_Visit__c >= Account__r.XX__MembershipEndDate__c - 365) Workflow B- BR Current Determination False
If the formula evaluates to True, Make the Is this a current year visit False.
(Date_of_Visit__c <= Account__r.XX_MembershipEndDate__c - 365)
||
(Date_of_Visit__c <= Account__r.npo02__MembershipEndDate__c - 365)Workflow C – BR Point in Time Data
All this does is write a snapshot of the data that was in the account record to the benefits redemption record. I do this because terms change and memberships get upgraded – so a formula wasn’t going to cut it. These are only triggered on creation of the benefit redemption record and never changed again.
Criteria :
Benefit Redemption: Benefit Redemption ID NOT EQUAL TO null
Field Update: Point in time benefit end date (copied from account membership end date)
Field Update: Point in Time benefit Begin Date (Calculated based on the above value – 365
Field Update: Redemption User (copied username)
Field Update : Point in time benefit level (copied from last membership level on the account object)
Step 4 – Populate Account Field Formulas
Available Benefits: Entitlement_Quantity – Current_Year_Benefit_Redemptions
Current Year Benefit Redemptions Rollup Field – with filter criteria “is this a current year redemption” = True.
Entitlement Quantity Formula:
IF( npsp__Membership_Status__c = 'Current' && Waive_Membership_Benefits__c = FALSE,
CASE(npo02__LastMembershipLevel__c,
'Level 1', 2,
'Level 2',4,
'Level 3',6 ,
'Level 4', 8,
'Level 5', 2,
'Level 6', 4,
'Level 7', 4,
0
),IF( XX_Membership_Status__c = 'Current' && Waive_Membership_Benefits__c = FALSE,
CASE(XX_LastMembershipLevel__c,
'Level 1 A', 4,
'Level 2 A',8 ,
'Level 3 A', 4,
'Level 4 A', 6,
'Level 5 A', 2,
'Level 6 A', 4,
0
)
,0))Entitlement Type Text Formula:
F(npsp__Membership_Status__c = 'Current' && Waive_Membership_Benefits__c = FALSE,
CASE(npo02__LastMembershipLevel__c,
'Level 1', "50% Ticket",
'Level 2',"50% Ticket",,
'Level 3', "50% Ticket",,
'Level 4', "50% Ticket",,
'Level 5', "FREE Ticket",
'Level 6', "FREE Ticket",
'Level 7', "FREE Ticket",
null),
IF(XX_Membership_Status__c = 'Current' && Waive_Membership_Benefits__c == FALSE,
CASE(XX_LastMembershipLevel__c,
'Level 1 A',"50% Ticket",
'Level 2 A',"50% Ticket",,
'Level 3 A', "50% Ticket",,
'Level 4 A', "50% Ticket",,
'Level 5 A', "FREE Ticket",
'Level 6 A', "FREE Ticket",
'Level 7 A', "FREE Ticket",
null
), null))VOILA! Membership Benefits Redemption tracking. Phew! That was fun!
