Servicenow how to split Comma Separated Values into one by one

[mandy_zhao]

Hi all,

Refer to the subject,   I am writing an UI Macro with below code, which need to compare the CSV values,   I have put the code in business rule to debug , then found the script can work (Pic 1) when user.lob only has one single value. But if user.lob has multiple values, the CONTAINS will not work, result in Pic 2.

We also tried use "IN" instead of "CONTAINS", but still no lucky. Any one know how to split the CSV values into one by one to do the comparison?   We need to check if block lobs contain user lob.

Business rule for debug:

var isValid = false;

var u = gs.getUserID();

var user = new GlideRecord('sys_user');

user.addQuery('sys_id',u);

user.query();

while(user.next())

{

gs.print("user LOB is: " + user.lob);

gs.addInfoMessage("user LOB is: " + user.lob);

var hrblock = new GlideRecord('portal');

hrblock.addQuery('sys_id', current.sys_id);

hrblock.addQuery('lob','CONTAINS', user.lob);

hrblock.query();

while(hrblock.next())

{gs.print("block LOB is: " + current.lob);

gs.addInfoMessage("block LOB is: " + current.lob);

gs.print("enter into while");

gs.addInfoMessage("enter into while");

isValid = true;

}

}

[mandy_zhao]

Hi Dylan,

Pls check my post below regarding the sys_id query and thanks so much for your reply.

[mandy_zhao]

Hi Cory,

Really appreciated your detail explanation and sorry for late response. I'd like to have a try and hope It works for me.

Regarding the sys_id   query   hrblock.addQuery('sys_id', current.sys_id);  

Actually the portal table is used to store blocks (something like kb_knowledge table stores many articles), and the "hrblock.lob" is a field in each block form. I use the query to find the currently working block in portal table and trying to identify if lobs in this block contain at least one intersection of user's lob. If yes, the current block is visible to currently login user.

[dhasselquist · Accepted solution]

To recap, you check if, on the current portal record (current.sys_id) if the "lob" field contains any of the value(s) the current user record has in the lob field.

In that case, the script below should work (as well as Cory's, but I didn't try it). Give it a shot and let us know.

var isValid = false;
var u = gs.getUserID();   
var user = new GlideRecord('sys_user');   
if (user.get(u)){   
         var lob = user.getValue("lob").split(',');   

         var hrblock = new GlideRecord('portal');   
         if (hrblock.get(current.sys_id)){   
                   var hrblockArray = hrblock.getValue("lob").split(',');   

                   for (var i = 0; i < lob.length; i++){   
                             for (var j = 0; j < hrblockArray.length; j++){   
                                       if (lob[i] == hrblockArray[j]) isValid = true;   
                             }   
                   }   
         }   
}

Assuming it works in your case, I would make some optimizations so that if it found a match, it would exit the loop and stop executing.

[mandy_zhao]

Hi Dylan,

I tried with your code, it works.   As a newer, I have learnt a lot from you guys.

My practise script:

   var isValid = false;
   var u = gs.getUserID();
   var userLobs = [];
   var userLob;
   var lobQuery;

   var user = new GlideRecord('sys_user');
   user.addQuery('sys_id', u);
   user.query();
   while (user.next()) {
   gs.print("user LOB is: " + user.u_lob);
   gs.addInfoMessage("user LOB is: " + user.u_lob);

   var userlobs = user.getValue('u_lob').split(',');
   var hrblock = new GlideRecord('kb_knowledge');
   hrblock.addQuery('sys_id', current.sys_id);
   hrblock.query();
   while (hrblock.next()) {
   var knowledgelobs = hrblock.getValue("u_line_of_business").split(',');

   for (var i = 0; i < userlobs.length; i++){
   for (var j = 0; j < knowledgelobs.length; j++){
   if (userlobs[i] == knowledgelobs[j])
       {
           gs.print("block LOB is: " + current.u_line_of_business);   
           gs.addInfoMessage("block LOB is: " + current.u_line_of_business); 
           isValid = true;
           gs.print("Result is: " + isValid);
           gs.addInfoMessage("Result is: " + isValid)
         }
     }
 }
}
   }

Script output:

[mandy_zhao]

Hi Cory,

I tried with your code as well, but looks like still have some issues on my side.

My practise script:

var isValid = false;
   var u = gs.getUserID();
   var userLobs = [];
   var userLob;
   var lobQuery;

   var user = new GlideRecord('sys_user');
   user.addQuery('sys_id', u);
   user.query();
   while (user.next()) {
   gs.print("user LOB is: " + user.u_lob);
   gs.addInfoMessage("user LOB is: " + user.u_lob);

   userLobs = user.getValue("u_lob").split(",");
   var hrblock = new GlideRecord('kb_knowledge');
   hrblock.addQuery('sys_id', current.sys_id);

   gs.print("Current LOB is: " + current.u_line_of_business);
   gs.addInfoMessage("Current LOB is: " + current.u_line_of_business);

   for (var i = 0; i<userLobs.length; i++) {
   userLob = userLobs[i];

   //gs.print("user LOB is: " + userLob);
   //gs.addInfoMessage("user LOB is: " + userLob);

   if (!lobQuery)
   lobQuery = hrblock.addQuery('u_line_of_business', 'CONTAINS', userLob);
   else
   lobQuery.addOrCondition('u_line_of_business', 'CONTAINS', userLob);

   hrblock.query();
   lobQuery = null;

   while (hrblock.next()) {
   gs.print("block LOB is: " + current.u_line_of_business);
   gs.addInfoMessage("block LOB is: " + current.u_line_of_business);
   isValid = true;
   gs.print("Result is: " + isValid);
   gs.addInfoMessage("Result is: " + isValid);

         }
     }
}

Script output:

Then I tried to print the "userLob" value out to see what it looks like, seems they are split correctly.

I have no idea why the CONTAINS can not work here