record\exists() possible fiilters/operators

[Jakub Grufik]

Hello guys,

I am currently trying to remove duplicate emails from the system.

I was originally using:

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

But the problem is that if the email has an attachment, then the duplicate email with the status Archived has different attachment ID and because of that the body is little bit different (there is a different attachment ID mentioned in the body - "<img src="?entryPoint=attachment&amp;id=644b8f556f0c38c 4e>"

Is there any other possibility how to compare the Body of the emails instead of "body="? for example "bodyContains" or something like that to be able to for example compare only part of the body without the last row where the attachment ID is mentioned? The rest of the body is exactly the same.

I was not able to find anything in the documentation.

For some reason "bodyPlain" is different in both emails so I cannot use this. I just need something those emails have in common to be able to recognize that the email is duplicate and there is exactly the same email only with status "Sent"

Original post with email duplicates https://forum.espocrm.com/forum/gene...ated-by-system

In the attachment, I am sending a screenshot of the workflow that is deleting archived emails. It is working if the email has no attachment, but with the attachment it does not recognize duplicate because of the attachment ID mentioned in the "body" as I mentioned above.

Thanks a lot for the help!

[rabii]

Maybe you can just double check with other fields other than the body or bodyPlain should acually work because it will not include attachment as bodyPlain is a text version of the body (which is hmtl Wysiwyg). how about comparing by messageId and subject fields). i think that might work, hence if the email is duplicated i would assume it has the same subject.

[Jakub Grufik]

Hi, thank you for tips.
For some reason bodyPlain is different in both emails. Subject is same, but it can be same if there is multiple replies so I think it is not safe to use it as an identifier of the duplicate. Message ID is different thats the problem caused by Amazon Workmail. I will send how it looks like in the post below.

[Jakub Grufik]

bodyPlain status "Sent":

"bodyPlain": "\n\n\n\n\n\nS pozdravem,\n\nJiří Koláček\nvedoucí stavebních prací\n\ntel:  +420724203599web: www.esmero.eu\nmail.: jiri.kolacek@esmero.eu\nfb.: esmerozimnizahradyinstagram: esmerocz\nyoutube: esmero\n \n\n28 Dub 11:17:\n\nAhojte,\n\n\n\n\nprosím Vás o skompletovanie dochádzky za 4/2023 a následné zaslanie na môj mail najneskôr do 3. 5. 2023 (streda). Do prílohy posielam nový dochádzkový formulár na mesiac 5/2023.\n\n\n\n\nĎakujem za spoluprácu a prajem príjemný víkend :)\n\n\n\n\nS pozdravom,\n\nAndrea Zaňátováasistentka finančného oddeleniatel.: +421 919 170 837\nweb: www.esmero.eumail.: andrea.zanatova@esmero.eu fb.: esmerozimnezahradyinstagram: esmero.skyoutube: esmero\n",

bodyPlain status "Archived":

"bodyPlain": "S pozdravem,\r\n\r\nJiří Koláček\r\n\r\nvedoucí stavebních prací\r\n\r\ntel:  +420724203599\r\nweb: www.esmero.eu\r\nmail.: jiri.kolacek@esmero.eu\r\nfb.: esmerozimnizahrady <https://www.facebook.com/esmerozimnizahrady> \r\ninstagram: esmerocz <https://www.instagram.com/esmero.cz/?hl=cs> \r\nyoutube: esmero <https://www.youtube.com/channel/UCuIv8R8ixZNmCjH8H4upGrQ> \r\n\r\n \r\n28 Dub 11:17:\r\n\r\nAhojte,\r\n\r\n\r\n\r\nprosím Vás o skompletovanie dochádzky za 4/2023 a následné zaslanie na môj mail najneskôr do 3. 5. 2023 (streda). Do prílohy posielam nový dochádzkový formulár na mesiac 5/2023.\r\n\r\n\r\n\r\nĎakujem za spoluprácu a prajem príjemný víkend :)\r\n\r\n\r\n\r\nS pozdravom,\r\n\r\nAndrea Zaňátová\r\nasistentka finančného oddelenia\r\n\r\ntel.: +421 919 170 837\r\nweb: www.esmero.eu <https://www.esmero.eu/> \r\nmail.: andrea.zanatova@esmero.eu <mailto:andrea.zanatova@esmero.eu>  \r\nfb.: esmerozimnezahrady <https://www.facebook.com/esmerozimnezahrady> \r\ninstagram: esmero.sk <https://www.instagram.com/esmero.sk/> \r\nyoutube: esmero\r\n\r\n",

messageId status "Sent":

"messageId": "<0037c2ce51f2a99b4ccc7a8dfcddb016/1683138110/1531@espo>"

messageId status "Archived":

"messageId": "<01020187e2d973a4-01428bee-fa18-44a8-befd-b57ac3f56a9d-000000@eu-west-1.amazonses.com>"

I am really not sure what to use to be able to distinguish duplicates.

[rabii]

Best way is to try to print the bodyPlain of two duplicated emails using sandbox. this will give you an idea if the field bodyPlain is the best option. just use sandbox and tinker around and should allow you to find the correct fields.

[Jakub Grufik]

For this I am using api/v1 view of the record to be able to see the JSONs of both emails compared side-to-side. I can see that the body is sompletely same except of the last row and bodyPlain is different for some reason as I sent the example above. To test the outcome of the function I am using to distinguish the duplicate I use sandbox

[Jakub Grufik]

so there is only "body=" in the record\exists() and it is not possible to use something like "bodyContains" to be able to compare only part of the body? Because it is really exactly the same except that last row

[Jakub Grufik]

Here on the screen you can see that the body is really same except that last row so that would be perfect to use as an identifier of the duplicate. However, because of that last row it is not working for emails with attachment.

[Jakub Grufik]

Plus there is an error when I am trying to use "subject=" in the record\exists() function..

[2023-05-04 12:57:23] ERROR: (42S22) SQLSTATE[42S22]: Column not found: 1054 Unknown column 'email.subject' in 'where clause'; POST /Formula/action/run; line: 70, file: /data/0/f/0f6e82d6-07b5-42f8-803a-fad2758ce077/esmerozz.eu/web/application/Espo/ORM/SqlExecutor.php [] []

[rabii]

that is weird it should allow to compare by subject as it is only a string field.

[Jakub Grufik]

I checked the database and for some reason column "subject" is not in the "email" table.

[rabii]

That is weird it should be there : check out the demo is has the field subject https://demo.eu.espocrm.com/#Admin/f...er/scope=Email

[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

[yuri]

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

[yuri]

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

'body*', '%word%'

[rabii]

would that work in formula ? ?

[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

[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

[esforim]

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

[rabii]

it is not going to work, it should used in a query using one of the formula like record\exists 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]

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]

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?

[yuri]

'%' + $myVariable + '%' – this won't work.

String concatenation with + operator is not supported in Formula (unfortunately, we cannot implement it as it would be a backward compatibility break, as currently '20' + '10' converts strings to integer and applies summation operator.

You need to use string\concatenate function.

[Jakub Grufik]

hmm this makes sense, I will try it thanks a lot!!!

[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]

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 %%