Show Last Item from Many Relationship in Report

  • 0
  • 3
  • Question
  • Updated 2 years ago
  • Acknowledged
We are creating a sales pipeline type tool where one Opportunity has many Activities.  On the main report for the Opportunities, I show the # of Activities for a given opportunity.  I've figured out how to show the date of the last modified Activity using a Date / Time (summary) field set to Maximum.  However, I can't figure out how to show the Activity Name for the last modified.  Given this data:

Date Modified | Activity Name
3/1/2017 | Opportunity Received
3/3/2017 | Contacted Client
3/8/2017 | Contract Sent

I can display "3/8/2017", but I want "Contract Sent" displayed in Opportunities Report as the last Activity for the given Opportunity.  Is there a way to do that?
Photo of Daniel Brown

Daniel Brown

  • 222 Points 100 badge 2x thumb
  • Frustrated

Posted 2 years ago

  • 0
  • 3
Photo of Matthew Neil

Matthew Neil

  • 31,678 Points 20k badge 2x thumb
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
Photo of Matthew Neil

Matthew Neil

  • 31,678 Points 20k badge 2x thumb
THIS IS NOT A REVERSE RELATIONSHIP.... That is a bad word to me.