record\exists() possible fiilters/operators

[rabii]

I think it is not going to work using record exists, instead i have test another code and it worked, below is the code, we will simply find another existing email that has the same name and its status is Sent and then get the body of both existing and current email record, then will do a string replace and string match to remove the img tag from both bodies and then if the length of both bodies is equals we proceed to delete the record.

// find an existing email with the same name and status is Sent
$emailId = record\findOne('Email', 'createdAt', 'desc', 'name=', name, 'status=', 'Sent');

// Get the body field
$body = record\attribute('Email', $emailId, 'body');

// compare the length of the two bodies using string\replace and string\match to remove the img tag
ifThen(string\length(string\replace($body, string\match($body, '/<img[^>]+>/'), '')) == string\length(string\replace(body, string\match(body, '/<img[^>]+>/'), '')),

    record\update('Email', id, 'deleted', true)
);​ 

I have tested this and worked give it a ago and let me know what you think.

[rabii]

You can also use record\exists to check first condition and then execute the code above, like below:

ifThen(record\exists('Email', 'name=', name, 'status=', 'Sent'),

    // find an existing email with the same name and status is Sent and get its body field
    $body = record\attribute('Email', record\findOne('Email', 'createdAt', 'desc', 'name=', name, 'status=', 'Sent'), 'body');
    
    // compare the length of the two bodies using string\replace and string\match to remove the img tag
    ifThen(string\length(string\replace($body, string\match($body, '/<img[^>]+>/'), '')) == string\length(string\replace(body, string\match(body, '/<img[^>]+>/'), '')),
    
        record\update('Email', id, 'deleted', true)
    );
);​ 

[Jakub Grufik]

rabii thanks a lot for the workaround.

Seems like it is working as expected, I just needed to adjust regular expression a bit because there was img element at the beginning of the body as well and it matched regex so the one with attachment was not extracted.

Also I tried to compare $body1 and $body2 (both with extracted <img src="?entryPoint=attachment&amp;id=6452a6557bbdec 88b" style="width: 25%;">) directly using $body1 == $body2, and it seems like it is working perfectly fine even without string\length.
What do you think?

$sentId = record\findOne('Email', 'createdAt', 'desc', 'name=', name, 'status=', 'Sent');
$sentBody = record\attribute('Email', $sentId, 'body');

$sentBodyCut = string\replace($sentBody, string\match($sentBody, '/<img[^>]+attachment[^>]+>/'), '');
$archivedBodyCut = string\replace(body, string\match(body, '/<img[^>]+attachment[^>]+>/'), '');

output\printLine($sentBodyCut == $archivedBodyCut)​

this one is printing "True" which is win ))

Thanks a lot for this workaround mate!

Now I am going to try to use string\concatenate to be able to use variable in record\exists() and its LIKE operator as yuri mentioned like this:

$bodyCut = string\substring(body, 0, -100);
record\exists('Email', 'name=', name, 'body*', string\concatenate('%', $bodyCut, '%'), 'status=', 'Sent')

I think that could be working as well.

[rabii]

string length just added a security to make sure that the body of both emails match, but if you feel ease to not use it then it should be fine.

[Jakub Grufik]

Also this one could be working as expected:

$archivedBodyCut = string\replace(body, string\match(body, '/<img[^>]+attachment[^>]+>/'), '');
$archivedBodyCutRegEx = string\concatenate('%', $archivedBodyCut, '%');
record\exists('Email', 'name=', name, 'body*', $archivedBodyCutRegEx, 'status=', 'Sent')

What do you think? its a bit overkill but should be working in my opinion.

[rabii]

Hey mate,

Maybe this would work, using like operator and string replace + string match

$body = string\replace(body, string\match(body, '/<img[^>]+attachment[^>]+>/'), '');

ifThen(record\exists('Email', 'name=', name, 'body*', string\concatenate('%', $body, '%'), 'status=', 'Sent'),
    
    record\update('Email', id, 'deleted', true)
);​ 

[Jakub Grufik]

yeah thats exactly what I have in the topic above, I am going to try it. Thanks for help man

[Jakub Grufik]

For some reason it does not work with concat

$archivedBodyCut = string\replace(body, string\match(body, '/<img[^>]+attachment[^>]+>/'), '');
output\printLine(record\exists('Email', 'name=', name, 'body*', string\concatenate('%', $archivedBodyCut, '%'), 'status=', 'Sent'));​

I tried this and it is returning false. I tried to check manually if adjusted body is exactly same as adjusted body2 and they are exactly same, however function record\exists is returning false. Not sure why. Probably because of the length of the body.. I tried to printout $archivedBodyCut and put it manualy to record\exist using '%printedBody%' and it returned false, when I just deleted half of the body to shorten it a bit and tried it again and it returned True.. so I am bit confused now

[Jakub Grufik]

I think I got it. Give me 5 mins

[Jakub Grufik]

OK guys I have finally cracked it. But it is pretty hard to explain it

The problem was that it was extracting the image element from the body by using RegEx: /<img[^>]+attachment[^>]+>/ BUT there was still some content after that whole image element, so when I was comparing bodyWithoutImage and bodyWithImage by using LIKE operator. It was returning false because the whole bodyWithoutImage was not part of the bodyWithImage because the image element was just cut from the body, I needed to use a bit different RegEx to extract image element and everything after it and it is now working as expected.

Hopefully you will understand it from the screen

RegEx used to extract everything after image as well:
/<img[^>]+attachment.+/

Whole code:

$archivedBodyCut = string\replace(body, string\match(body, '/<img[^>]+attachment.+/'), '');
$archivedBodyCutRegEx = string\concatenate('%', $archivedBodyCut, '%');
output\printLine(record\exists('Email', 'name=', name, 'body*', $archivedBodyCutRegEx, 'status=', 'Sent'))​

On the screen, the red underline is where the problem was.

I am pretty sure that I am explaining it like dum, so if it is not clear tell me and I will try to explain it a bit more

Thanks a lot for everything!

[Jakub Grufik]

this regex also solved problem which would occur if there would be more than 1 attachment

[Jakub Grufik]

Basically if I have
string1: "Test 1234 test 4567" and
string2: "Test 1234 test 4567"
then extract 'test' from string1 using RegEx I would get:
string1: "Test 1234 4567"
then I am using LIKE operator to compare if the whole string1 is part of the string2 -> it would return False, right?

I would have to extract 'test' and everything after it to be able to use LIKE operator that would return True. That is how the LIKE operator works, it is comparing if the whole string1 is part of the string2, looking for exactly same string sequence

Hopefully it is clear

[rabii]

glad you sorted it out mate.

[Jakub Grufik]

WE sorted it out! Thanks a lot for the help guys!