How to UNION data from approval and task tables?

[chuckh_]

Hi all,

      Auditors are looking for a one-stop view into the details of our changes, which includes change details coming from both the change task and the approval tables.   In short, we need to union rows from each of the two tables to chronologically show the work and the approvals that occurred for each change.   I have a sample query below which runs against an ODBC connection, but I need to know if something more efficient can be created 'within' the tool so that a scheduled report can be run and delivered instead of running/exporting SQL query results.

Query:

SELECT ct.dv_change_request AS ChangeNbr,
      'Task' as Type,
      ct.short_description AS Acty,
      ct.dv_state AS Status,
      convert(ct.closed_at,timestamp) AS Timestp,
      ct.dv_closed_by AS Auth_By,
      su.email as EMail
FROM change_task ct,
      sys_user su
  WHERE su.sys_id = ct.closed_by AND ct.dv_change_request='CHGXXXXXXX'

UNION ALL

SELECT sa.dv_sysapproval AS ChangeNbr,
      'Appr' as Type,
      sa.u_activity AS Acty,
      sa.dv_state AS Status,
      convert(sa.sys_updated_on,timestamp) AS Timestp,
      sa.dv_approver AS Auth_By,
      su.email as EMail
FROM sysapproval_approver sa,
                      sys_user su
WHERE su.sys_id = sa.approver AND sa.dv_sysapproval='CHGXXXXXXX' AND sa.dv_state='Approved'

ORDER BY 5;

Has anybody had to do something similar?   If so, how did you accomplish it?

Thanks in advance,

      Chuck H.

[drjohnchun]

Can you try running this in Background Script:

var gr = new GlideRecord('change_request');
gr.query('number', 'CHG0031322');
if (gr.next()) gs.info(gr.approval_history.getDisplayValue());

How many approvals do you see?

[chuckh_]

Sorry so late---I don't have all perms in prod and needed assistance....   and thanks again....

It appears to only show the last approval....

[drjohnchun]

No worries - sorry, Background Script requires an elevated role. That output is disappointing, but confirms what you've been getting so far on your side.

Which release are you on?

[chuckh_]

Appreciate your time and help.

We are on Helsinki

[drjohnchun]

Here's how Approval History looks like in Helsinki OOB, showing both approval and rejection:

1. Approval History added to Change form - I grabbed only the last 4 approvals (I approved 2 as admin and the other 2 by impersonation)
   
2. Output from Background Script
   

So it's still not clear to me why in your case it only shows the last approval. You might check the 2 things from my earlier reply below if you still want to use Approval History. Other than that, I'm not sure what else might be causing it.

FYI - the above output does NOT show comments, although the BR adds it; we can look into it later once you get this working first.