Searching for Records with Data in Related Lists

[Dubz]

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?

[Dubz]

Thanks Patrick, i'll definitely retest with v3 lists when we go to Jakarta.

Can you point me in the right direction on the script front? I can generally muddle my way through scripts but i'm drawing a complete blank on how to structure this one.

[Michael Ritchie · Accepted solution]

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

[Michael Ritchie]

Also you can change line 7 if you want to check for all tasks versus just Service Orders:

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

The company attribute is on the task table that Service Order extends.

[Dubz]

Thanks Michael, in the absence of list v3 that script is really useful!