Records on one table but not another

This is a pretty common use case, so I thought I would quickly write it up.

The use case is: suppose you want to report on the number of changes without an associated approval, or want to report on the number of applications without mapped servers.

You need to compare two tables for the same key field, and find where the key is on one table or another.

For this example, we'll use 'changes without associated approvals', but the same steps would apply for any similar case.


Here's how you do it:

1) Create a report with the Approvals, grouped by the associated change request:

(note: I relabelled the 'Approval For' field to 'Number' just for this report, for the mash-up to work)

Screenshot 2017-04-24 09.29.11.png

2) Create a report with all of the records on the change request table:

Screenshot 2017-04-24 09.29.54.png

3) Create a 'Mashup' view by selecting those two reports:

Screenshot 2017-04-24 09.30.20.png


Now you'll have a report that shows all of the changes, with the number of approvals (if any):

Screenshot 2017-04-24 09.30.59.png

With a right-click, you can filter on only the records that have no approvals:

Screenshot 2017-04-24 09.30.37.png

Screenshot 2017-04-24 09.30.48.png


Now you can see any of the records on table A (change_request) that are not on table B (sysapproval_approver).


Please sign in to leave a comment.