Formula - choosing email template problem

[tomas.klicka]

Hi, i tried to choose email template based on changing some variables.
Is there any possibility this can work? And if yes, can you tell me what i am doing wrong?
Emails are sent but no subject, no body (basically no template is applied).

Could it be that email is not being created with all necessary attributes(I noticed that there is for example "from: null", "name: null" etc.) and thats(maybe) causing the template could not be applied?
Current ESPO version 7.2.7



Workflow:

Any (At least one must be met)

nakupZalohovkaPanelyZadost
changed
nakupZalohovkaKonstrukceZadost
changed
nakupZalohovkaMeniceZadost
changed
nakupZalohovkaElektroZadost
changed

Exectute formula script:

$emailId = record\create(
'Email',
'to', 'miroslava.kaiserova@esmero.eu',
'status', 'Sent',
'parentId', entity\attribute('id'),
'parentType', 'Zakazka');

ifThen(
entity\isAttributeChanged('nakupZalohovkaPanelyZadost'),
ext\email\applyTemplate($emailId, '6414723c9d9a30faf');
ext\email\send($emailId);
);

)

ifThen(
entity\isAttributeChanged('nakupZalohovkaKonstrukceZadost'),
ext\email\applyTemplate($emailId, '64147249c944824b7');
ext\email\send($emailId);
);


ifThen(
entity\isAttributeChanged('nakupZalohovkaMeniceZadost'),
ext\email\applyTemplate($emailId, '6414726ac64dafaa5');
ext\email\send($emailId);
);


ifThen(
entity\isAttributeChanged('nakupZalohovkaElektroZadost'),
ext\email\applyTemplate($emailId, '6414728f96a6229ff');
ext\email\send($emailId);
);​
​

JSON of generated email (no subject no body):

{
"id": "641477d2a2585703d",
"name": null,
"deleted": false,
"subject": null,
"fromName": null,
"fromAddress": "",
"fromString": null,
"replyToString": null,
"replyToName": null,
"replyToAddress": null,
"addressNameMap": null,
"from": null,
"to": "miroslava.kaiserova@esmero.eu",
"cc": "",
"bcc": "",
"replyTo": "",
"isRead": true,
"isReplied": false,
"isUsers": false,
"nameHash": {
"miroslava.kaiserova@esmero.eu": "Miroslava Kaiserová"
},
"typeHash": {
"miroslava.kaiserova@esmero.eu": "User"
},
"idHash": {
"miroslava.kaiserova@esmero.eu": "624b0adb25a730048"
},
"messageId": "dummy:641477d2a25bbdc60",
"messageIdInternal": null,
"bodyPlain": "",
"body": null,
"isHtml": true,
"status": "Sent",
"hasAttachment": false,
"dateSent": null,
"deliveryDate": null,
"createdAt": "2023-03-17 14:23:14",
"modifiedAt": "2023-03-17 14:23:14",
"isSystem": false,
"icsContents": null,
"icsEventUid": null,
"fromEmailAddressId": null,
"fromEmailAddressName": null,
"toEmailAddressesIds": [
"63651305236a99274"
],
"toEmailAddressesNames": {
"63651305236a99274": "miroslava.kaiserova@esmero.eu"
},
"ccEmailAddressesIds": [],
"ccEmailAddressesNames": {},
"replyToEmailAddressesIds": [],
"replyToEmailAddressesNames": {},
"attachmentsIds": [],
"attachmentsNames": {},
"parentId": "6413200d4b616ed4b",
"parentType": "Zakazka",
"parentName": "Testovací Zakázka",
"createdById": "630c8dc00dd195da1",
"createdByName": "Admin Jakub",
"sentById": null,
"sentByName": null,
"modifiedById": null,
"modifiedByName": null,
"assignedUserId": null,
"assignedUserName": null,
"repliedId": null,
"repliedName": null,
"repliesIds": [],
"repliesNames": {},
"repliesColumns": {},
"teamsIds": [],
"teamsNames": {},
"assignedUsersIds": [
"624b0adb25a730048"
],
"assignedUsersNames": {
"624b0adb25a730048": "Miroslava Kaiserová"
},
"createdEventId": null,
"createdEventType": null,
"createdEventName": null,
"accountId": null,
"accountName": null,
"tasksIds": [],
"tasksNames": {},
"tasksColumns": {},
"rodicPrilezitostId": null,
"rodicPrilezitostType": null,
"rodicPrilezitostName": null,
"isFollowed": true,
"followersIds": [
"630c8dc00dd195da1",
"624b0adb25a730048"
],
"followersNames": {
"630c8dc00dd195da1": "Admin Jakub",
"624b0adb25a730048": "Miroslava Kaiserová"
},
"bccEmailAddressesIds": [],
"bccEmailAddressesNames": {},
"attachmentsTypes": {}

}


