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?

[Michael Cross]

I am not looking for a specific user. I would like to return all fields.

[Ankur Bawiskar]

@Michael Cross 

Yes the fields would be present on table level

in the sample script I shared; you can use your query and enhance it

Thank you for marking my response as helpful.

If my response helped please mark it correct and close the thread so that it benefits future readers.

Regards
Ankur

[Ankur Bawiskar]

@Michael Cross 

Thank you for marking my response as helpful.

If my response helped please mark it correct and close the thread so that it benefits future readers.

Regards
Ankur

[Hitoshi Ozawa]

Hi Michael,

Following will query the sys_dictionary table to look for all String fields that were not created by "system" nor "glide.maint". I've added a condition to only set type String because setting a string value to reference or other field type would be an error.

I've added a query condition to only get records where one of these fields are empty instead of looping through all records to speed up.

var tableName = 'sys_user';
var defaultValue = 'default value'; // set value to value to set

var grDict = new GlideRecord('sys_dictionary');
grDict.addQuery('name', tableName);
grDict.addQuery('sys_created_by', '!=', 'system');
grDict.addQuery('sys_created_by', '!=', 'glide.maint');
grDict.addQuery('internal_type', 'string');
grDict.query();
var userFieldNameList = [];
while (grDict.next()) {
  userFieldNameList.push(grDict.element.toString());
}
var queryString = '';
for (var j=0; j<userFieldNameList.length; j++) {
  if (j > 0) {
    queryString += '^OR';
  }
  queryString += userFieldNameList[j] + 'ISEMPTY';
}
gs.info(queryString);
var grUser = new GlideRecord(tableName);
grUser.addActiveQuery();
grUser.addEncodedQuery(queryString);
grUser.query();
while (grUser.next()) {
  var updateFlg = false;
  for (var k=0; k<userFieldNameList.length; k++) {
    if (!grUser.getValue(userFieldNameList[k]) || grUser.getValue(userFieldNameList[k] == '')) {
      //gs.info('set to default value:' + grUser.sys_id + ' ' + userFieldNameList[k]);
      gsUser.setValue(userFieldNameList[k], defaultValue);
      updateFlg = true;
    }
  }
  if (updateFlg) {
    gsUser.update();
  }
}