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,438 Points 20k badge 2x thumb
There are a few different ways to do this:
1- Reverse Relationship (Not Recommended)
2- Use a combo of formula fields to pass the value of that activity type back to the parent
3- Script code page to display the value.

What level would you like, and I can explain in more detail?
Photo of Daniel Brown

Daniel Brown

  • 222 Points 100 badge 2x thumb
Let's go with Option #2.
Photo of Fendall Marbury

Fendall Marbury

  • 64 Points
Is there a response for Option #2?  I'd love to see how to do this.  Thanks.
Photo of Matthew Neil

Matthew Neil

  • 31,438 Points 20k badge 2x thumb
Is the "activity name" a multiple choice field? If so, what are the options? Then I can give the real answer using your situation. (Sorry for the delay)
Photo of Daniel Brown

Daniel Brown

  • 222 Points 100 badge 2x thumb
How about these Matthew?

Opportunity Received
Initial E-mail Sent
Contract Terms Negotiated
Contract Emailed for Review
Signed Contract Received
Setup Complete
Photo of QuickBaseCoach App Dev./Training

QuickBaseCoach App Dev./Training, Champion

  • 51,456 Points 50k badge 2x thumb
i guess that Matthew is doing real work and not on the forum. i should be too. :)

I would do this.

Summarize the Maximum Activity date up to the opportunity.
Look that up down to the activities.
Summarize the maximum [Record ID#] where the [Activity date] is equal to the value in the field [maximum activity date]. call is [Record ID# of most Recent activity]

Great, now the Opportunity knows the record ID that has the info it wants.

Now do a reverse relationship where 1 Activity has many Opportunities.  On the right hand side, select the field called [Record ID# of most Recent activity].

Then add a lookup field for any of that child records data that you want to float, such as the Activity Name or the [Activity Comments]
Photo of Matthew Neil

Matthew Neil

  • 31,438 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,438 Points 20k badge 2x thumb
THIS IS NOT A REVERSE RELATIONSHIP.... That is a bad word to me.
Photo of Daniel Brown

Daniel Brown

  • 222 Points 100 badge 2x thumb
Thanks for the solutions, guys.  Once I finish fighting fires, I will implement this.  If I run into any problems, I will indeed hit you-all up.

Your magic will make me a wizard to my people. :-D
Photo of Daniel Brown

Daniel Brown

  • 222 Points 100 badge 2x thumb
Got the solution up and running.  I pretty much wouldn't have figured that out on my own.  But now that I have that pattern, I can probably use it somewhere else later.  Thanks Again!
Photo of Matthew Neil

Matthew Neil

  • 31,418 Points 20k badge 2x thumb
Glad to hear you got it working.  Its a pretty nifty work around, just be sure to keep an eye on those multiple choice fields, so if somebody every changes them, they'll need to know to update your numeric formulas that are passing the data.

Cheers
Photo of QuickBaseCoach App Dev./Training

QuickBaseCoach App Dev./Training, Champion

  • 51,456 Points 50k badge 2x thumb
QuickBase Product Development Managers know that this is on our collective wish lists.  The know that that the technical aspects of creating reverse relationships is not intuitive and also it places extra load on their system to calculate the loops.  It would be nice to be able to summarize a text fields up to the parent.