Sorting List Collector values on RITM

[Cirrus]

Good afternoon,

We have a list collector which references the sys_user table, and has the attribute ref_ac_order_by=name applied, such that if a user selects Roger, then Mark, then Anna, when we look at the right hand slushbucket in the associated RITM the users are listed in alphabetical order Anna, Mark, Roger. No problem there.

However, when we look at a dashboard view of the same RITM, the values are still displayed in the order in which they were selected - Roger, Mark, Anna. Is there a way to sort them alphabetically in this view??

The dashboard is simply a report on sc_req_item table with Choose Columns showing number, item and .variables

Thanks for any advice

[SwarnadeepNandy · Accepted solution]

Hello @Cirrus,

• One way to sort the values in a dashboard view is to use a scripted field in your report. A scripted field is a custom field that you can create using JavaScript code to manipulate the data in your report. You can use a scripted field to sort the values of your list collector variable by name and display them as a comma-separated string. Here is an example of how to do this. 

//Get the value of the list collector variable
var listCollectorValue = current.variables.list_collector;

//Split the value by comma
var listCollectorArray = listCollectorValue.split(",");

//Create an empty array to store the user names
var userNameArray = [];

//Loop through the list collector array
for (var i = 0; i < listCollectorArray.length; i++) {
  //Get the sys_id of each user
  var userId = listCollectorArray[i];
  //Create a GlideRecord object for the sys_user table
  var userGr = new GlideRecord("sys_user");
  //Query the table by sys_id
  userGr.get(userId);
  //Get the name of the user
  var userName = userGr.name;
  //Push the name to the user name array
  userNameArray.push(userName);
}

//Sort the user name array alphabetically
userNameArray.sort();

//Join the user name array by comma
var sortedListCollectorValue = userNameArray.join(", ");

//Return the sorted value as the scripted field value
answer = sortedListCollectorValue;

• Another way to sort the values in a dashboard view is to use a reference field instead of a list collector variable. A reference field is a field that points to another table and allows you to select one or more records from that table. You can use a reference field to reference the sys_user table and enable multiple selections. You can also use the ref_ac_order_by=name attribute to sort the values by name in both the form and the report views. Here is an example of how to do this:

  • Create a reference field with the name users in your catalog item.
  • Set the Reference specification > Table to sys_user.
  • Add the attribute ref_ac_order_by=name to sort the values by name.
  • Check the Multiple selections box to allow multiple selections.
  • Save the reference field and test your catalog item in Service Portal. You should see that you can select multiple users from a drop-down list and they are sorted by name.
  • Create a report on sc_req_item table with Choose Columns showing number, item and .variables.users. You should see that the values are also sorted by name in the report view.

Hope this helps.

Kind Regards,

Swarnadeep Nandy

[Cirrus]

Thankyou for responding. Unfortunately the first link takes me to a generic reporting page. Is there a specific link you can provide?

Also, I may be misinterpreting your response, but if I create a reference variable, I cannot see any where a tick box for  allowing multiple selection

Thanks

Cirrus

[Cirrus]

I got this to work in the end by creating a new variable and then adding the following code into the workflow as a run script

//Get the value of the list collector variable

var listCollectorValue = current.variables.nominee_name.toString();  

//Split the value by comma

var listCollectorArray = listCollectorValue.split(",");

//Create an empty array to store the user names

gs.log(listCollectorArray);

var userNameArray = [];

//Loop through the list collector array

for (var i = 0; i < listCollectorArray.length; i++) {

  //Get the sys_id of each user

  var userId = listCollectorArray[i];

  //Create a GlideRecord object for the sys_user table

  var userGr = new GlideRecord("sys_user");

  //Query the table by sys_id

  userGr.get(userId);

  //Get the name of the user

  var userName = userGr.name;

  //Push the name to the user name array

  userNameArray.push(userName);

}

//Sort the user name array alphabetically

userNameArray.sort();

//Join the user name array by comma

var sortedListCollectorValue = userNameArray.join(", ");

//Return the sorted value as the scripted field value and update RITM

var ritm = new GlideRecord('sc_req_item');

ritm.get('sys_id', current.sys_id); // Pass in the sys_id of the RITM

ritm.variables.<variable> = sortedListCollectorValue; 

ritm.update();