$icon picker$
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.