Update reference qualifiers via script

[kchorny]

We are faced with this issue: in catalog items, it is sometimes necessary for the end user to select a user that is no longer active (for example, request to access the users data after they've already left the company). However, OOB ServiceNow does not allow end users to have visibility to inactive users so when asked to select the user whose data they need access to, they can't find them.* I've fixed that issue, but now I need to update the reference qualifier for all fields pointing to the user table (~5500) and add 'active=true'. 

I've written this script to update one record to see what happens, but it doesn't make the change and I'm wondering what I'm missing.

var refqual = 'active=true';
var qs = 'use_reference_qualifier=simple^reference=sys_user^reference_qual_conditionNOT LIKEactive=true^ORreference_qual_conditionISEMPTY';
var d = new GlideRecord('sys_dictionary');
d.addEncodedQuery(qs);
d.query();
gs.print(d.getRowCount());
if (d.next()) {
	if (d.reference_qual_condition != '') {
		refqual = refqual + '^' + d.reference_qual_condition;
	} 
	gs.print(d.element);
	gs.print(d.name);
	d.reference_qual_condition = refqual;
	d.reference_qual = refqual;
	d.autoSysFields(false);
	d.update();
}

[Tony Chatfield1]

Hi, can you clarify what the OOB issue was and how you 'fixed' it?
Looking at dictionary records return by your query, many do not have OOB refqualifer filtering for sys_user.active and so the need to add this filtering seems like excessive customization for no major gain;
And with no clear details of your configuration changes I don't think it's possible to ensure any response provided is appropriate for your situation/best practice. 
As a basic guide I would look at only updating directly impacted fields, IE fields that the 'end users' can see\interact with and this is probably < 10  tables\fields for unlicensed/end users.

[kchorny]

The problem: OOB, users (all but admins) can not see inactive users. This is handled by the 'user query' business rule. This is a problem because some catalog items require the end user to select an inactive user but are unable to do so.

To fix:

1. I turned off the business rule 'user query' which was only allowing anyone, except admins, to see active users.

2. Then I updated the sys_user write ACL to only allow write on active records for all but admins.

3. Then I updated all of the variables that reference the sys_user table and added an active=true condition, except in cases where inactive records need to be available.

Now that I've done that, all field filters need to be updated so that roled users can't select an inactive user in any records. Since we could potentially use any table in the future, we decided that updating the filter on all of them would be the best approach and we can deal with outliers as they come up - they should be few and far between.

Also - "Looking at dictionary records return by your query, many do not have OOB refqualifer filtering for sys_user.active". I presume that's because the 'user query' business rule is active OOB, so there's no need for the filter.