approval_summarizer_sc_request approval summarizer and glide queries

[jamesmcwhinney]

It seems the default approval summarizer for sc_request doesn't show the approval comments.

I am trying to address this but am struggling to create the query.

How would I go about performing the following query using the gliderecord class?

select * from sysapproval_approver where sysapproval IN (SELECT sys_id from sc_req_item WHERE request = targetrequestnumber)

Thanks!

- James

[siva_dirisala]

Hello James, instead of that, you could query individually for each request. Given that there is already a loop with sc_req_item glide record, you can just get the approval info for each req item using that loop.

[jamesmcwhinney]

Thanks Siva, I suppose I was just hoping for a more elegant solution.

I am using the below for now:

var approval = new GlideRecord('sysapproval_approver');

approval.addQuery('sysapproval', sc_request.sys_id);

var sc_req_item2 = new GlideRecord('sc_req_item');

sc_req_item2.addQuery('request', sc_request.sys_id);

sc_req_item2.query();

while (sc_req_item2.next()) {

    approval.addOrCondition('sysapproval', sc_req_item2.sys_id);

}

approval.orderBy('sys_updated_on');

approval.query();

[jamesmcwhinney · Accepted solution]

For anyone else trying to develop a UI macro for summarizing approvals related to either a sc_request, sc_req_item or sysapproval_approver, here is what I ended up with:

<?xml version="1.0" encoding="utf-8" ?>
<j:jelly trim="true" xmlns:j="jelly:core" xmlns:g="glide" xmlns:j2="null" xmlns:g2="null">
<g:evaluate jelly="true">      
             
  var approval = new GlideRecord('sysapproval_approver');
  approval.addQuery('state', '!=', 'not_required');
 
  var recordUnderApproval;
  var recordType;
  var typeIdentified = false;
 
  //Try Get Request Record
  var gr = new GlideRecord('sc_request');
  if(gr.get(jelly.sysparm_sys_id)){
    recordUnderApproval = gr;
    recordType = 'sc_request';
    typeIdentified = true;
  }

  if(typeIdentified == false){
    //Approval Record for sc_request or sc_req_item
    recordUnderApproval = ${ref}.sysapproval;
    recordType = ${ref}.sysapproval.sys_class_name.trim();
  }
 
  var recCondition = approval.addQuery('sysapproval', recordUnderApproval.sys_id);
  if(recordType == 'sc_request'){
    var sc_req_item2 = new GlideRecord('sc_req_item');
    sc_req_item2.addQuery('request', recordUnderApproval.sys_id);
    sc_req_item2.query();
    while (sc_req_item2.next()) {
      recCondition.addOrCondition('sysapproval', sc_req_item2.sys_id);
    }
  } else if(recordType == 'sc_req_item'){
    var sc_req_item3 = new GlideRecord('sc_req_item');
    if(sc_req_item3.get(recordUnderApproval.sys_id)){
      recordType = sc_req_item3.request.sys_id;
      recCondition.addOrCondition('sysapproval', sc_req_item3.request.sys_id);
    }
  }
     
  approval.orderBy('sys_updated_on');
              approval.query();
 
              </g:evaluate>
     
<table class="table" width="100%">
  <thead>
    <tr>
      <th>
        ${gs.getMessage('Approval Date')}
      </th>
      <th>
        ${gs.getMessage('Approval of')}
      </th>
      <th>
        ${gs.getMessage('Approver Name')}
      </th>
      <th>
        ${gs.getMessage('State')}
      </th>
      <th>
        ${gs.getMessage('Approver Comments')}
      </th>
    </tr>
  </thead>
  <tbody>
    <j:while test="${approval.next()}">
      <tr class="${jvar_line_color}">
        <td valign="top"> ${approval.sys_updated_on}</td>
        <j:if test="${approval.sysapproval.sys_class_name == 'sc_req_item'}">
        <td valign="top"> ${approval.sysapproval.short_description}</td>
        </j:if>
        <j:if test="${approval.sysapproval.sys_class_name != 'sc_req_item'}">
          <td valign="top"> ${approval.sysapproval.sys_class_name.getDisplayValue()}</td>
        </j:if>
        <td valign="top"> ${approval.approver.name}</td>
        <td valign="top"> ${approval.state.getDisplayValue()}</td>
        <td valign="top"> ${approval.comments.getJournalEntry(1)}</td>
      </tr>          
    </j:while>
  </tbody>
</table>

</j:jelly>

[jamesmcwhinney]

and here is the end of the UI macro approval_summarizer_sc_request such that it now includes a summary of the approvals:

<tr>

              <td width="100%"><h3>

                      ${gs.getMessage('Approval History')}:

    </h3>

                      <g:label_spacing />

              </td>

      </tr>

<tr>

  <td width="100%">

    <g:macro_invoke macro="my_approval_history_ui_macro" />

  </td>

      </tr>

</j:jelly>