Pulling the latest timeline notes for a specific activity type

  • 1
  • Question
  • Updated 4 days ago
I'm attempting to create a report that pulls in company data along with activity timeline data that would show the notes for the latest activity of type 'X'. In order to get the maximum activity date for that specific activity type, there has to be a 'by' clause but then that prevents one from bringing in any string fields, specifically the notes field, which I realize can't be included in a report if there's a 'by' statement.

My use case for this is that i'm trying to replicate the customers tab in a report, since that tab will soon be deprecated.

Thanks for any information you may have.
Photo of Shane McCoy

Shane McCoy

  • 356 Points 250 badge 2x thumb

Posted 7 days ago

  • 1
Photo of Sagan Sherlin

Sagan Sherlin, Champion

  • 4,784 Points 4k badge 2x thumb
I have a CSM with a similar ask.  Wanting a report of only her [latest] Timeline Entries - for the accounts she is posting on.  I've been able to create the report that shows me her last entry for an account - but as soon as I add Subject, it includes all entries - rather than the latest [max].  Following this post. :)
Photo of Heather Hansen

Heather Hansen, Champion

  • 9,382 Points 5k badge 2x thumb
I got around this by adding some fields to Customer Info.  For example, we have a Training activity type, so I created a field called Last Training, and I'm using the Rules Engine to populate that field based on the last date an activity of type Training was logged.  Then, I can display this value in reports or currently the Customer's tab.
Photo of Shane McCoy

Shane McCoy

  • 356 Points 250 badge 2x thumb
This is useful. However, in addition to the knowing when the last training was, I'd like to see any comments that the CSM had about it.
Photo of Dan Ahrens

Dan Ahrens, Official Rep

  • 25,920 Points 20k badge 2x thumb
Hi Shane, in this case you would need to accomplish this via a Bionic rule and an MDA table, as every additional field you add in the "by" area is used to create a composite selection for what is considered unique. 

Here's what I mean, if you ask the report to Show me: Max of Activity Date, By: Account Name, you'll get one record per Account Name. 

But if you ask the report to Show me: Max of Activity Date, By: Account Name, and Subject, you'll get one record per unique combination of Account Name AND Subject, which will most surely be more than one record per Account.

Add any other fields to the By: area and the idea of pulling the most recent data becomes untenable.



What you need to do in the Bionic rule is first fetch the most recent timeline entry (filtered for the type of activity you need) for each account with a simple fetch that pulls Account ID and Max of Activity Date (with appropriate filters). This will give you one date per Account ID. Then fetch the details that you want to appear in your data in another data task. Merge those two together. Then optionally perform another fetch to pull in all accounts (if you want to show blank values for accounts that did not have any entries of the type you want to show), and then merge that all account data with your merged data from step 3.

It would look something like this (fetch newest instead of oldest in this example):


Photo of Sagan Sherlin

Sagan Sherlin, Champion

  • 4,784 Points 4k badge 2x thumb
I do enjoy how you come up with solutions Dan!  I have a question - would this then be turned into a 'report' that can be seen on a Dashboard, but not updated in Real Time, but only updated every 2 hours?  
Photo of Dan Ahrens

Dan Ahrens, Official Rep

  • 25,920 Points 20k badge 2x thumb
That's correct, Sagan. The update of the data could be at most frequent, every 2 hours, based on rule scheduling. 
Photo of Shane McCoy

Shane McCoy

  • 356 Points 250 badge 2x thumb
Thanks Dan! This should be a fun one to implement, always appreciate your help.