Is there a way to filter Approval (sysapproval_approval) records based on the Approving (document_id) without a scripted filter?

[eurban]

We are looking for a way to only show approvals (sysapproval_approver) for Knowledge articles that are in specific workflow states in the approval list view.   For example, we want to only display approvals for articles that are in the state of review and not show articles in a state of pending retired.

I was able to get something working using a script include filter on the Approving column, but was wondering if it is possible to get this to work without the scripted filter?

The Approving field on the approval is a Document ID type and looks to be a reference to the knowledge article.   The filters in the list view do not have any reference fields on the Approving column.   I tried adding the source table as I have seen that this is either a good idea or necessary to include when working with a Document ID type.

On my personal developer instance I tried creating a URL with the query in place using a filter on the source_table field and a filter on the number field of a knowledge article (KB0010009).   Here is the URL that isn't working as can be seen in the screenshot result below:

https://dev12345.service-now.com/nav_to.do?uri=/sysapproval_approver_list.do%3Fsysparm_query%3Dstate...

[damodarreddyp]

hi Eric,

'document_id' Field on Approval table is of type 'Document ID', so this should be having sys_id of the Approving record, that is sys_of Knowledge Base.. so give sys_of of 'KBxxxxx' in your filter, that should work.

With regards,

Damodar

[eurban]

Hello Damodar, thank you for your response.   I should have clarified I am looking for a way to filter that would be more easily understood by our end users like on the document_id.workflow_state field where you could do something like document_id.workflow_state='review'.   The scripted filter I have returns an array of sys_ids to filter on the Approving column so does work in a way similar to the suggestion you made.

One of the reasons for my question is that I saw the response marked as the correct answer at https://community.servicenow.com/message/816661#816661 includes a query that has the document_id in it, which is:

var queryString='source_table=u_screening_and_event_requests^state=requested';^document_id.u_event_date>javascript:gs.daysAgoEnd(1)';

I am thinking the fact they have the document id in query as they have done is a red herring and is not actually working the way it is believed to be.   The query appears to have a typo too.   If the semi-colon and quote are taken out of the center of the line I think they are getting the results they want because of the while loop in their script.   That loop uses the sys_ids of the document_ids and goes to the targeted table to filter there so would work similar to how my current scripted filter does.

[eurban]

We ended up working around this in two ways.

The first was to use a scripted filter.   Yes this isn't really a correct answer to my question since I asked "without a scripted filter" but I thought I would post it anyway.   I then used this script as a filter on the Approving (document_id) field.   I created a script include with the following function.   It should be easy enough to make this even more reusable by allowing a third argument that could be the source_table value.

/**
   * Get knowledge article sys_ids based on specified workflow_state.
   * For use in the Approving (document_id) column.
   *
   * @param field - desired document_id field, e.g. workflow_state
   * @param value - desired document_id value, e.g. 'review'
   * @return - array of sys_ids of KB articles, or empty array for no results
   */
   getRequestedKbByFieldValue : function(field, value) {   

         var knowledgeArticles = [];
         var gr = new GlideAggregate('sysapproval_approver');
         // limit to requested approvals
         var queryString = 'source_table=kb_knowledge^state=requested';
         gr.addEncodedQuery(queryString);
         // We only need distinct KB article numbers, so will do a group-by
         gr.groupBy('document_id');
         gr.query();

         while (gr.next()) {
               var gr_know = new GlideRecord('kb_knowledge');
               gr_know.addQuery('sys_id', gr.document_id.toString());
               gr_know.addQuery(field, value);
               gr_know.query();
               if(gr_know.next())
               {
                     knowledgeArticles.push(gr_know.sys_id.toString());
               }
         }

         return knowledgeArticles;

   },

The second way we worked around this was by creating a business rule to populate a user-defined column on the Approvals table.   The business rule acts on the kb_knowledge table and I needed to make it an asynchronous rule to introduce just enough delay to to allow the approvals to be created.   Otherwise using just an 'after' rule wouldn't populate the approvals.   I used some debug statements and it looked like the approvals were not created quickly enough.

The business rule is as follows with u_description being the user-defined column on our Approvals table.   For the condition I made sure that this only runs for workflow_state values that are accompanied by an approval.

(function executeRule(current, previous /*null when async*/) {

   var kba_approval_title = '';

   var gr_approval = new GlideRecord('sysapproval_approver');
   // only update approvals in the requested state
   gr_approval.addQuery('state', 'requested');
   gr_approval.addQuery('document_id', current.sys_id);
   // limit our query to approvals created today
   gr_approval.addEncodedQuery('sys_created_onONToday@javascript:gs.daysAgoStart(0)@javascript:gs.daysAgoEnd(0)');
   gr_approval.query();

   while(gr_approval.next()) {
         kba_approval_title = "KB: '" + current.kb_knowledge_base.getDisplayValue() + 
                                                   "', WS: '" + current.workflow_state.getDisplayValue() + "'";
         gr_approval.u_description = kba_approval_title;
         gr_approval.setWorkflow(false);
         gr_approval.update();
   }

})(current, previous);

Users can now filter on the u_description field.   The one problem with this workaround is that there is a delay in populating the field since it is an asynchronous call.   However, I believe the maximum amount of time that would pass is one minute and on average it should be much quicker than that.

Hope this helps anyone who may encounter this same situation.