Split data from a sys_user field and display as a selectable list on the Service Portal

[Liam Rhodes]

Hi all,

I have had a rather difficult request from a client and I'll do my best to explain what that is, but I'm essentially unsure as to how to approach a suitable method to implement it.

To start off, on our sys_user table we have a field called u_partner_role that is populated with a series of roles split using a semi-colon ';'.

On the ServiceNow Portal, we have a catalog item where you can only select users where this Partner Role field is populated with data.

What I want to be able to do is take this fields data, using the ; as a split populate these in a drop-down selection variable on the Catalog Item form.

So as you can see above, when the user is selected in the 'Partner' reference field, I want the data from that users u_partner_role  field to be available in a drop down selection as below.

• Advising
• Principal
• DaaS

Any advice on this would be greatly appreciated even if it's just to direct me down the right path of what to look to include in a catalog client script.

[AshishKM]

Hi @Liam Rhodes ,

You can write a catalog client script [ onChange ] over Partner field and retrieve the "Partner Role".  User the array split method on delimiter [ ; ] this will return all partner role in array. Array can be iterate and retrieve the item and add in select box variable.

Where you stuck in client script, share the script code.

-Thanks,

AshishKMishra

[Liam Rhodes]

Thanks Ashish,

I've managed to write the following script which successfully retrieves the values from the sys_user field and split it into 3 values.

function onChange(control, oldValue, newValue, isLoading) {
   if (isLoading || newValue == '') {
      return;
   }

   //Type appropriate comment here, and begin script below

	var id ='';
	id = newValue;
	//alert('sys_id of user : ' + id);
	
	var user = new GlideRecord("sys_user");
	user.addQuery("sys_id", id);
	user.query();
	if (user.next()) {
		var partrole = user.u_partner_role;

   var valArray = partrole.split(';'); // values will be in an array named valArray
   
}

I'm unsure now how I can set these as options for the end user to select from in a select box variable on the catalogue form. I don't want to set the value of this variable, I want them to have the options in this array to choose from themselves. Is this possible?

[AshishKM]

add following code and test, make sure this field type should be select box type.

var valArray = partrole.split(';');
for ( var i=0 ; valArray.length >0; i++){
  g_form.addOption("<fieldName>", valArray[i];valArray[i]);
}

Use can try the below code using vai getReference() method

function onChange(control, oldValue, newValue, isLoading) {
   if (isLoading || newValue == '') {
      return;
   }
   //Type appropriate comment here, and begin script below
/*
	var id ='';
	id = newValue;
	//alert('sys_id of user : ' + id);
	
	var user = new GlideRecord("sys_user");
	user.addQuery("sys_id", id);
	user.query();
	if (user.next()) {
		var partrole = user.u_partner_role;
*/
var valArray = [];
var partnerReference = g_form.getReference("partner", getPartRole ); // set the corrent fieldName for partner
function getPartRole (partnerReference ){
          var partrole = partnerReference .u_partner_role; 
          valArray = partrole.split(';'); // values will be in an array named valArray
         for(var i=0; valArray.length >0 ; i++){
               g_form.addOption("<fieldName>", valArray[i], valArray[i] );        
        }        
}
  
}

[Liam Rhodes]

Thanks Ashish.

The second method you've shared just times out the web page when I select a partner from the reference field that triggers the script.

The first method also still doesn't work for me. This is what I have it as now below.

function onChange(control, oldValue, newValue, isLoading) {
   if (isLoading || newValue == '') {
      return;
   }

   //Type appropriate comment here, and begin script below

	var id ='';
	id = newValue;
	//alert('sys_id of user : ' + id);
	
	var user = new GlideRecord("sys_user");
	user.addQuery("sys_id", id);
	user.query();
	if (user.next()) {
		var parole = user.u_partner_role;
		

  var valArray = parole.split(';'); // values will be in an array named valArray

		for ( var i=0 ; valArray.length >0; i++){
		g_form.addOption("practice_needed", valArray[i], valArray[i]);  
        }        
}
  
}

I've tested this in a background script and valArray does successfully return the three values that I'm testing with in the users sys_user record, but it seems adding them in the Select Box variable is where it's falling short.