JSON of email template:

{
"id": "6414723c9d9a30faf",
"name": "Zakázka - Žádost o zaplacení zalohovky (Panely)",
"deleted": false,
"subject": "Prosím o zaplacení zálohové faktury (Panely) Zakázka: {Zakazka.name}",
"body": "<p>Prosím o zaplacení zálohové faktury (Panely) Zakázka: {Zakazka.name}<br></p>",
"isHtml": true,
"oneOff": false,
"createdAt": "2023-03-17 13:59:24",
"modifiedAt": "2023-03-17 13:59:24",
"attachmentsIds": [],
"attachmentsNames": {},
"categoryId": null,
"categoryName": null,
"assignedUserId": null,
"assignedUserName": null,
"teamsIds": [
"609b9a9e5b55802d4"
],
"teamsNames": {
"609b9a9e5b55802d4": "Obchod ESMERO (všetci)"
},
"createdById": "630c8dc00dd195da1",
"createdByName": "Admin Jakub",
"modifiedById": null,
"modifiedByName": null,
"versionNumber": 1,
"attachmentsTypes": {}
}

Email template example , sent email_screen, condition_screen, action_screen are in attechement)
Thanks a lot for answers

[rabii]

try status, 'Sending' Not sent.

$emailId = record\create(
'Email',
'to', 'miroslava.kaiserova@esmero.eu',
'status', 'Sending',
'parentId', entity\attribute('id'),
'parentType', 'Zakazka');​ 

[tomas.klicka]

Hi, we´ve just tried it and nothing happened. Even the mail wasn´t sent. "Draft" or "Sending" do anything while "Sent" actually sent an mail...but as i pointed out above, no template is applied. It´s just empty mail.

[shalmaxb]

Hi, probably error in formula:

