Mass ADD/PASTE Affected CIs into a change

[marcguy]

A customer recently asked me to implement a solution whereby they could grab a list of CIs from an excel spreadsheet and paste them straight into the affected CIs list within a change, as you know you could use the EDIT button to go find the CIs but if you have a big list that could take a while, so I went off and found a way to do this using a few pieces:

1. a field on the change (can be hidden VIA UI policy) called Mass CIs (u_mass_cis)

2. A UI Action (Add Mass CIs) which is going to be calling our old friend a UI Page (mass_add_cis)
Name: Add Mass CIs
Action: mass_add_cis
Onclick: massaddcis()
Client - True
Condition: define your own condition as to whom can do this and when

Script:

function massaddcis() {
   //Get any values to pass into the dialog
   var number = g_form.getValue("number");

   //Initialize and open the Dialog Window
var dialog = new GlideDialogWindow("mass_add_cis"); //Render the dialog containing the UI Page
dialog.setTitle("Add Affected CIs to " + number); //Set the dialog title
dialog.setSize(500,1000); //Set the dialog size
dialog.render(); //Open the dialog
}

3. A UI Page which is going to present the user with a large text box for them to paste the CIs into.
One thing I did notice is that I had to replace line breaks with commas so that in our next phase which is a business rule we can easily split the CIs:
I have attached the UI page html code as I noticed it disappears here (will fix this later)
Name: mass_add_cis
HTML:

<g:ui_form>
   <!-- Set up form fields and labels -->
   <table width="100%">
     <tr id="description_row" valign="top">
        <td colspan="2">
           <!-- Short description value used as a label -->
           Use the box below to add or paste CIs into the box below in the following format:
        </td>
     </tr>
     <tr id="description_row2" valign="top">
        <td colspan="2">
           CI1
        </td>
     </tr>
     <tr id="description_row3" valign="top">
        <td colspan="2">
           CI2
        </td>
     </tr>
     <tr>
       <td>
         <!-- Comments text field (Contains comments from originating record as a default) -->
         <g:ui_multiline_input_field name="dial_comments" id="dial_comments" label="Add or Paste CIs below" value="" mandatory="true" />
       </td>
     </tr>
     <tr>
       <td colspan="2">
       </td>
     </tr>
     <tr id="dialog_buttons">
        <td colspan="2" align="right">
           <!-- Pull in 'dialog_buttons_ok_cancel' UI Macro for submit/cancel buttons.
          'ok' option will call the 'validateComments' Client script function from the UI Page-->
           <g:dialog_buttons_ok_cancel ok="return validateCIs()" />
        </td>
     </tr>
  </table>
</g:ui_form>

CLIENT SCRIPT in the UI Page:

function validateCIs() {
   //Gets called if the 'OK' dialog button is clicked
   //Make sure dialog comments are not empty
   var comments = gel("dial_comments").value;
   comments = trim(comments);
   if (comments == "") {
      //If comments are empty stop submission
      alert("Please provide comments to submit the dialog.");
      return false;
   }
   //If comments are not empty do this...
   GlideDialogWindow.get().destroy(); //Close the dialog window
var cis = comments.replace(/(\r\n|\n|\r)/gm,",");  //this turns line breaks into commas which are easier to split in the upcoming business rule
   g_form.setValue("u_mass_cis", cis); //Set the 'mass CIs' field with the values in the dialog
}

OK so now we have our CIs (comma seperated) in our field on the change form and next we will call a business rule next time the change is saved to convert these values into the affected CIs list:
We also check the CIs actually exist and if they don't then give them an error message

Business Rule
Name: Add Mass CIs to CIs affected
when: before - insert or update
condition: current.u_mass_cis.changes()
script:

/*
Created to automatically add any list of Mass CIs added via the 'Add Mass CIs' action to the affected CIs related list
*/
addaffectedcis();

function addaffectedcis(){
var chg = current.sys_id;
var cis = current.u_mass_cis;
var array = cis.split(",");  //split the cis into an array this is why we used commas
for (var i=0; i < array.length; i++) {  //start going through each one
var cmdb = new GlideRecord('cmdb_ci'); //Indicate the table to query from
if(cmdb.get('name', array<i>))  //if CI is found in the cmdb database, n.b. We should not need to query as name should be unique so when we find it stop the query
{
//check to see its not already on the affected cis list
var gr = new GlideRecord('task_ci'); //Indicate the table to query from
//The 'addQuery' line allows you to restrict the query to the field/value pairs specified (optional)
gr.addQuery('task', current.sys_id);
gr.addQuery('ci_item.name', array<i>);
gr.query(); //Execute the query
if (gr.next()) { //While the recordset contains records, iterate through them
   //Do something with the records returned
gs.addErrorMessage('CI already in Affected CIs list: '  + array<i>);
}
else {
//Create a new Affected CI record on the task_ci table and populate the fields with the values below I.e. The change number and the ci itself
var taskci = new GlideRecord('task_ci');
taskci.initialize();
taskci.task = chg;
taskci.ci_item = cmdb.sys_id;
taskci.insert();
}

}
else
{
gs.addErrorMessage('CI not found in CMDB!: ' + array<i>); //inform user ci cannot be found and show as error message does not stop submission though
}
}
current.u_mass_cis = '';  //empty the field out
}

And that's it! so 1 new field, 1 UI Action, 1 UI Page and 1 Business rule later we have a solution, here are some screenshots attached of it in action!
unfortunately I've had to x out the names to protect CI names.