Searching for Records with Data in Related Lists

Dubz · ‎09-05-2017

Hi All,

I'm trying to cleanup our companies table, to do this i need to produce a list of companies that have no users and no service orders associated with this. The user and service order information is sitting in related lists on the company form. Is there an easy way to filter my list view of companies to only show those with no users or service orders associated?

If not, what other options are there to retrieve this data?

Michael Ritchie · ‎09-05-2017

David, the only way to do this is via script. You need to do this via background script so make sure your ID has the security_admin role.

Click your name in the upper right hand corner and choose Elevate Roles
Click security_admin and click OK
Then navigate to System Definition\Scripts - Background and paste in the script below and click Run Script
The script has a variable at the top that you can set to true if you want counts for all companies.
Note: This will only show active users and active orders

var showAllCounts = false;


var companyRec = new GlideRecord("core_company");

companyRec.query();

while (companyRec.next()) {

       var userCount = parseInt(getRecordCount("sys_user", companyRec.sys_id.toString()), 10);

       var orderCount = parseInt(getRecordCount("sm_order", companyRec.sys_id.toString()), 10);

       if (showAllCounts) {       

               gs.print("Name: " + companyRec.name + ", ID: " + companyRec.sys_id + " Users: " + userCount + " Orders: " + orderCount);

       } else if (userCount > 0 || orderCount > 0) {

               gs.print("Name: " + companyRec.name + ", ID: " + companyRec.sys_id + " Users: " + userCount + " Orders: " + orderCount);

       }

}


function getRecordCount(tableName, companyID) {

       var count = new GlideAggregate(tableName);

       count.addQuery("company", companyID);

       count.addQuery("active", true); // Only getting active records

       count.addAggregate("COUNT");

       count.query();

       var activeRecords = 0;

       if (count.next()) {

               activeRecords = count.getAggregate('COUNT');

       }

       return activeRecords;

}

You will then get output like the following after you execute the script:

*** Script: Name: ACME Italy, ID: 187d13f03710200044e0bfc8bcbe5df2 Users: 19 Orders: 0

*** Script: Name: ACME South America, ID: 227cdfb03710200044e0bfc8bcbe5d6b Users: 37 Orders: 0

*** Script: Name: ACME North America, ID: 31bea3d53790200044e0bfc8bcbe5dec Users: 143 Orders: 25

*** Script: Name: ACME Czech Republic, ID: 4b7d13f03710200044e0bfc8bcbe5db6 Users: 20 Orders: 0

*** Script: Name: ACME France, ID: 81fbfe03ac1d55eb286d832de58ae1fd Users: 63 Orders: 0

*** Script: Name: ACME Germany, ID: 81fca4cbac1d55eb355b4b6db0e3c80f Users: 86 Orders: 0

*** Script: Name: ACME Japan, ID: 81fd65ecac1d55eb42a426568fc87a63 Users: 42 Orders: 0

*** Script: Name: ACME China, ID: 81fdf9ebac1d55eb4cb89f136a082555 Users: 26 Orders: 0

*** Script: Name: ACME UK, ID: a66b1fb03710200044e0bfc8bcbe5d08 Users: 87 Orders: 0

*** Script: Name: ACME Australia, ID: df7d53303710200044e0bfc8bcbe5dac Users: 31 Orders: 0

View solution in original post

gdd · ‎09-05-2017

write a one time script which will check the count for respective tables for the reference table, that way you will get list and it can be used in future as well.

Patrick Schult2 · ‎09-05-2017

You can easily use the Related List Query function of Lists v3 to do this. You would go to a list of Company records, and filter the list to just records that have zero related Users, Incidents, or whatever.

https://docs.servicenow.com/bundle/jakarta-servicenow-platform/page/use/using-lists-v3/task/create-r...

Dubz · ‎09-05-2017

Thanks Patrick but is that example only available on Jakarta? I'm still on Helsinki and i activated list v3 on my dev environment and the related lists conditions section wasn't available?

I'm planning on upgrading to Jakarta at some point soon, do the v3 lists load quicker on there? They're slow on Helsinki...

Patrick Schult2 · ‎09-05-2017

List v3 is available in Helsinki, but the related list query tools are Istanbul+. List loading speed is something ServiceNow has advertised Jakarta as increasing quite a bit (30%-ish: ServiceNow Software Release — Jakarta ).

Since you can't use that method, you'll need to write a script to find out which companies are unused. You did also mention that you are doing this to "clean up" the Company list. Really important to note that the Company records could be used for much more than just Users and Incidents - they could be referenced by your CMDB models, installed software, stuff like that. If you're having problems with duplicate companies, you should investigate Data Normalization instead of just deleting the records. Normalize a field