ifThen(
entity\isAttributeChanged('nakupZalohovkaPanelyZad ost'),
ext\email\applyTemplate($emailId, '6414723c9d9a30faf');
ext\email\send($emailId); <- don`t put this semicolon
);

) <- why this?

[tomas.klicka]

Hi, It said syntax was OK even before, but i tried this and no changes :-(

[rabii]

can you share the whole code all code in you script.

[tomas.klicka]

Workflow

Conditions

Any (At least one must be met)

Žádost o zaplacení zálohovky (Panely)
changed
Žádost o zaplacení zálohovky (Konstrukce)
changed
Žádost o zaplacení zálohovky (Měniče/Příslušenství)
changed
Žádost o zaplacení zálohovky (Elektroinstalační materiál)
changed

Execute formula script

$emailId = record\create(
'Email',
'to', 'jakub.grufik@esmero.eu',
'status', 'Draft',
'parentId', entity\attribute('id'),
'parentType', 'Zakazka');

ifThen(
entity\isAttributeChanged('nakupZalohovkaPanelyZadost'),
ext\email\applyTemplate($emailId, '6414723c9d9a30faf');
ext\email\send($emailId)
);

ifThen(
entity\isAttributeChanged('nakupZalohovkaKonstrukceZadost'),
ext\email\applyTemplate($emailId, '64147249c944824b7');
ext\email\send($emailId)
);

ifThen(
entity\isAttributeChanged('nakupZalohovkaMeniceZadost'),
ext\email\applyTemplate($emailId, '6414726ac64dafaa5');
ext\email\send($emailId)
);

ifThen(
entity\isAttributeChanged('nakupZalohovkaElektroZadost'),
ext\email\applyTemplate($emailId, '6414728f96a6229ff');
ext\email\send($emailId)
);

[lazovic]

Hi tomas.klicka,

Please try to change your formula to the following:

$emailId = record\create(
'Email',
'to', 'jakub.grufik@esmero.eu',
'status', 'Draft',
'parentId', entity\attribute('id'),
'parentType', 'Zakazka');

ifThen(
workflow\targetEntity\attributeFetched('nakupZalohovkaPanelyZadost') != workflow\targetEntity\attribute('nakupZalohovkaPanelyZadost'),
ext\email\applyTemplate($emailId, '6414723c9d9a30faf');
ext\email\send($emailId)
);

ifThen(
workflow\targetEntity\attributeFetched('nakupZalohovkaKonstrukceZadost') != workflow\targetEntity\attribute('nakupZalohovkaKonstrukceZadost'),
ext\email\applyTemplate($emailId, '64147249c944824b7');
ext\email\send($emailId)
);

ifThen(
workflow\targetEntity\attributeFetched('nakupZalohovkaMeniceZadost') != workflow\targetEntity\attribute('nakupZalohovkaMeniceZadost'),
ext\email\applyTemplate($emailId, '6414726ac64dafaa5');
ext\email\send($emailId)
);

ifThen(
workflow\targetEntity\attributeFetched('nakupZalohovkaElektroZadost') != workflow\targetEntity\attribute('nakupZalohovkaElektroZadost'),
ext\email\applyTemplate($emailId, '6414728f96a6229ff');
ext\email\send($emailId)
);​

[tomas.klicka]

Hi. Just tried this and still the same problem. No mails sent and still no errors in LOG.
I know i can create 4 single workflows to solve this, but i thought this is the way how to make it in 1.

[lazovic]

tomas.klicka,

Please make sure that your Outbound Emails are set up correctly and that you can successfully send a test email. Also, make sure the Trigger Type for your workflow is set to After record saved or After record updated.

[tomas.klicka]

Test email receiving is OK and trigger type of the workflow is set to After record updated. - seems OK

[lazovic]

tomas.klicka,

I created an entity with Base type and added four fields with Varchar type with the same names/values as your fields (in fact, this should not affect the correct operation of Workflow, just for our convenience).
Next, I attach screenshots of my Workflow, which works successfully every time the value of one or another field changes, and screenshots of emails. That is, the formulas work correctly.






The problem must be looked for elsewhere.

Please tell me which versions of EspoCRM and Advanced Pack extension do you use? Need to analyze.

[item]

Hi Lazovic,

if first email/Send.. .so what append to other ifThen because email first is send ?

in my case, this work .. i create one email for one while loop :

while($i < array\length($factureIds),
$factureId = array\at($factureIds, $i);
///// many other data
$language = record\attribute('Contact', $contactId, 'language');

ifThenElse(
$language == 'Fr',
$emailTemplateId = 'ddd';
$factureTemplateId = 'ccc'
,
$emailTemplateId = 'aaa';
$factureTemplateId = 'vvv'
);
$attachmentName = string\concatenate($factureName, '.pdf');
$attachmentId = ext\pdf\generate('Facture', $factureId, $factureTemplateId, $attachmentName);
$emailId = record\create('Email',
'to', $contactEmailAddress,
'attachmentsIds', list($attachmentId),
'parentId', $contactId,
'parentType', 'Contact',
'teamsIds', list('aaaa', 'bbbb')
);
ext\email\applyTemplate($emailId, $emailTemplateId);
ext\email\send($emailId);

$i = $i + 1;
);


maybe is VPS performance issue ?
i don't set status of email too.
i create oneEmail by loop

[Jakub Grufik]

Hello lazovic I am taking over this issue after my colleague.

The problem is somewhere in the function "ext\email\send($emailId)"

I followed these steps and here are results:

//This Creates the email with the status Draft - no problem so far
$emailId = record\create(
    'Email',
    'to', 'jakub.grufik@esmero.eu',
    'status', 'Draft',
    'parentId', entity\attribute('id'),
    'parentType', 'Zakazka');

//This update created email "Draft" and applies the template - no problem so far
ext\email\applyTemplate($emailId, '6414723c9d9a30faf')

//This is where the problem appears, this just changes the status of the existing email from Draft to Sending, but nothing is delivered to my mailbox
ext\email\send($emailId)​

I found out that if the "from" is empty then the system email is being used - that is cool and it is working perfectly. However, the email is not being delivered and is being stuck in the status "Sending". I have tested our SMTP for the system email address and it is working without any problem. When I try to send the test email from the system email it is working perfectly and the email arrives within 1 minute.

I have no clue what could be wrong with the function.
EspoCRM 7.2.7
Advanced pack 2.11.4
PHP 8.1
Thanks a lot for the help and for your time!

I am attaching a screen of the email stuck in the status Sending.

Here is the JSON of the email stuck in the status Sending.

{"id": "6419711c6ab255980","name": "Prosím o zaplacení zálohové faktury (Panely) Zakázka:","deleted": false,"subject": "Prosím o zaplacení zálohové faktury (Panely) Zakázka:","fromName": null,"fromAddress": "","fromString": null,"replyToString": null,"replyToName": null,"replyToAddress": null,"addressNameMap": null,"from": "admin.crm@esmero.eu","to": "jakub.grufik@esmero.eu","cc": "","bcc": "","replyTo": "","isRead": true,"isReplied": false,"isImportant": false,"inTrash": false,"isUsers": true,"nameHash": {"jakub.grufik@esmero.eu": "Admin Jakub"},"typeHash": {"jakub.grufik@esmero.eu": "User"},"idHash": {"jakub.grufik@esmero.eu": "630c8dc00dd195da1"},"messageId": "dummy:6419711c6ab68b3a5","messageIdInternal": null,"bodyPlain": "Prosím o zaplacení zálohové faktury (Panely) Zakázka:\r\n","body": "<p>Prosím o zaplacení zálohové faktury (Panely) Zakázka:<br></p>","isHtml": true,"status": "Sending","hasAttachment": false,"dateSent": null,"deliveryDate": null,"createdAt": "2023-03-21 08:55:56","modifiedAt": "2023-03-21 08:57:32","isSystem": false,"icsContents": null,"icsEventUid": null,"fromEmailAddressId": "636b5cdd676f468a3","fromEmailAddressName": "admin.crm@esmero.eu","toEmailAddressesIds": ["6363bf5c22f7efaf0"],"toEmailAddressesNames": {"6363bf5c22f7efaf0": "jakub.grufik@esmero.eu"},"ccEmailAddressesIds": [],"ccEmailAddressesNames": {},"replyToEmailAddressesIds": [],"replyToEmailAddressesNames": {},"attachmentsIds": [],"attachmentsNames": {},"parentId": "6413200d4b616ed4b","parentType": "Zakazka","parentName": "Testovací Zakázka","createdById": "630c8dc00dd195da1","createdByName": "Admin Jakub","sentById": null,"sentByName": null,"modifiedById": "system","modifiedByName": "System","assignedUserId": null,"assignedUserName": null,"repliedId": null,"repliedName": null,"repliesIds": [],"repliesNames": {},"repliesColumns": {},"teamsIds": [],"teamsNames": {},"assignedUsersIds": ["630c8dc00dd195da1"],"assignedUsersNames": {"630c8dc00dd195da1": "Admin Jakub"},"createdEventId": null,"createdEventType": null,"createdEventName": null,"accountId": null,"accountName": null,"tasksIds": [],"tasksNames": {},"tasksColumns": {},"rodicPrilezitostId": null,"rodicPrilezitostType": null,"rodicPrilezitostName": null,"isFollowed": true,"followersIds": ["630c8dc00dd195da1"],"followersNames": {"630c8dc00dd195da1": "Admin Jakub"},"bccEmailAddressesIds": [],"bccEmailAddressesNames": {},"attachmentsTypes": {}}​

[Jakub Grufik]

edit: When I use workflow action "Send email" it is working without any problem and emails are being delivered. The only problem appears when I am trying to use the formula ext\email\send()

[rabii]

Hi Jakub Grufik

Why not try to upgrade the advanced pack to the latest version and try again. there has some fixes done the advanced pack and now the current version is 2.23.9. it is worth upgrading the package and also the espocrm instance.

[lazovic]

Hi Jakub Grufik,

On EspoCRM 7.2.7 and Advanced Pack 2.11.4, sending emails through a formula in Workflows works fine.
Please try changing your formula to the following (added mandatory dot and comma characters):

​$emailId = record\create(
'Email',
'to', 'jakub.grufik@esmero.eu',
'status', 'Draft',
'parentId', entity\attribute('id'),
'parentType', 'Zakazka');

ext\email\applyTemplate($emailId, '6414723c9d9a30faf');
ext\email\send($emailId)​;

[Jakub Grufik]

Hey lazovic, of course ; is present where it should be in the original code but thanks a lot for the tip.

Here is the real code I am using:

$emailId = record\create(
'Email',
'to', 'jakub.grufik@esmero.eu',
'status', 'Draft',
'parentId', entity\attribute('id'),
'parentType', 'Zakazka');

ext\email\applyTemplate($emailId, '6414723c9d9a30faf');
ext\email\send($emailId)

as I mentioned, everything is being executed correctly except ext\email\send($emailId), this one just changes the status of the email to "Sending" from "Draft". But nothing is being delivered.

[rabii]

Hey Jakub,

You are missing ; at the end of ext\email\send($emailId)

That would skip the function.

Thanks

[Jakub Grufik]

hey rabii, ; does not have to be there as it is the last row of the code, and it is somehow working because it is changing the status from draft to sending.. (I tried it even with ; at the end of the row just to make sure - same result)