Pulling the latest timeline notes for a specific activity type

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. 🙂

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.

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.

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):

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?

That's correct, Sagan. The update of the data could be at most frequent, every 2 hours, based on rule scheduling.

Thanks Dan! This should be a fun one to implement, always appreciate your help.

Hey Dan, on the initial fetch of the latest activity, if I don't include a filter (aka I want to pull the latest activity regardless of the type) with the three fields being Account ID, Activity ID and MAX of Activity Date it brings in multiple records per account. Is that because I'm including the Activity ID? I do that in order to bring additional activity information.

Hi Shane,

Sorry for the slow reply - I was out on PTO last week.

Yes, the reason you are getting more than one record per Account ID is because you are including the Activity ID, which makes your "uniqueness criteria" for looking for MAX of Activity Date the combination of Account ID and Activity ID.

In that first fetch, just include the Account ID, so you get one record per account ID.

That's why in my example I have a second fetch for grabbing the rest of the details of the specific activity (including the activity ID). Then merge this back with the oldest record with a left join where account ID and date/time stamp match. This will allow you to now have the activity ID (and other details) for the single record per account that you pulled in the first merge.

Sorry if this seems a little tricky, but data management when you're doing aggregations, merges and transformations needs to be very precise otherwise you'll get the wrong data. Ironically, the computer is very good at doing EXACTLY what you tell it - even if what you tell it isn't really what you intended to tell it. ;-)

@shane_mccoy  @dan_ahrens I have a similar request. Can we use “Embed page” to pull a subset of timeline with constraints such that it only show the latest entry on a specific type of activity? Have either of you tried anything like that?

A report layout is different from the timeline layout which is what we would like add to the c360 so our exec team does not have to search for it.