Skip to content
Sonia Chugh's Portfolio
My Portfolio
NPSP Project Overview
Data Design and Setup
Backend Configuration
Programming and UI
Add-on Configuration
Share
Explore

Formulas Fields

Account Object: In addition to custom rollups, the following formulas were created for reporting purposes to understand Donor’s Engagement. These use the target fields created for Custom Rollups.
For custom rollup fields
click here
⁠
⁠
Expand or Collapse each section for your reference.

Days Since First Gift:

Purpose: To get the max value by comparing today's date with First Close Date , First Account Soft Credit Date and First Contact Soft Credit Date
Formula:
MAX( IF(NOT(ISBLANK(npo02__FirstCloseDate__c)), TODAY() - npo02__FirstCloseDate__c, 0), IF(NOT(ISBLANK(First_Account_Soft_Credit_Date__c)), TODAY() - First_Account_Soft_Credit_Date__c, 0), IF(NOT(ISBLANK(First_Contact_Soft_Credit_Date__c)), TODAY() - First_Contact_Soft_Credit_Date__c, 0) )

Days Since Last Gift:

Purpose: To get the minimum value between today’s date and one of the last dates for hard or soft credit
Formula:
MIN ( IF( NOT( ISBLANK( npo02__LastCloseDate__c ) ) ,TODAY() - npo02__LastCloseDate__c , 0), IF( NOT( ISBLANK( Last_Account_Soft_Credit_Date__c ) ) ,TODAY() - Last_Account_Soft_Credit_Date__c , 0) , IF( NOT( ISBLANK( Last_Contact_Soft_Credit_Date__c ) ) ,TODAY() - Last_Contact_Soft_Credit_Date__c , 0) )

First Gift Date Soft or Hard:

Purpose: To get the first date that either a hard credit or a soft credit was given. This is done by comparing the value of Days Since First Gift (see details above) and to get the date:- First close date OR First Account Soft Credit Date OR First Contact Soft Credit Date
Formula:
CASE( Days_Since_First_Gift__c, TODAY() - npo02__FirstCloseDate__c, npo02__FirstCloseDate__c, TODAY() - First_Account_Soft_Credit_Date__c, First_Account_Soft_Credit_Date__c, TODAY() - First_Contact_Soft_Credit_Date__c, First_Contact_Soft_Credit_Date__c, NULL )

Last Gift Date Soft or Hard:

Purpose: To find the recent most gift, hard or soft. This is done by comparing the value of Days Since Last Gift (see details above) and to get the date:- Last close date OR Last Account Soft Credit Date OR Last Contact Soft Credit Date
Formula:
CASE( Days_Since_Last_Gift__c, TODAY() - npo02__LastCloseDate__c, npo02__LastCloseDate__c, TODAY() - Last_Account_Soft_Credit_Date__c, Last_Account_Soft_Credit_Date__c, TODAY() - Last_Contact_Soft_Credit_Date__c, Last_Contact_Soft_Credit_Date__c, NULL )


Total Gifts Soft or Hard All Time

Purpose: Gets the total gifts soft and hard on account and contact
Formula:
npo02__TotalOppAmount__c + Total_Account_Soft_Credit_Amount__c + Total_Contact_Soft_Credit_Amount__c

Gifts Soft or Hard This FY

Purpose: Total Gifts with Total Account and Total Contact soft credits for this Fiscal Year
Formula:
npo02__OppAmountThisYear__c + Total_Account_Soft_Credits_This_FY__c + Total_Contact_Soft_Credits_This_FY__c

Gifts Soft or Hard Last FY

Purpose: Total Gift Amount from Last Year with Total Account Soft Credit and Contact Soft Credit of Last Fiscal Year
Formula:
npo02__OppAmountLastYear__c + Total_Account_Soft_Credits_Last_FY__c + Total_Contact_Soft_Credits_Last_FY__c


Contact Object: Formula fields created for reporting purposes:

Communication Capability

Purpose: Formula for written form of communication email and/or mailing address
Formula:
IF( ISBLANK(Email) && ISBLANK(MailingStreet), "No Email and Address", IF( ISBLANK(Email) && NOT(ISBLANK(MailingStreet)), "Only Address", IF( NOT(ISBLANK(Email)) && ISBLANK(MailingStreet), "Only Email", IF( NOT(ISBLANK(Email)) && NOT(ISBLANK(MailingStreet)), "Address and Email", NULL ) ) ) )

Has Phone?

Purpose: Formula that stores a value for phone or not. This field is used in reports.
Formula:
IF( ISBLANK( Phone), "No Phone", "Has Phone")




Want to print your doc?
This is not the way.
Try clicking the ⋯ next to your doc name or using a keyboard shortcut (
CtrlP
) instead.