Discussions

 View Only
Expand all | Collapse all

Show Last Item from Many Relationship in Report

  • 1.  Show Last Item from Many Relationship in Report

    Posted 03-14-2017 20:00
    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?


  • 2.  RE: Show Last Item from Many Relationship in Report

    Posted 03-14-2017 20:05
    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?


  • 3.  RE: Show Last Item from Many Relationship in Report

    Posted 03-14-2017 20:38
    Let's go with Option #2.


  • 4.  RE: Show Last Item from Many Relationship in Report

    Posted 03-16-2017 13:44
    Is there a response for Option #2?  I'd love to see how to do this.  Thanks.


  • 5.  RE: Show Last Item from Many Relationship in Report

    Posted 03-16-2017 15:17
    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)


  • 6.  RE: Show Last Item from Many Relationship in Report

    Posted 03-16-2017 18:55
    How about these Matthew?

    Opportunity Received
    Initial E-mail Sent
    Contract Terms Negotiated
    Contract Emailed for Review
    Signed Contract Received
    Setup Complete


  • 7.  RE: Show Last Item from Many Relationship in Report

    Posted 03-16-2017 20:39
    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]


  • 8.  RE: Show Last Item from Many Relationship in Report

    Posted 03-16-2017 20:51
    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


  • 9.  RE: Show Last Item from Many Relationship in Report

    Posted 03-16-2017 20:56
    THIS IS NOT A REVERSE RELATIONSHIP.... That is a bad word to me.


  • 10.  RE: Show Last Item from Many Relationship in Report

    Posted 03-17-2017 19:36
    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


  • 11.  RE: Show Last Item from Many Relationship in Report

    Posted 03-17-2017 20:59
    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!


  • 12.  RE: Show Last Item from Many Relationship in Report

    Posted 03-21-2017 02:58
    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


  • 13.  RE: Show Last Item from Many Relationship in Report

    Posted 03-17-2017 21:29
    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.