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;

}

}

[Brian Dailey1]

Hi Weiwei,

Try using this instead for your 'CONTAINS' query:

        hrblock.addQuery('lobLIKE' + user.lob.toString());

Thanks,

-Brian

Edit:   Sorry, had to update my query string there... had my logic reversed using the IN statement.   But for your GlideRecord, a 'LIKE' statement works like 'CONTAINS'.   See how that works for you.

[Brian Dailey1]

By the way, instead of making business rules for the purpose of debugging code, I've found it much easier to use Fix Scripts for testing.

Fix Scripts - ServiceNow Wiki

If you need to run code against a particular record or records (like a BR would be), just declare a variable in the beginning and set it equal to the sys_id(s) of any appropriate records.   You can run it just like you would any BR, just refer to your variable declared for a GlideRecord instead of 'current'.

-Brian

[Brian Dailey1]

Hi Weiwei,

Actually, I think your original query using 'CONTAINS' would also have worked save for one thing, you need to get the sys_id for 'user.lob' to be used in a query string.   That's what the ".toString()" is doing for you, forcing resolution of the reference field into its sys_id string value....   Which is what you need to setup the query condition.

So this would also work for you:

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

-Brian

[coryseering]

Brian,

This is only true if the combination of values being queried for exists in the queried data *in the same order*.

Weiwei's sys_user table appears to have a GlideList field called "lob". That would be a reference to another table, but storing multiple values. When that is turned into a string, the order of elements in that list cannot be guaranteed. So it may have 3 values: A, B, C. But the order could be "A,C,B", "B,C,A", "B,A,C", "C,A,B," or "C,B,A".

The table being queried appears to have a field called "lob" which has a similar GlideList field. Assuming the really are GlideLists, pointing to the same table, and that Weiwei wants to find any intersection, the query must split each of the values and have a separate OR condition for each one.

Consider this scenario:

1. There is a Portal with this value in the lob field:

"3ea969433b021000dada82c09ccf3d83, a800cc2237311000913e40ed9dbe5df2, 4366fc82bf0221000ba9dc2ecf073932".

Each of those is a value sys_id for a record in another table (a Line of Business table?)

2. There is a User "Bob" with 2 values in his lob field:

"a800cc2237311000913e40ed9dbe5df2, 3ea969433b021000dada82c09ccf3d83".

3. For the results of this query, Weiwei wants to return the record described in step 1, because it shares at least one value in common with the value's in Bob's lob field.

4. Do a contains query against portal.

5. No results found.

The string "3ea969433b021000dada82c09ccf3d83, a800cc2237311000913e40ed9dbe5df2, 4366fc82bf0221000ba9dc2ecf073932" does not contain "a800cc2237311000913e40ed9dbe5df2, 3ea969433b021000dada82c09ccf3d83". The order of the first two elements in the values being queried is opposite that of the values passed in.

However, splitting up the query is like asking 'Does the string "3ea969433b021000dada82c09ccf3d83, a800cc2237311000913e40ed9dbe5df2, 4366fc82bf0221000ba9dc2ecf073932" contain either "a800cc2237311000913e40ed9dbe5df2" or "3ea969433b021000dada82c09ccf3d83"?

This is string comparison, not array intersection. Weiwei must split the fields and do separate OR queries to find the intersection.

[Brian Dailey1]

Yeah, I realized my mistake about misreading his real question, and that 'user.lob' is actually a CSV list.   I've been scanning stuff in too much of a hurry today.

But check the time-stamp on my post below.     ... talk about post-overlap.

Thanks,

-Brian