Q: How can I see each Account’s most-recently-won Opportunity Close Date, Amount, and ID?
A: Roll-Up and Formula fields! (Yeah, you can do it with code, but let’s show off.)
Create a formula field on Opportunity that creates an encoded (“hashed“) version of each Opportunity’s Close Date and Amount:
- Opportunity Field HASH Close Date & Amount (Number Formula Field):
VALUE(TEXT(YEAR(CloseDate)) &
LPAD(TEXT(MONTH(CloseDate)), 2, "0") &
LPAD(TEXT(DAY(CloseDate)), 2, "0") &
LPAD(TEXT(Amount), 10, "0"))
This field would show 200802290000000500 for an Opportunity that closed on February 29, 2008 for $500:
- The first eight digits of the formula (20080229) are the Close Date in YYYYMMDD format, and
- the last ten digits (0000000500) are the Amount.
Then create these fields on Account:
- Account Field HASH Latest Opty Won: CloseDate&Amount (Rollup Summary Field):
MAX Opportunity.HASH Close Date & Amount
CRITERIA: Won = True
- Account Field Latest Opty Won: Amount (Number Formula Field):
IF(HASH_Latest_Opty_Won_CloseDate_Amount__c > 0,
VALUE(RIGHT(TEXT(HASH_Latest_Opty_Won_CloseDate_Amount__c), 10)),
NULL)
- Account Field Latest Opty Won: Close Date (Date Formula Field):
IF(HASH_Latest_Opty_Won_CloseDate_Amount__c > 0,
DATE(VALUE(MID(TEXT(HASH_Latest_Opty_Won_CloseDate_Amount__c), 1, 4)),
VALUE(MID(TEXT(HASH_Latest_Opty_Won_CloseDate_Amount__c), 5, 2)),
VALUE(MID(TEXT(HASH_Latest_Opty_Won_CloseDate_Amount__c), 7, 2))),
NULL)
This principle can be used to roll all sorts of data up from child objects to parent objects.
One example: you want each Account’s highest-Stage Opportunity Close Date; replace the “Won Date & Amount” field described above with a field called “Stage & Close Date”:
- Opportunity Field HASH Stage & Close Date (Number Formula Field):
CASE(TEXT(StageName),
"Prospecting", 1,
"Qualification", 2,
"Needs Analysis", 3,
"Value Proposition", 4,
"Id. Decision Makers", 5,
"Perception Analysis", 6,
"Proposal/Price Quote", 7,
"Negotiation/Review", 8,
"Closed Won", 9,
0)
* 100000000 +
VALUE(TEXT(YEAR(CloseDate)) &
LPAD(TEXT(MONTH(CloseDate)),2,"0") &
LPAD(TEXT(DAY(CloseDate)),2,"0"))
- Account Field HASH Highest Opty: Stage&CloseDate (Rollup Summary Field):
MAX Opportunity.HASH Stage & Close Date
- Account Field Highest Opty: Stage (Text Formula Field):
IF(HASH_Highest_Opty_Stage_CloseDate__c > 0,
CASE(VALUE(LEFT(TEXT(HASH_Highest_Opty_Stage_CloseDate__c), 1)),
1, "Prospecting",
2, "Qualification",
3, "Needs Analysis",
4, "Value Proposition",
5, "Id. Decision Makers",
6, "Perception Analysis",
7, "Proposal/Price Quote",
8, "Negotiation/Review",
9, "Closed Won",
"#Error!#"),
NULL)
- Account Field Highest Opty: Close Date (Date Formula Field):
DATE(VALUE(MID(TEXT(HASH_Highest_Opty_Stage_CloseDate__c), 2, 4)),
VALUE(MID(TEXT(HASH_Highest_Opty_Stage_CloseDate__c), 6, 2)),
VALUE(MID(TEXT(HASH_Highest_Opty_Stage_CloseDate__c), 8, 2)))
A different, crazier example: you want to roll up the Most-Recently-Won Opportunity ID.
In this case, I’m encoding the date as the first five digits of the formula, representing days elapsed since January 1, 1970. This will work for all dates between May 19, 1997 through October 16, 2243.
The ID is encoded using a one-digit number for characters 4-9 of the Opportunity ID (in most cases, there will only be one or two different combinations).
- Opportunity Field HASH Close Date & Opty ID (Number Formula Field):
You’ll need to change the second line to reflect characters 4-9 of the Opportunity IDs in your org.
If there’s more than two combinations for characters 4-9 of the Opportunity IDs in your org, you’ll need to nest another IF() for each additional combination:
(CloseDate - DATE(1970, 1, 1)) * 10000000000000 +
IF(MID(Id, 4, 6) = "130000", 0, IF(MID(Id, 4, 6) = "6A0000", 1, 9)) * 1000000000000 +
FIND(MID(Id, 10, 1), "ABCDEFGHIJKLMNOPQRSTUVWXYZabcdefghijklmnopqrstuvwxyz0123456789") * 10000000000 +
FIND(MID(Id, 11, 1), "ABCDEFGHIJKLMNOPQRSTUVWXYZabcdefghijklmnopqrstuvwxyz0123456789") * 100000000 +
FIND(MID(Id, 12, 1), "ABCDEFGHIJKLMNOPQRSTUVWXYZabcdefghijklmnopqrstuvwxyz0123456789") * 1000000 +
FIND(MID(Id, 13, 1), "ABCDEFGHIJKLMNOPQRSTUVWXYZabcdefghijklmnopqrstuvwxyz0123456789") * 10000 +
FIND(MID(Id, 14, 1), "ABCDEFGHIJKLMNOPQRSTUVWXYZabcdefghijklmnopqrstuvwxyz0123456789") * 100 +
FIND(MID(Id, 15, 1), "ABCDEFGHIJKLMNOPQRSTUVWXYZabcdefghijklmnopqrstuvwxyz0123456789") * 1
- Account Field HASH Latest Opty Won: CloseDate&OptyID (Rollup Summary Field):
MAX Opportunity.HASH Close Date & Opty ID
CRITERIA: Won = True
- Account Field HASH Latest Opty Won: OptyID (Text Formula Field):
MID(TEXT(HASH_Latest_Opty_Won_CloseDate_OptyID__c), 6, 13)
- Account Field Latest Opty Won: Hyperlink (Text Formula Field):
As with the Opportunity field above, you’ll need to change the fourth line to reflect characters 4-9 of the Opportunity IDs in your org.
If there’s more than two combinations for characters 4-9 of the Opportunity IDs in your org, you’ll need to nest another IF() for each additional combination:
IF(HASH_Latest_Opty_Won_OptyID__c = "",
"",
HYPERLINK(
"/006" &
CASE(MID(HASH_Latest_Opty_Won_OptyID__c, 1, 1), "0", "130000", "1", "6A0000", "XXXXXX") &
MID("ABCDEFGHIJKLMNOPQRSTUVWXYZabcdefghijklmnopqrstuvwxyz0123456789", VALUE(MID(HASH_Latest_Opty_Won_OptyID__c, 2, 2)), 1) &
MID("ABCDEFGHIJKLMNOPQRSTUVWXYZabcdefghijklmnopqrstuvwxyz0123456789", VALUE(MID(HASH_Latest_Opty_Won_OptyID__c, 4, 2)), 1) &
MID("ABCDEFGHIJKLMNOPQRSTUVWXYZabcdefghijklmnopqrstuvwxyz0123456789", VALUE(MID(HASH_Latest_Opty_Won_OptyID__c, 6, 2)), 1) &
MID("ABCDEFGHIJKLMNOPQRSTUVWXYZabcdefghijklmnopqrstuvwxyz0123456789", VALUE(MID(HASH_Latest_Opty_Won_OptyID__c, 8, 2)), 1) &
MID("ABCDEFGHIJKLMNOPQRSTUVWXYZabcdefghijklmnopqrstuvwxyz0123456789", VALUE(MID(HASH_Latest_Opty_Won_OptyID__c, 10, 2)), 1) &
MID("ABCDEFGHIJKLMNOPQRSTUVWXYZabcdefghijklmnopqrstuvwxyz0123456789", VALUE(MID(HASH_Latest_Opty_Won_OptyID__c, 12, 2)), 1),
Name & "'s Latest Won Opty"))
Kudotek v2.0 