How to create a report on comments from approval table

[lkm]

I have created a report from Approval [Sysapproval_approver] table. I want all comments including history comments only for change requests. I have tried adding filter using sysapproval_approver where starts with CHG% but that doesn't work to only get changes. I tried approval journal column is not empty and that didn't return anything either trying to get comments that are populated with data. Can someone tell me what I am doing wrong?

Thanks,

Linda

[Dennis R]

I'm not sure what you mean by "comments".   Are you referring to journal log entries?   If so, those are actually stored in a separate table called sys_journal_field (labeled Journal Entries).   There's not really a good way to report against it directly, since the ID field stored in it isn't a reference field.   There is a way, though, but it's a bit on the advanced side.   You'll need to create a database view.   Here's a step-by-step:

• Go to Database Views in your navigator and select New.
• Give the view a name, like u_change_comments, and a label such as Change Comments, and click Submit.
• Open the view back up, and you should see a View Tables related list down at the bottom.   Click on New.
• Select Change Request [change_request] as your table name, give it a variable prefix like cr, and Submit it. (Leave the where clause field blank.)
• In the View Tables related list, click on New again.
• Select Journal Entry [sys_journal_field] as your Table, and give it a variable prefix like j.   In the Where clause field, enter the following, and then Submit it.
  • j.element_id = cr.sys_id AND (j.element = 'comments' OR j.element = 'work_notes')

Now you can run reports against the Change Comments view (which should show up anywhere a table would).   You'll have access to all change request fields (like number, short_description, etc.), plus some extra fields in the sys_journal table.   The Element field will tell you whether the comment is a work note or a comment, and the Value field will be the actual comment.   You can filter blank ones out by filtering on Value is not empty.

Hope this helps, let me know if it meets your needs.   If so, please mark the answer correct of helpful.  

[Kalaiarasan Pus]

There is a comment field on approval record ?

[Dennis R]

Maybe, I'm not sure what comments she's referring to.   If she is referring to comments on the approval, that's easily enough to account for, just use sysapproval_approver instead of change_request as the table:

• Go to Database Views in your navigator and select New.
• Give the view a name, like u_approval_comments, and a label such as Approval Comments, and click Submit.
• Open the view back up, and you should see a View Tables related list down at the bottom.   Click on New.
• Select Approval [sysapproval_approver] as your table name, give it a variable prefix like a, and Submit it. (Leave the where clause field blank.)
• In the View Tables related list, click on New again.
• Select Journal Entry [sys_journal_field] as your Table, and give it a variable prefix like j.   In the Where clause field, enter the following, and then Submit it.
  • j.element_id = a.sys_id

[lkm]

I created the database view but the comments don't show up.

[Dennis R · Accepted solution]

Add the field named "Value", that is where the comment is stored.

I know that's a bit weird, but the technical reason is because you actually want to look at the Value field of the journal table, not the Comments field of the approval table, which will always be blank in reports.

You might want to also add the "Approval for" field; that's the change number for which the comment is applicable.