recipients in sys_email table

[Shivani29]

Hi All,

I have a scenario where i need to send email after 7 days. SO I am checking in sys_email table if the recipients have received email in last 7 days, event should not be triggered however I am facing an issue. 

I am storing all the primary contacts of a vendor in a variable x, which is an array. Now, I am using below encoded query to fetch the records from sys_email table:

em.addEncodedQuery('sys_created_onBETWEENjavascript:gs.beginningOfLast7Days()@javascript:gs.endOfToday()^recipients='+x+'^type=sent^subject=xyz');

The above query is working fine except some of the records where order of the emails in recipients are not matching. e.g.

var x stored values in order  abc@z.com,def@z.com,ghi@z.com

and in sys_email table recipients field has order ghi@z.com,def@z.com,abc@z.com
Due to this, i am not getting any record in email table so event is getting triggered.

Can anyone please help me out to search for recipients even if order is not matching.

Thanks,
Shivani

[Aman Kumar S]

You can follow below logic to identify if users arrays are same or not:

ArrayUtil - diff(Array a, Array b)

Finds the differences between two or more arrays.

Any number of arrays can be provided as parameters.

Parameters

Name	Type	Description
a	Array	An array
b	Array	An array

Returns

Type	Description
Array	Returns an array of items from array a that were not found in either array b or c, or other input arrays. Duplicates are removed from the result.

Example

var arrayUtil = new ArrayUtil();
var a1 = new Array("a", "b", "c");
var a2 = new Array("c", "d", "e");
gs.print(arrayUtil.diff(a1, a2));

Output: a,b

If your case, result length should be equal to zero, then the recipients are same

[Mohith Devatte]

Hello Shivani,

I think you can sort an array before executing the script

In your scenario take your array and use sort like below 

var name = ["Mohith", "Shivani"];
name.sort(); 

This will sort your array elements in alphabetical order ,

Then through script while gliding sys_email table get recepients and push then in to an array using split(',') as recepients are stored in comma seperated manner 

then compare two arrays 

like if(arr1==arr2)

is yes then execute your logic

Please mark my answer correct if it helps you

[Shivani29]

Hi Mohith,

Thanks for reply but it dos not seem to be feasible in my case because there are around 400 emails in last 7 days and how would i store recipients in 1 array for each record separately. Below is the block of the script which I am using to check emails. Please let me know how I can achieve solution proposed by you:

for(i=0;i<=vrr.length;i++){
    pr = new GlideRecord('contact');
    pr.addEncodedQuery('company='+vrr[i]+'^primary_contact=true^active=true');
pr.query();
    gs.log('ct '+pr.getRowCount());
while(pr.next()){
    cont.push(pr.getValue('email'));
}
    //var prim = cont;
    gs.log("contact "+cont);
    var em = new GlideRecord('sys_email');      em.addEncodedQuery('sys_created_onBETWEENjavascript:gs.beginningOfLast7Days()@javascript:gs.endOfToday()^recipients='+cont+'^type=send-ready^subject=Pending actions are overdue');
    em.query();
    gs.log('rowcount '+em.getRowCount());
     
        if(em.getRowCount()==0){
gs.eventQueue("vendor.event",pr,vrr[i],cont);}
    else{
    gs.log("email sent in 7 days");}//}