How to do gliderecord "IN" query when values contain commas?

Jon Ulrich · ‎04-07-2021

I have a table with a column whos values contain commas:

My,value,1
My,value,2
My,value,3

I need to query this table looking for an array of matching values

var myArray = ['My,value,1','My,value,3'];
var gr = new GlideRecord('my_table');
gr.addQuery('my_column','IN',myArray);
gr.query();
while(gr.next()){
   gs.print(gr.my_column);
}

System is returning nothing. I have a feeling the system is querying my_column IN [My,value,1,My,value,3]

I have tried escaping and encoding the commas, but still didn't work.

Any ideas how to make this work?

Jon Ulrich · ‎04-07-2021

so far my only idea is to loop through the array and add OR conditions:

var myArray = ['My,value,1','My,value,3'];
var gr = new GlideRecord('my_table');
var or = gr.addQuery('my_column',myArray[0]);
for(i=1; i<myArray.length; i++){
   or.addOrCondition('my_column',myArray[i]);
}
gr.query();
while(gr.next()){
   gs.print(gr.my_column);
}

I got it to work this way, but worry about performance for large arrays

View solution in original post

Jon Ulrich · ‎04-07-2021

Yes, the encoded query had the same issue.

Elijah Aromola · ‎04-07-2021

Are you able to post your entire code snippet with data values? Doing this with fields on a table works in my PDI.

Jon Ulrich · ‎04-07-2021

not really 😞 its a lot of complex data, and its all our user account data.