I have created a database view between the task table and the sys_journal_field table so that I could report on the comment field for things such as who made the last comment (caller, assigned to or watch/work notes list), how long has it been since a comment was made, etc. Below are my settings for the database view:
Database View Name : u_task_comment_recording
View Table1
Task
Task View Fields
active, assigned_to, assignment_group, comments, number, opened_by, sys_class_name, sys_created_by, sys_id, sys_updated_by, sys_updated_on, u_caller, u_requester, watch_list, work_notes, work_notes_list
Where Clause
tsk_sys_id=jrnl_element_id
View Table2
sys_journal_field
Journal Entry View Fields
element, element_id, name, sys_created_by, sys_created_on, sys_id, sys_tags, value
Where Clause
jrnl_element_id=tsk_sys_id
I was able to get results, however, the results show multiple entries for a single task. Instead of seeing only the last comment for a single task, it is showing more than one comment for a single task. The report will show 60 results, however, there are only a total of 22 actual task requests. Please see the screenshot below. (Disregard the report title as I was using a previously created metric as a template.)
Report Conditions
Report Results
I wanted to follow up with the solution I came up with in accordance to Adam Stout's nudge/recommendation of a different way to solve my problem. I thought it might help someone else in the future solve the same or similar problem.
TASK TABLE NEW DICTIONARY ENTRIES
1. Type Tables in the Search Filter Navigator and click on Tables module under the System Definition Application Menu
2. Select Name and in the search box type =task and hit enter or return on your keyboard
3. Click on the Task list item and the table screen will populate all current table columns on the Task table.
4. Click on the New Button next to Task Columns
I created the below new table columns, (dictionary entries), one at a time.
a. Task table "New Dictionary Entry" column 1
Type : Reference
Column Label : Last Commented - Additional Comments
Column Name : u_last_commented_additional_comments
Under Reference Specification Tab
Reference : User
Use reference qualifier : Simple
b. Task table "New" column 2 named : Last Commented : Work Notes
Type : Reference
Column Label : Last Commented - Work Notes
Column Name : u_last_commented_work_notes
Read Only : Checkbox Selected
Under Reference Specification Tab
Reference : User
Use reference qualifier : Simple
c. Task table "New" column 3 named : Time Last Commented : Additional Comments
Type : Date/Time
Column Label : Time Last Commented - Additional Comments
Column Name : u_time_last_commented_additional_comments
Read Only : Checkbox Selected
d. Task table "New" column 4 named : Time Last Commented : Work Notes
Type : Date/Time
Column Label : Time Last Commented - Work Notes
Column Name : u_time_last_commented_work_notes
Read Only : Checkbox Selected
ADD NEW TABLE FIELDS TO TABLE FORMS
Since I am creating these fields to be added to the Facilities Request and the Requested Item tables, I then went to the form design for each of those tables and added the four new fields onto each table's form. You might be wondering why I initially created these four new columns on the Task table instead of their respective Facilities Request and Requested Item table. If you remember most all tables are directly extended from the Task table (parent). Therefore, when I added these new columns/fields to the Task table, they are automatically inherited on each child table that is extended which would make the relationship the parent's child. I now no longer have to go and create these four new fields on the Facilities Request, Request and Requested Item tables. This also allows me to run reports directly from the task table if I need to to incorporate overall metrics for various tables I might need to track. I repeated steps 2 and 3 below so I could add the new fields to the two separate table forms.
1. Right Click on the Task table header and choose Configure > Form Design. A new window will open to show the Form Design window.
2. Click on the Dropdown Box in the upper left corner labeled "Table [sys_db_object]" and choose whichever table you are needing to add a field on the table form. In my case it will be the Facilities Request and Requested Item table.
3. Find your new fields under the Fields section on the left sidebar and drag them over to whichever form section you want to place them.
SCRIPTING SECTION ON HOW TO DYNAMICALLY UPDATE THESE NEW FIELDS
Since we use two main tables in our instance, Facilities Request [facilities_request] and Requested Item [sc_req_item], I created two business rules, one for each table. One business rule is updating the Additional Comments fields we just made and one for the Work Notes fields we just made. Therefore, I have 4 separate business rules. One to track the who made the last additional c comment,
1. Go back to your ServiceNow instance (Exit the Form Design window or tab that opened up when you clicked to configure form design above).
2. Type business rules in the Seach Navigator in the top left corner and then click on Business Rules under System Definition.
3. Click on the "New" button next to Business Rules
4. BR1 - Track Who/When Recent Comment Was Made
a. Name: FCR - Track Recent Comment <-- Or whatever you want to call this Business Rule
b. Table: Facilities Request [facilities_request] <-- Select which table this business rule will apply to. My case is Facilities Request.
c. When To Run Tab
i. When: after
ii. Insert and Update boxes Checked
d. Actions Tab
i. Advanced box Checked
e. Advanced Tab
i. Condition
current.comments.changes()ii. Script
(function executeRule(current, previous /*null when async*/) {
var ladComm = gs.getUserName();
current.u_last_commented_additional_comments = ladComm;
var timeLadcomm = gs.nowDateTime();
current.u_time_last_commented_additional_comments = timeLadcomm;
current.update();
})(current, previous);f. Click Update to save the Business Rule
5. BR2 - Track Who/When Recent Work Note Was Made
a. Name: FCR -Track Who/When Recent Work Note Was Made <-- Or whatever you want to call this Business Rule
b. Table: Facilities Request [facilities_request] <-- Select which table this business rule will apply to. My case is Facilities Request.
c. When To Run Tab
i. When: after
ii. Insert and Update boxes Checked
d. Actions Tab
i. Advanced box Checked
e. Advanced Tab
i. Condition
current.work_notes.changes()ii. Script
(function executeRule(current, previous /*null when async*/) {
var lastWknote = gs.getUserName();
current.u_last_commented_work_notes = lastWknote;
var timeLastwknote = gs.nowDateTime();
current.u_time_last_commented_work_notes = timeLastwknote;
current.update();
})(current, previous);f. Click Update to save the Business Rule
I repeated the creation of the above two business rules but for the Requested Item table. Therefore, I just opened up one of the Business Rules I just created, changed the name, changed the table to Requested Item and then right clicked in the gray table header and chose Insert and Stay. Doing this allows you to keep the original Business Rule and creates a new one with your changes.
NOTES
Now whenever a new comment or work note is made, it will update the record fields with who made the last comment or work note and timestamp the according time fields. I've attached a screenshot of the form showing the dynamic updates of the fields we created.
My original post was inquiring about how to report against the journal table which is where the comments and work notes are stored. I can now report directly from the Task table to compare if who made the last comment is now the same or different than who the request is assigned to. I can go further by also tracking how long since the assigned to has updated the caller or requestor.
I hope this possibly helps someone with a similar need.
