record\exists() possible fiilters/operators

[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

[Jakub Grufik]

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

[rabii]

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

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

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

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]

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

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

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.

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

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

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!