Alright, This is going to be a long one, so strap in.
The basic overview is create a field that translate the "Status" to a numeric value, then summarize that value to the parent, then translate it back to the text status.
You will also need to pass the max record ID# back and forth to get it.
Step 1:
Summarize the Maximum Record ID# from the Activity Table. Call it [Max Activity Record ID#]
Step 2:
Pass the [Max Activity Record ID#] back to the activity table as a lookup, it will automatically name it something like [Opportunity - Max Activity Record ID#]
Step 3:
Create a Formula-Numeric field called [Activity Status Number] on the activity table
Step 4:
Use this formula, assuming your "activity status" field is called such
Case([Activity Status],
"Opportunity Received", 1,
"Initial E-mail Sent", 2,
"Contract Terms Negotiated", 3,
"Contract Emailed for Review", 4,
"Signed Contract Received", 5,
"Setup Complete", 6,
null)
Step 5:
Summarize the "Total" of [Activity Status Number] with a condition of;
Where [Opportunity - Max Activity Record ID#] is equal to [Record ID#]
Call the field [Latest Activity Status Number]
****You now have the number value for that most recent activity status, now convert that back to text****
Step 6:
Create a Formula-Text field called [Activity Status] on the opportunity table
Step 7:
Use this formula;
Case([Latest Activity Status Number],
1, "Opportunity Received",
2, "Initial E-mail Sent",
3, "Contract Terms Negotiated",
4, "Contract Emailed for Review",
5, "Signed Contract Received",
6, "Setup Complete",
"")
Step 8:
Like this post
