question Sort/OrderBy values in Array. in Developer forum

Sort/OrderBy values in Array.

Wesley Breshear — Mon, 29 Apr 2019 19:58:16 GMT

Hello,

Hopefully, this is an easy one to solve. Just need to sort my array based on the 'sysProperty.getDisplayValue('description');'. I have looked at few KB articles and tried various methods but since my sorting needs to occur in or after the While loop, I am lost how to sort (orderBy) my array. This is my 'Server Script' section of a Service Portal Widget.

(function () {
	data.appProperties = [];
	var sysCategory = new GlideRecord('sys_properties_category_m2m');
	sysCategory.addQuery('category','0c14fbf0db20ebc02911e525ca9619f9'); //REST API Business Services
	sysCategory.query();
	while (sysCategory.next()) {
		var appProperty = {};
			var sysProperty = new GlideRecord('sys_properties');
			sysProperty.addQuery('sys_id',sysCategory.property);
			sysProperty.query();
			while (sysProperty.next()) {
				appProperty.groupName = sysProperty.getDisplayValue('description');
				appProperty.propertyValue = sysProperty.getDisplayValue('name');
				//appProperty.orderBy(sysProperty.getDisplayValue('description'));  //Failure to sort
				//appProperty.orderBy('groupName');  //Failure to sort
			}
			//var sortList = sysProperty.orderby('appProperty.groupName');  //Failure to sort
  		    //data.appProperties.push(sortList);

		data.appProperties.push(appProperty);
		}
})();

Here is the current look/output. Need to sort/orderby alphabetically on the Business Service Name column [sysProperty.getDisplayValue('description')].

Thank you for your assistance.

-Wesley

Re: Sort/OrderBy values in Array.

bbf3562 — Mon, 29 Apr 2019 20:16:22 GMT

Have you tried to add like sysProperty.orderBy('description'); before sysProperty.query();?

If doesn't work then maybe use .sort()?

Here is an example of how to use it,

https://www.geeksforgeeks.org/javascript-array-sort/

Re: Sort/OrderBy values in Array.

Wesley Breshear — Mon, 29 Apr 2019 20:56:43 GMT

Hi bbf3562,

I did with no success either and your other example seems to work if the array has already been created or established. I think in my case the Array is being built in the while loop.

while (sysCategory.next()) {
		var appProperty = {};
			var sysProperty = new GlideRecord('sys_properties');
			sysProperty.addQuery('sys_id',sysCategory.property);
		    sysProperty.orderBy('description'); 
			sysProperty.query();
			while (sysProperty.next()) {
				appProperty.groupName = sysProperty.getDisplayValue('description');
				appProperty.propertyValue = sysProperty.getDisplayValue('name');			
			}			
		data.appProperties.push(appProperty);
		}

-Wesley

Re: Sort/OrderBy values in Array.

ggg — Tue, 30 Apr 2019 12:03:32 GMT

I have two suggestions:

1) there is an OOB Script Include ArrayUtil (although it does not sort)

2) try orderby in your query BEFORE adding to your array

Re: Sort/OrderBy values in Array.

Chuck Tomasi — Tue, 30 Apr 2019 12:14:40 GMT

On a similar note, move the "var appProperty = {};" inside the (inner) while loop. Best practice is to initialize the object in the scope it is going to be used.

Re: Sort/OrderBy values in Array.

ggg — Tue, 30 Apr 2019 12:36:42 GMT

you are creating an array of json objects which you will not be able to sort.

create an array of comma separated strings and you will be able to sort.

data_appProperties = [];
var sysCategory = new GlideRecord('sys_properties_category_m2m');
//sysCategory.addQuery('category','0c14fbf0db20ebc02911e525ca9619f9'); //REST API Business Services
sysCategory.setLimit(10);
sysCategory.query();
while (sysCategory.next()) {
   //var appProperty = {};
   var appProperty = '';
       var sysProperty = new GlideRecord('sys_properties');
       sysProperty.addQuery('sys_id',sysCategory.property);
       sysProperty.query();
       while (sysProperty.next()) {
           appProperty = sysProperty.getDisplayValue('description') + ',' + sysProperty.getDisplayValue('name');

       }
       //var sortList = sysProperty.orderby('appProperty.groupName'); //Failure to sort
       //data.appProperties.push(sortList);

       data_appProperties.push(appProperty);
   }
gs.info(data_appProperties);
data_appProperties.sort();
gs.info('*********************************************');
gs.info(data_appProperties);

Re: Sort/OrderBy values in Array.

Chuck Tomasi — Tue, 30 Apr 2019 12:55:21 GMT

The first point made was to have the database do the sorting using the orderBy() method. When possible, it's always more efficient to have the database do the work. I'd be more interested in getting to the bottom of the "it doesn't work" statement by running the script debugger over the records retrieved when ordering that inner query by description.

Re: Sort/OrderBy values in Array.

ggg — Tue, 30 Apr 2019 13:40:18 GMT

the inner query is doing its job -- the inner query is inside the outer query

so, we get an ordered list of sys_properties (by name or description) FOR EACH outer query record (m2m table)

so the end result is NOT going to be in name or description order

