'List' Type field - search for inactive users

[Dan Brown2]

Hi, 

I have a list field on a form from the [sys_user] table and I want to find a way to report or search somehow to find any records with inactive users that is in the list.  

Is there any way to achieve this?

Dan

[Saurav11]

Hello,

Instead of reporting it which i feel is not possible why not create a filter at the field level that only active users can be chosen in the list field.

[Claude DAmico]

I've gotta say, working with list collectors in this fashion is not a very pleasant experience, and I haven't found a good way (for me) to work with them for reporting either. However, I have found a way to loop through these kinds of fields with scripts. I can't say it is very fast, but it does work. The below example is something that will just take action.

//Use case: Remove inactive users from field

var searchRecs = new GlideRecord('search_table');
//add filters as needed
searchRecs.query(); //Get all the records that you need to search through to report on or make changes to

while(searchRecs.next()){
     var userArr = searchRecs.getValue('send_work_notes_to').split(','); //Get the VALUE of the list collector
     for(var i = 0; i < userArr.length(); i++){ //Loop through all the values in the list collector
          var userRec = new GlideRecord('sys_user');
          userRec.get(userArr[i]); //Get the user record
          if(!userRec.next() || userRec.active !== 'true'){ //If the user record doesn't exist or the user is not active
               userArr.splice(i); //Remove the user from the list collector array of values
               i--;
          }
     }
     searchRecs.setValue('send_work_notes_to', userArr.toString()); //With the remaining values, update the field
     searchRecs.update();
}

This next example is similar, but will provide a CSV output attached to a specified record instead of taking action (compliments of "Generate CSV file through script")

//Use case: Generate a CSV report of records to review

var headers = ["Record", "Current Users", "Inactive Users"];
var fileName = 'Inactive Users in Records.csv';
var csvData = '';
for(var h = 0; h < headers.length; h++){
     csvData = csvData + '"' + headers[i] + '"' + ',';
}
csvData = csvData + "\r\n";

var searchRecs = new GlideRecord('search_table');
//add filters as needed
searchRecs.query(); //Get all the records that you need to search through to report on or make changes to

while(searchRecs.next()){
     var userArr = searchRecs.getValue('send_work_notes_to').split(','); //Get the VALUE of the list collector
     var inactiveUsers = [];
     for(var i = 0; i < userArr.length(); i++){ //Loop through all the values in the list collector
          var userRec = new GlideRecord('sys_user');
          userRec.get(userArr[i]); //Get the user record
          if(!userRec.next() || userRec.active !== 'true'){ //If the user record doesn't exist or the user is not active
               inactiveUsers.push(userRec; //Add the user to the inactiveUsers array
          }
     }
     csvData = csvData + '"' + searchRecs.getUniqueValue() /*Replace with searchRecs.Number or something else if there is a better identifier*/ + '",' + '"' + userArr.toString() + '",' + '"' + inactiveUsers.toString() + '"';
     csvData = csvData + "\r\n"
}

var gsa = new GlideSysAttachment();
gsa.write('sys_id',fileName, 'application/csv', csvData); //The first parameter is the sys_id of a record you want to attach the output to

[Brad Bowman · Accepted solution]

Hi Dan,

You can see these results in a report or List view by first creating a Database View with a before Query Business Rule.

In this case the Database View will only have the one table (whatever form this list field is on), which isn't typical, but it allows us to apply a before Query Business Rule to the view (table) without affecting other reports and lists on this table.

So first create a Database View named whatever you want, and add the table this applies to, with any 2-4 letter prefix that makes sense.  In this example I'm displaying the records on a custom table named u_bkb

If you click the Try it Related Link, you should see a list of every record on that table.  You can define the columns displayed like any other list view via Configure -> List Layout.

Next, create a Business Rule, using the Database View Name as the Table

Your Script on the Advanced tab will look similar to this:

function executeRule(current, previous /*null when async*/) {
	var recArr = [];
			
	//add all records with an inactive user to an array
	var recGR = new GlideRecord('u_bkb_inact_user_in_list'); //database view name
	recGR.query();
	while (recGR.next()) {
		var usrGR = new GlideRecord('sys_user');
		usrGR.addQuery('sys_id', 'IN', recGR.bkb_u_user); //list field name
		usrGR.addQuery('active', false);		
		usrGR.query();
		if (usrGR.next()) {
			recArr.push(recGR.bkb_sys_id.toString()); //table prefix used in database view
		}
	}
	current.addQuery('bkb_sys_id', 'IN', recArr.join(','));
})(current, previous);

Note all of the places where the Database View Name, Variable prefix, and your List field name need replace my example ones.

Now when you click Try It on the Database View, or database_view_name.list in the left nav, or report (using the Database View Name as the Table) you will only see the records that have an inactive user as one of the users selected in the List field.

[Dan Brown2]

Hi Brad,

Many thanks for the reply - it looks perfect.  Annoyingly, I can't get it to work.

I have used the same name for the database view, the only difference is the Table and field in the view.

then added to the before query BR

function executeRule(current, previous /*null when async*/) {
	var recArr = [];
			
	//add all records with an inactive user to an array
	var recGR = new GlideRecord('u_bkb_inact_user_in_list'); //database view name
	recGR.query();
	while (recGR.next()) {
		var usrGR = new GlideRecord('sys_user');
		usrGR.addQuery('sys_id', 'IN', recGR.bkb_u_sme); //list field name
		usrGR.addQuery('active', false);		
		usrGR.query();
		if (usrGR.next()) {
			recArr.push(recGR.bkb_sys_id.toString()); //table prefix used in database view
		}
	}
	current.addQuery('bkb_sys_id', 'IN', recArr.join(','));
}(current, previous);