The Nonprofit Starter Pack does so much that it’s almost hard to imagine anything that would improve upon its already existing features. Here is a simple bit of report trickery that does just that.
Most anyone who uses the Nonprofit Starter Pack is familiar with the automatic rollup fields for donations and soft credits which are added to the Contact page layout. What might look slightly less familiar in the image below is the way the rollup fields appear to be clickable links.
The reason that the soft credit fields in the image above appear to be clickable links is because we replaced the existing rollup fields with formula fields which, when clicked, link directly to a report that shows a list of the records that make up that particular value. This way you can quickly and easily get to the information behind the number.
You can utilize this functionality in just ten easy steps! (It will go really fast, I promise)
- Create a report of type “Donations with Contact Roles” (note that if you renamed Opportunities to something other than Donations that the name of this report type will differ). Make sure the report is allowing for all Donations from all time, the date limitations will be handled by the links themselves.
- Set the filter values as follows. Make sure to leave the values blank, these will also be handled by the link fields we create.
- Save the report (name it whatever you want users to see when clicking the link). You need to view the report and copy the report Id in the URL bar at the top of your browser. For example: in this URL you would take the string of characters after salesforce.com/ (https://na12.salesforce.com/00Oi0000005p606)
- Create a new formula field on the Contact object with the same field label as the field you want to replace but NOT the same field name as Salesforce will not allow that, you will need to do this for each rollup field you want to turn into a link.
- Copy and paste the following example formula, where the bold text will have to be replaced:
- The first bold text will be replaced with the Id you copied in step 1.4, make sure to keep the “/” in front of the Id (ex. /00Oi0000005p606).
- The second bold text dictates if this is a normal donation field or a soft credit donation field, use $Setup.npe01__Contacts_And_Orgs_Settings__c.npe01__Opportunity_Contact_Role_Default_role__c for a normal donation field or $Setup.npo02__Households_Settings__c.npo02__Soft_Credit_Roles__c for a soft credit donation field.
- The last bold text will decide what range of close dates to show, you can use date literals to ensure that the links are always current. For LAST_N_DAYS:n use “&pv2=LAST_N_DAYS:” + TEXT($Setup.npo02__Households_Settings__c.npo02__Rollup_N_Day_Value__c) for all other date literals use “&pv2=YOUR_DATE_LITERAL” and replace ‘YOUR_DATE_LITERAL’ with any date literal from the provided date literal link. Also be sure to include the quotation marks for this portion
- All the rest of the formula is used to properly format the link text with commas and a dollar sign.
- Go to any Contact page layouts where you want this clickable field to appear and then simply replace the field currently on the layout with the field you just created.
| HYPERLINK(“/ReportId?pv0=”+Id+”&pv1=” + SUBSTITUTE(SUBSTITUTE(Filter for standard or soft credit donations,”;”,”,”),” “,”+”) + Close Date range , “$” & IF(npo02__Soft_Credit_Last_Year__c >= 1000000, TEXT(FLOOR(npo02__Soft_Credit_Last_Year__c / 1000000)) & “,”, “” ) & IF(npo02__Soft_Credit_Last_Year__c >= 1000, RIGHT(TEXT(FLOOR(npo02__Soft_Credit_Last_Year__c / 1000)), 3) & “,”, “” ) & RIGHT(TEXT(FLOOR(npo02__Soft_Credit_Last_Year__c)), 3) & “.” & IF(MOD(npo02__Soft_Credit_Last_Year__c , 1) * 100 < 10, “0” & TEXT(ROUND(MOD(npo02__Soft_Credit_Last_Year__c , 1), 2) * 100), TEXT(MIN(ROUND(MOD(npo02__Soft_Credit_Last_Year__c , 1), 2) * 100, 99)) ), “_blank” ) |
That’s it, you’re done!
