Help with Encoded query in flow designer

[Snowman15]

Hi everyone,

I'm working on a Flow Designer script in ServiceNow where I build an encoded query to filter records. One of the conditions I need is to exclude any records on the sys_email table where the recipients field  contains @hotmail 

I’ve tried the following variations but none of these seem to work:

• recipientsNOTLIKE@hotmail
• recipientsNOT LIKE@hotmail
• recipientsNOTLIKEhotmail
• recipientsDOESNOTCONTAIN@hotmail
• recipientsDOES NOT CONTAIN@hotmail

If I remove that condition, flow works correctly. Any idea what's happening?

Here is the full script:

var gdt = new GlideDateTime();

var ms = 65 * 60 * 1000; // 65 minutes in milliseconds

gdt.add(-ms); // subtract 65 minutes

 var encodedQuery = [

'u_accountISEMPTY',  

'mailbox=sent',  

'sys_created_on>=' + gdt.getValue(),  

'target_tableSTARTSWITHsn_customerservice',  

'target_tableENDSWITHcase',

'recipientsNOT LIKE@hotmail'

].join('^');

return encodedQuery;

[Ravi Gaurav]

Hi @Snowman15 

Can you check the below code ?

var gdt = new GlideDateTime();
var ms = 65 * 60 * 1000; // 65 minutes in milliseconds
gdt.add(-ms); // subtract 65 minutes

var encodedQuery = [
'u_accountISEMPTY',
'mailbox=sent',
'sys_created_on>=' + gdt.getValue(),
'target_tableSTARTSWITHsn_customerservice',
'target_tableENDSWITHcase',
'recipients!LIKE@hotmail'
].join('^');

return encodedQuery;

[Snowman15]

@Ravi Gaurav that didn't work but thanks

[Ankur Bawiskar]

@Snowman15 

it works fine here so it should work there as well

Did you hard-code that encodedquery in background script and see if that works?

💡 If my response helped, please mark it as correct ✅ and close the thread 🔒— this helps future readers find the solution faster! 🙏

[Ankur Bawiskar]

@Snowman15 
try this once -> use wild card along with NOT LIKE operator

var gdt = new GlideDateTime();
var ms = 65 * 60 * 1000; // 65 minutes in milliseconds
gdt.add(-ms); // subtract 65 minutes

var encodedQuery = [
'u_accountISEMPTY',
'mailbox=sent',
'sys_created_on>=' + gdt.getValue(),
'target_tableSTARTSWITHsn_customerservice',
'target_tableENDSWITHcase',
'recipientsNOT LIKE%@hotmail%'
].join('^');

return encodedQuery;

💡 If my response helped, please mark it as correct ✅ and close the thread 🔒— this helps future readers find the solution faster! 🙏

[Snowman15 · Accepted solution]

Hi @Ankur Bawiskar  that didn't work either, for some reason when adding that condition, the look up always returns 0 results. I have now found a workaround to filter those records out by adding a step after that, not ideal but did the job. Thanks all.