Report on records not being referenced

[JBrennan]

Hello all,

Like always, I apologies if this has been asked and answered elsewhere. I have searched for an answer but wasn't able to find anything similar.

To simplify my question lets assume there are two standalone tables;

u_table_a - A simple table, has say 3 string fields and for argument sake contains 50 records.

u_table_b — Also a simple table with say 2 string fields and a reference field to point to table a. Again for argument sake, say it has 30 records and for each record the reference field is pointing to a separate entry in table a.

Now, I am trying to create a report that only shows the 20 entries in table a that are not being referenced by table b.

I feel like I'm close with creating a custom database view but just cant crack it.

-------FYI-----------

If it were In SQL I would use something like this;

SELECT   *

FROM u_table_a

WHERE name NOT IN (SELECT name FROM u_table_b)

-----------------------

Thank you very much in advance

[Ashutosh Munot1]

You will have to Use Database views for this.

Which will take data from two tables and combine them to have on report.

See below link which will Give a brief idea about it...let me know if you need any help.

Database Views - ServiceNow Wiki

[ramireddy · Accepted solution]

We can write the same query in the following way as well.

SELECT A. *

FROM u_table_a A

LEFT JOIN u_table_b B

ON A.name = B.name

WHERE B.name is null

Now, we can create a database view like this. Below article explains creating a left join.

Database Views - ServiceNow Wiki

Adding first table is same. While adding second table, it's bit different. here is the screenshot of the example I tried now.

[Pradeep Sharma]

Hi Jeremy,

You are on right track to create a database view as you mentioned in your post. I tried on my instance with same scenario.

Detailed Steps :

1.Create a database view

Give a name : Test (change to what ever you want)->Save the record

2.Click on view table

1.select table u_table_b and give variable prefix as rel with order as 200->Save the record

2.select table u_table_a and give variable prefix as a with order as 100->Where clause as a.sys_id = rel.u_name //Here replace u_name with the exact column name of the field on u_table_b which refers to u_table_a->Save the record.

Screenshot attached for reference: In my example u_table_b is referred as b and u_table_a is referred as u_a

Database Views - ServiceNow Wiki

You can then refer the same table for reporting purpose.

Reporting - ServiceNow Wiki

I hope this helps

[JBrennan]

Ashutosh, Thank you for confirming I was on the right track with Database Views and your offer of help!

Pradeep, Thank you very much for taking the time-out to test and screencap this for me, I followed your instructions and was able to get a combined database view but not filter by only records that didn't reference table a.

And finally ram, although I couldn't get the exact result I was hoping from the database view (adding the sys_id is null in the where clause wasn't working but I'm sure it was just me), using the left join as you suggest I was able to display all records and then just applying the sys_id ISEMPTY filter via a standard list view yielded the desired result. Thank you very much.

Again, much appreciated all.

Jeremy