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.

[Jakub Grufik]

I will test it but it will not work in my opinion. I already tried to test it with this:

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


Same for:
record\exists('Email', 'name=', name, 'body*', string\substring(body, 0, -100), 'status=', 'Sent')

It is not returning the error, but it is returning false even when body and $body2 is exactly the same.

It seems like when you are using LIKE operator 'body*' it is expecting some regular expression with %%

[rabii]

hey mate,

try this and see if it works, we will use a function to extract the part of < img src=

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

[Jakub Grufik]

also I think if you want to use similar logic in formula, there is plenty of functions that will help you achieve what you are looking for.
For example: string\contains(), string\test() where you can use regular expression, string\match(), etc.

[Jakub Grufik]

yep, I think this can be used only where the query is being executed. So as rabii mentioned, record\exists() function for example uses things like "body=" so you could use it there. But now I cannot figure out how to put variable inside %%

[Jakub Grufik]

Guys, it is working perfectly fine if you have hardcoded string you are looking for like this:

record\exists('Email', 'name=', name, 'body*', '%testString%', 'status=', 'Sent')

However, I am now trying to figure out how to use variables inside '%%'

According to SQL docs it should be used like this:

$myVariable = string\substring(body, 0, 10)
record\exists('Email', 'name=', name, 'body*', '%' + $myVariable + '%', 'status=', 'Sent')

But syntax used above is not working for some reason.
I also tried '%$myVariable%' but it is not working as well cause it considers name of the variable as hardcoded string cause it is inside ' '

Do you guys have any idea how to crack it to be able to use a variable inside %% in record\exists() function?

[rabii]

it is not going to work, it should used in a query using one of the formula like record\exists etc

[esforim]

Hmm I will wait for someone to hold my hand with example.

[esforim]

Sorry offtopic: Please give some example rabii! This look like a new level of formula usage?

[Jakub Grufik]

I think I got it yuri

I will just create $someVariable where I will store just part of the body2 and then I will compare it body1 with $someVariable using operator LIKE and some proper pattern.. seems like that could be working. Thanks a lot again! I will update this post with results

[Jakub Grufik]

wow that is exactly what I am looking for, thanks a lot for that. Could you please help me on how to compare two bodies of the emails where the last row is different? How am I able to compare just part of the body? I think if I use:

record\exists('Email', 'name=', name, 'body*', %body%, 'status=', 'Sent') it will not work because the whole body2 is not part of the body1 because of that last row.. Also do I need to use '%body%' or just %body%, please? Thanks a lot

[rabii]

would that work in formula ? ?

[yuri]

You can use LIKE operator https://docs.espocrm.com/development...like-operators.

'body*', '%word%'

[yuri]

"subject" is not-storable field. Values are stored in the "name" column.

[rabii]

did you apply any customization to the email entity ? if not try to rebuild the system and also check if the entityDefs of Email has a subject field def