Query in server Scripting

[devservicenow k]

Under My open Incidents in SP portals i need to sort the state fields value. where i need the On hold state field should come at the top . i have a custom widgets for this but i need the state on hold values should be seen at the top  followed by other values . 

[AnveshKumar M]

Hi @servicenow lath / @devservicenow k 

Try this script.

//we get only a max number of elements to avoid to have a big list of it
var maxNumberOfItemsInTheList = parseInt(options.max_number_of_elements_shown_on_the_list);
//set 10 if maxnumber is undefined, empty or negative value
maxNumberOfItemsInTheList = maxNumberOfItemsInTheList > 0 ? maxNumberOfItemsInTheList : 10;
var initRow = 0;
var lastRow = maxNumberOfItemsInTheList;
var currentPage = 0; //0 is the first page
if (input) {
    // update pagination
    currentPage = input.pagination.currentPage;
    initRow = (currentPage * maxNumberOfItemsInTheList);
    lastRow = initRow + maxNumberOfItemsInTheList;

}

var displayField = "short_description";

var secondaryfields = "number,sys_updated_on,state";
secondaryfields = secondaryfields.split(",");


var gr = new GlideRecordSecure('incident');
gr.chooseWindow(initRow, lastRow);
gr.addEncodedQuery('active=true^caller_idDYNAMIC90d1921e5f510100a9ad2572f2b477fe^ORu_affected_userDYNAMIC90d1921e5f510100a9ad2572f2b477fe');
gr.orderByDesc("state");
gr.query();
var rowCount = gr.getRowCount();
var approvals_on_hold = [];
var approvals = [];
var ids = [];
var source_tables = [];

while (gr.next()) {

    var j = {};
    j.sys_id = gr.getUniqueValue();
    j.table = gr.getRecordClassName();

    j.display_field = getField(gr, displayField);

    j.secondary_fields = [];
    secondaryfields.forEach(function(f) {
        j.secondary_fields.push(getField(gr, f));
    });

    j.state = gr.getValue("state");
    j.stateLabel = gr.state.getDisplayValue();
	if(j.state == '3'){
		approvals_on_hold.push(j);
	}else{
		approvals.push(j);
	}
}

var arrayUtil = new ArrayUtil();
approvals = arrayUtil.concat(approvals_on_hold, approvals);

function getField(gr, name) {


    var f = {};
    f.display_value = gr.getDisplayValue(name);
    f.value = gr.getValue(name);
    var ge = gr.getElement(name);
    if (ge == null)
        return f;

    f.type = ge.getED().getInternalType();
    if (f.type == "glide_date_time")
        f.isFuture = gs.dateDiff(gr.getValue(name), gs.nowNoTZ(), true) < 0;
    else if (f.type == "glide_date")
        f.isFuture = gs.dateDiff(gr.getValue(name), gs.now(), true) < 0;
    f.label = ge.getLabel();
   // gs.log('secondaryfieldsnameFFFF' + JSON.stringify(f));

    return f;

}

data.approvals = approvals;
// for pagination
data.pagination = {};
data.pagination.hasNext = (approvals.length == (parseInt(lastRow) - parseInt(initRow)) && lastRow < rowCount);
data.pagination.hasPrevious = parseInt(initRow) > 0;
data.pagination.from = parseInt(initRow + 1);
data.pagination.to = parseInt(lastRow) < parseInt(rowCount) ? parseInt(lastRow) : parseInt(rowCount);
data.pagination.of = parseInt(rowCount);
data.pagination.showPagination = data.pagination.hasPrevious || data.pagination.hasNext;
data.pagination.currentPage = data.pagination.from > data.pagination.to ? currentPage - 1 : currentPage;

[AnveshKumar M · Accepted solution]

Hi @devservicenow k ,

To include on hold reason, try this script.

//we get only a max number of elements to avoid to have a big list of it
var maxNumberOfItemsInTheList = parseInt(options.max_number_of_elements_shown_on_the_list);
//set 10 if maxnumber is undefined, empty or negative value
maxNumberOfItemsInTheList = maxNumberOfItemsInTheList > 0 ? maxNumberOfItemsInTheList : 10;
var initRow = 0;
var lastRow = maxNumberOfItemsInTheList;
var currentPage = 0; //0 is the first page
if (input) {
    // update pagination
    currentPage = input.pagination.currentPage;
    initRow = (currentPage * maxNumberOfItemsInTheList);
    lastRow = initRow + maxNumberOfItemsInTheList;

}

