Quickly Organize your Category/Subcategory Items in Alphabetical Sequence

[ShaneFr]

Dear SN Community -

I wanted to share this script that I wrote. This script will take your categories and subcategories and put them in alphabetical order quickly without needing to manually do this in the sys_choice table for each depdenent_value that you may have. I didn't want to have to re-do it manually each and every time that I have to add a new subcategory into the system as I like my users to see the subcategories in alphabetical order all the time.

gs.info('Starting Category Updating Sequence.');

//  inital search for categories
var choice_category = new GlideRecord('sys_choice');
choice_category.addQuery('name', '=', 'incident');
choice_category.addQuery('element', '=', 'category');
choice_category.orderBy('label');
choice_category.query();

var i = 0;
var tmpCat = [];

//  add to tmpCat array all Major categories and the value assigned
while (choice_category.next()) {
  tmpCat.push({label: choice_category.label.toString(), value: choice_category.value.toString()});
}

// update the sequence based off label
for (var num = 0; num < tmpCat.length; num++) {
  var choice_category = new GlideRecord('sys_choice');
  choice_category.addQuery('name', '=', 'incident');
  choice_category.addQuery('element', '=', 'category');
  choice_category.addQuery('label', '=', tmpCat[num].label.toString());
  choice_category.query();
  while (choice_category.next()) {
    //  here is where we always put the "other" last in the list even though it's not truly the last letter of the alphabet.
    var result = choice_category.label.match(/other/i);
    if (result) {
      choice_category.sequence = tmpCat.length;
    } else {
      choice_category.sequence = i;
    }
    choice_category.update();
  }
  i++;
}

gs.info('Category Updating Done.');
gs.info('Subcategory Updating Sequence.');

//  ok! now we are going to do the subcateogies. we already have the categories stored so lets go through them again.
for (var num = 0; num < tmpCat.length; num++) {

  var choice_subcategory = new GlideRecord('sys_choice');
  choice_subcategory.addQuery('name', '=', 'incident');
  choice_subcategory.addQuery('element', '=', 'subcategory');
  choice_subcategory.addQuery('dependent_value', '=', tmpCat[num].value.toString());
  choice_subcategory.orderBy('label');
  choice_subcategory.query();

  // clear the array of the subcategory so we have a new set to work with
  var tmpSubCat = [];
  while (choice_subcategory.next()) {
    tmpSubCat.push({label: choice_subcategory.label.toString()});
  }

  //  set the sequence to 0 at the start each time we do a new category
  var j = 0;

  //  go through each of the subcategory items for the parent category
  for (var num_three = 0; num_three < tmpSubCat.length; num_three++) {

    var choice_subcategory_update = new GlideRecord('sys_choice');
    choice_subcategory_update.addQuery('name', '=', 'incident');
    choice_subcategory_update.addQuery('element', '=', 'subcategory');
    choice_subcategory_update.addQuery('dependent_value', '=', tmpCat[num].value.toString()); // master category
    choice_subcategory_update.addQuery('label', '=', tmpSubCat[num_three].label.toString()); // the subcategory
    choice_subcategory_update.query();

    while (choice_subcategory_update.next()) {
      //  here is where we always put the "other" last in the list even though it's not truly the last letter of the alphabet.
      var result = choice_subcategory_update.label.match(/other/i);
      if (result) {
        choice_subcategory_update.sequence = tmpSubCat.length;
      } else {
        choice_subcategory_update.sequence = j;
      }
      choice_subcategory_update.update();
    }

    //  go ahead and +1 to the sequence
    j++;
    //  kill the object so it can be regenerated
    choice_subcategory_update = '';
  }
}

gs.info('Subcategory Updating Done.');

I hope you enjoy this creation to just to make sure you don't go crazy in your hunt for perfection. You must be 'admin' role or access to "Run Background Scripts". I hold no liability for what happens to your instance of course. Verify my code always in your non-prod instance before you are sure of the results.

Thanks,

Shane

[Laszlo Balla]

Hi,

I just wanted to let you know that I tried your script and it did the job prefectly (on Jakarta), so thanks a bunch for it!