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;

}

}

[coryseering]

Hi weiwei,

Essentially, you are trying to search a list of comma-separated-values for any matches in another list of comma-separated-values. A simple contains won't work, because if the sys_ids are not in the exact same order in both places, the string comparison fails.

You need to use multiple CONTAINS searches.

I think something like this would work:

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.lob);
       gs.addInfoMessage("user LOB is: " + user.lob);

       userLobs = user.getValue("lob").split(",");

       var hrblock = new GlideRecord('portal');
       hrblock.addQuery('sys_id', current.sys_id);

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

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

       hrblock.query();
       lobQuery = null;

       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;
       }
}

Notice how we split the values, and do a different orCondition for each individual one. This is like saying "If the portal has this sys_id AND ALSO (this LOB, or this LOB, or this LOB), then it should be included in the result".

You have a sys_id query there that seems like it would limit the result to a single value anyway. Should that be part of the conditional? If so, you can set lobQuery to the result of hrblock.addQuery("sys_id", current.sys_id) and then each of the ORs will be top-level. Then you're saying "where the portal has this sys_id, or the portal has this LOB, or the portal has this LOB". Then you could potentially have multiple matches.

EDIT: Forgot to add the separator to my split. Had that mixed up with Javascript's default join behavior (it defaults to joining on comma).

[dhasselquist]

I'm a little confused as to what Weiwei is trying to query exactly.

A couple things, line 15 you'll want to make sure you have split(",") not just split().

Weiwei, you need to clear up your query a little bit. As Cory mentions, the first query you add is a sys_id which is going to limit your result to a single object anyway. Are you querying the table and seeing if the record it returns has this "lob" (can someone clarify what this is representing, btw)?

If I can simplify it down to the level of comparing one array against another array, I would do it similar to the following:

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

         var hrblock = new GlideRecord('portal');
         if (hrblock.get(current.sys_id)){
                   var hrblockArray = hrblock.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;
                             }
                   }
         }
}

[coryseering]

Hi Dylan,

Eagle-eyes! I forgot the separator in the split- probably because JS defaults to joining with commas.

It seems like Weiwei is trying to compare two GlideList elements and identifying any records in the portal table which have at least one intersection. I would be wary of splitting the element directly as in your example, because "hrblock.lob" is a reference to a GlideElement, not a string. That is why I use getValue() whenever I want the value out of a field.

I may be off the mark here as well. That initial sys_id query makes me question my assumptions.

[dhasselquist]

Ah, I understand why you had getValue() now.

Also, I used to write my glide records the same way you did, but read an article on "gliderecord best practices 101" here and have since started using the get() method. I'm not sure what the community consensus is, but it saves a line at least =p.

I think Weiwei will have to help us understand the query first before we can really definitively offer a solution.