var displayField = "short_description";

var secondaryfields = "number,sys_updated_on,state";
secondaryfields = secondaryfields.split(",");


var gr = new GlideRecordSecure('incident');
gr.chooseWindow(initRow, lastRow);
gr.addEncodedQuery('active=true^caller_idDYNAMIC90d1921e5f510100a9ad2572f2b477fe^ORu_affected_userDYNAMIC90d1921e5f510100a9ad2572f2b477fe');
gr.orderByDesc("state");
gr.query();
var rowCount = gr.getRowCount();
var approvals_on_hold = [];
var approvals = [];
var ids = [];
var source_tables = [];

while (gr.next()) {

    var j = {};
    j.sys_id = gr.getUniqueValue();
    j.table = gr.getRecordClassName();

    j.display_field = getField(gr, displayField);

    j.secondary_fields = [];
    secondaryfields.forEach(function(f) {
        j.secondary_fields.push(getField(gr, f));
    });

    j.state = gr.getValue("state");
    j.stateLabel = gr.state.getDisplayValue();
    j.hold_reason = '';
	if(j.state == '3'){
                j.hold_reason = gr.hold_reason.getDisplayValue();
		approvals_on_hold.push(j);

	}else{
		approvals.push(j);
	}
}

var arrayUtil = new ArrayUtil();
approvals = arrayUtil.concat(approvals_on_hold, approvals);

function getField(gr, name) {


    var f = {};
    f.display_value = gr.getDisplayValue(name);
    f.value = gr.getValue(name);
    var ge = gr.getElement(name);
    if (ge == null)
        return f;

    f.type = ge.getED().getInternalType();
    if (f.type == "glide_date_time")
        f.isFuture = gs.dateDiff(gr.getValue(name), gs.nowNoTZ(), true) < 0;
    else if (f.type == "glide_date")
        f.isFuture = gs.dateDiff(gr.getValue(name), gs.now(), true) < 0;
    f.label = ge.getLabel();
   // gs.log('secondaryfieldsnameFFFF' + JSON.stringify(f));

    return f;

}

data.approvals = approvals;
// for pagination
data.pagination = {};
data.pagination.hasNext = (approvals.length == (parseInt(lastRow) - parseInt(initRow)) && lastRow < rowCount);
data.pagination.hasPrevious = parseInt(initRow) > 0;
data.pagination.from = parseInt(initRow + 1);
data.pagination.to = parseInt(lastRow) < parseInt(rowCount) ? parseInt(lastRow) : parseInt(rowCount);
data.pagination.of = parseInt(rowCount);
data.pagination.showPagination = data.pagination.hasPrevious || data.pagination.hasNext;
data.pagination.currentPage = data.pagination.from > data.pagination.to ? currentPage - 1 : currentPage;

[Rahul Kumar17]

Hi,

To sort the state field values in the "My open Incidents" widget of the Service Portal, you can modify the GlideRecord query to order the records by state in the desired order.

To put the "On Hold" state at the top of the list, you can use a CASE statement to set a custom order for the "state" field in the ORDER BY clause of the query. Here's an example of how you can modify the GlideRecord query in your code:

var gr = new GlideRecordSecure('incident');
gr.chooseWindow(initRow, lastRow);
gr.addEncodedQuery('active=true^caller_idDYNAMIC90d1921e5f510100a9ad2572f2b477fe^ORu_affected_userDYNAMIC90d1921e5f510100a9ad2572f2b477fe');
gr.orderByDesc("(CASE WHEN state='On Hold' THEN 1 ELSE 2 END), state"); // put On Hold state at the top
gr.query();

The ORDER BY clause uses a CASE statement that evaluates the state field and assigns a value of 1 to the records with state "On Hold" and a value of 2 to the other records. This way, the records with state "On Hold" will be ordered first, followed by the other records ordered by their state values.

Thanks,

Rahul Kumar

[Weird]

Since it's custom it should be relatively easy to modify.
In your server script add a check for on hold.
if on hold -> j.onhold = true
If not on hold -> j.onhold = false

Then in your HTML as you have some kind of ng-repeat, you can just copy it and add a condition to it.
For example you could add filter to the ng-repeat or inside the elements an ng-if to check for the onhold true/false value.
After adding the filter/if condition, you can then just copy the whole ng-repeat under the original one and add a reverse condition to it. For example if first one checks for onhold == true, then the next one should be onhold == false

This way you'll first show the ones that are onhold and then the ones that are not.