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();
}

[kchorny]

Thank you for your input. The issue is within the Service Catalog, but in order to fix it I have to give everyone at least read access to all inactive users. This has a domino effect because, previously it wasn't possible to select an inactive user, therefore there were no filters on the variables OR fields referencing the sys_user table. Now that users will have visibility, we have to limit which users can be selected within most of the variables and fields. There weren't that many variables, so I did them individually, but there are ~5500 fields so I need to script it.

I believe that my issue may be related to the fact that some of the fields are on extended tables, and therefore read-only at the child table level. So I'll need to filter out dictionary entries for fields on child tables, but haven't yet worked out how to do that. It just so happens that the first record my script finds is one of those fields.

The field type of reference_qual_condition may also be a factor, so thank you for pointing out what I should have already discovered. 🙂

[kchorny]

The 'Conditions' type is extended from the 'String' type, so that wasn't the issue. What was the issue is as I mentioned earlier, I didn't have write access to fields on tables that are extended from other tables. I'll post my final working script as a reply to the original post.

[kchorny]

I needed to do a bit more filtering and validation in my script, as the one shown above was finding a field on a table extended from another table, and therefore not making the change because I didn't have write access to it.

Here's the script that I ended up with. It does not update fields in scopes other than Global.

If you believe this is the wrong approach, please feel free to explain why. 

var refqual = 'active=true';
var qs = 'internal_type=reference^ORinternal_type=glide_list^reference=sys_user^sys_scope=global^reference_qual_conditionNOT LIKEactive=true^ORreference_qual_conditionISEMPTY';
var d = new GlideRecord('sys_dictionary');
d.addEncodedQuery(qs);
d.query();
gs.print(d.getRowCount());
while (d.next()) {
	var tbl = d.name;
	var t = new GlideRecord('sys_db_object');
	t.get('name',d.name);
	if (t.super_class == '') {
		if (d.reference_qual_condition != '') {
			refqual = refqual + '^' + d.reference_qual_condition;
		} 
		d.reference_qual_condition = refqual;
		d.reference_qual = refqual;
		d.autoSysFields(false);
		d.update();
	}
}

[SanjivMeher]

As said before, I dont think it is a good solution to update thousands of dictionary records for a few catalog items.

As suggested, dont update the before query business rule on the user table and add a string field where if user can't find the user, they enter the username in that string field. But if you are confident, please go ahead.

[kchorny]

The underlying issue is visibility in catalog items. However, we also have roled users who wish to have visibility to inactive users. So my solution solves both issues and keeps roled users from selecting inactive users in any table that we use now or may use in the future. 

If I find that there are any adverse consequences to doing what I've done, I will come back here and report them. 🙂