looks to me like you HAVE to order the array AFTER it is completed by array.sort()

and I believe you would have to use strings to sort (sort on a json object property.value possible)?

Re: Sort/OrderBy values in Array.

Community Alums — Tue, 30 Apr 2019 13:40:38 GMT

Try the below script,

while (sysCategory.next()) {
var appProperty = {};
var sysProperty = new GlideRecord('sys_properties');
sysProperty.addQuery('sys_id',sysCategory.property);
sysProperty.orderBy('description');
sysProperty.query();
while (sysProperty.next()) {
appProperty.groupName = sysProperty.getDisplayValue('description');
appProperty.propertyValue = sysProperty.getDisplayValue('name');

data.appProperties.push(appProperty);
}

}

I have put the data.appProperties.push(appProperty); inside the while loop so that sorted values are passed to data array.

Re: Sort/OrderBy values in Array.

Wesley Breshear — Tue, 30 Apr 2019 15:00:09 GMT

Thanks Chuck,

Made change your change with no impact and still get the un-alphabetized list.

-Wesley

Re: Sort/OrderBy values in Array.

Wesley Breshear — Tue, 30 Apr 2019 15:04:41 GMT

Hi GGG,

Here is the output to the log file and sorting didn't occur.

DAS-ChatOps (P),cmdb_ci_service.chatops,

DAS-IAMMob (P),cmdb_ci_service.iammob,

DAS-ContReg (P),das.cmdb_ci_service.contreg,

DAS-Mail (P),das.cmdb_ci_service.mail,

Helix Forecaster API (P),cmdb_ci_service.helix_forecaster_api,

Helix Turbulence API (P),cmdb_ci_service.helix_turbulance_api,

DAS-RDG (P),cmdb_ci_service.rdg,

Bifrost Commands API (P),cmdb_ci_service.bifrost_commands_api,

Bifrost Storage API (P),cmdb_ci_service.bifrost_storage_api,

DAS-Egate (P),cmdb_ci_service.egate

Any other ideas?

-Wesley

Re: Sort/OrderBy values in Array.

Wesley Breshear — Tue, 30 Apr 2019 15:18:35 GMT

Hi Surajp,

No success. Breaks the script and no values display. I followed Chucks advice above and your solution then works but still not sorting.

Any other ideas?

-Wesley

Re: Sort/OrderBy values in Array.

vkachineni — Tue, 30 Apr 2019 15:46:20 GMT

data_appProperties.sort()

gs.info('*********************************************');
for(var j = 0; j<=data_appProperties.length-1; j++){
gs.info(data_appProperties[j]);
}

//BEFORE SORT

*** Script: Number of security questions required during the password reset request,password_reset.qa.num_reset
*** Script: Require authentication for user signature.,com.snc.assessment.signature_authentication
*** Script: Work notes icon used in Task Activity formatter.,glide.ui.incident_activity.image.work_notes
*** Script: Copy attachments from originating incident,com.snc.incident.copy.attach
*** Script: Escalation rule which applies when Shifts overlap. Setting are - [start: Escalate to outgoing Shift, end: Escalate to incoming Shift, all: Escalate to all Shifts].,com.snc.on_call_rotation.escalation_rule_rota_overlap
*** Script: Show breadcrumbs for the categories associated with items when they are displayed in browse and search listings,glide.sc.show_listing_breadcrumbs
*** Script: Logging level for TaskSLAController (default: Notice),com.snc.sla.task_sla_controller.log
*** Script: When filters are changed the graph will recalculate the layout using the currently selected layout algorithm,glide.ngbsm.filters_run_layout_automatically
*** Script: Maximum number of widgets that can render simultaneously on a responsive dashboard,glide.canvas.grid.widget_render_concurrent_max
*** Script: Show approvers when displaying workflow stages with the Linear renderer.,glide.workflow.renderer.linear.show_approver
*** Script: *********************************************

//AFTER SORT

*** Script: Copy attachments from originating incident,com.snc.incident.copy.attach
*** Script: Escalation rule which applies when Shifts overlap. Setting are - [start: Escalate to outgoing Shift, end: Escalate to incoming Shift, all: Escalate to all Shifts].,com.snc.on_call_rotation.escalation_rule_rota_overlap
*** Script: Logging level for TaskSLAController (default: Notice),com.snc.sla.task_sla_controller.log
*** Script: Maximum number of widgets that can render simultaneously on a responsive dashboard,glide.canvas.grid.widget_render_concurrent_max
*** Script: Number of security questions required during the password reset request,password_reset.qa.num_reset
*** Script: Require authentication for user signature.,com.snc.assessment.signature_authentication
*** Script: Show approvers when displaying workflow stages with the Linear renderer.,glide.workflow.renderer.linear.show_approver
*** Script: Show breadcrumbs for the categories associated with items when they are displayed in browse and search listings,glide.sc.show_listing_breadcrumbs
*** Script: When filters are changed the graph will recalculate the layout using the currently selected layout algorithm,glide.ngbsm.filters_run_layout_automatically
*** Script: Work notes icon used in Task Activity formatter.,glide.ui.incident_activity.image.work_notes