How can I query the sys_user table and only return custom or non system fields. Usually they start with u_

[Michael Cross]

I know I can use this script to return all of the fields in the sys_user table. But I would like to return only the fields that are user-created fields. They usually begin with u_

Once I have returned those user-created fields, I would like to place text into the user-created fields that are blank with no data. 

Can anyone help with this?

var arr_fields=[];
var fields = new GlideRecord('sys_user');
fields.addQuery('name');

fields.query();

while(fields.next())
{
arr_fields.push(fields.column_label.toString()+"("+fields.element.toString() + ")" );
}

for(var i=0; i<arr_fields.length; i++)
{
gs.print(arr_fields[i]);
}

Does this make sense?

[dmathur09]

Hi Michael,

Just put the following filter to get the list of custom field

Regards,

Deepankar Mathur

[Vincent Ramos]

You should change your code to query the Dictionary table [sys_dictionary] if you want to get the fields names from the table.

Your script should look like this.

var arr_fields = [];
var fields = new GlideRecord('sys_dictionary');
fields.addEncodedQuery('nameSTARTSWITHsys_user^elementSTARTSWITHu_');
fields.query();

while (fields.next()) {
    arr_fields.push(fields.column_label.toString() + "(" + fields.element.toString() + ")");
}

for (var i = 0; i < arr_fields.length; i++) {
    gs.print(arr_fields[i]);
}

Now, what text do you need to put into those fields? is it for sys_user records with blanks from the collected fields?

If so, try this

var arr_fields = [];
var fields = new GlideRecord('sys_dictionary');
fields.addEncodedQuery('nameSTARTSWITHsys_user^elementSTARTSWITHu_');
fields.query();

while (fields.next()) {
    var grSysUserFields = new GlideRecord('sys_user');
    grSysUserFields.addQuery(fields.element, '');
    grSysUserFields.query();

    while (grSysUserFields.next()) {
        grSysUserFields[fields.element] = "Text here";
        // update field
        gs.info(grSysUserFields[fields.element]);
    }
}

[Michael Cross]

This looks good. But how can I test to see if the text is actually inserted into the empty fields? 

[Ankur Bawiskar · Accepted solution]

@Michael Cross 

something like this will give you custom fields which starts with u_ and their values

var user = new GlideRecord('sys_user');
if(user.get('name', 'Abel Tuter')){
	var fields = user.getFields();
	for(var i=0; i<fields.size(); i++) { 
		var field = fields.get(i);
		var descriptor = field.getED(); 
		var fieldName = descriptor.getName();
		var fieldValue = user[fieldName].getDisplayValue();
		var fieldLabel = descriptor.getLabel();
		if(fieldName.toString().startsWith('u_') && fieldValue != ''){
			gs.info(fieldLabel + " = " + fieldValue + "\n");
		}
	}
} 

Regards
Ankur