Query Activity Log for Related List

[mwascak]

I have a request to be able to pull a related list of everything a user has commented or work-noted on for the past "X" days and I cant seem to find a way to query the activity log effectively. Has anyone done anything similar before?

[Jim Coyne]

I think this does what you are looking for, at least for any of the Task derived tables. There are 2 parts to the solution, a Database View and a Defined Related List.

First off, we need to create a Database View, linking the Task and the Journal Entry tables:




When adding the Task table to the list of View Tables, just add the Task Type (sys_class_name) and the Number (number) fields to the list of View Fields. This will limit the view to only those 2 fields from the Task table:



Then, we need to create the Relationship (System Definition \ Relationships):



Here's the Query with code:

(function(){
   current.addQuery("j_sys_created_by", parent.user_name);
   current.addEncodedQuery("j_sys_created_onONLast 30 days@javascript:gs.daysAgoStart(30)@javascript:gs.daysAgoEnd(0)");
})();

This will link the user record to the view using the User ID field (first line). The second line adds the last 30 days filter.

You can check it out running in the demo022 instance. Take a look at the System Administrator's user record (the default view):

[asset|aid=1237|format=image|formatter=asset|title=Community_RecentCommentsRelatedList1242134.jpg|width=964|height=190|resizable=true|align=none]

You can click on the Number link and it will open the appropriate record. Not sure how this will affect performance, as those 2 tables will likely be very big. I've also included an importable update set.

[mwascak]

Jim this is great, I never even thought about a Database View. Thank you tremendously, I owe you a beer.

[mwascak]

I'm still not quite done picking your brain Jim. The query definitely works for created on, I am more looking for not who and when they created it but when they simply updated it. Lets say User A gets a ticket assigned to him he does a bit of work and then the ticket is transferred, I need the ability to to track that that User A DID in fact do some work on that ticket( despite the fact that they are no longer the assigned to or the creator of the ticket) in an end of week report.

I attempted with the below relationship but it seems to have given me a giant list of 1000+ entries.

(function(){
   current.addQuery("j_sys_created_by", parent.user_name);
   current.addEncodedQuery("j_sys_updated_onONThis week@javascript:gs.beginningOfThisWeek()@javascript:gs.endOfThisWeek()");
})();

[Jim Coyne]

OK, so not restricted to the journal fields. That would be different. The created_on works for journal fields because they are only ever created and not updated, they just keep getting added to the journal.

I'll have to think about that one and get back to you.