Reference stream posts & status changes in PDF templates?

[migrator]

Hello all!

I really want to include all the non-internal Posts and Status Changes in the PDF for Cases somehow.

Tried to rtfm this extensively, but have not seen any mention of how to reference the Stream ( Posts and Status Changes ) in a PDF template -- for example in Cases.

Tried this also to see if it was in scope, like so, but I see no objects to grab and iterate through:

{{#each this}}
{{@key}} – key
{{/each}}

Also looked a the Entity Relationships available to Cases, and see nothing, except Notes, but that seems to be a different entity than Stream Posts.

Also looked through the Issues on GitHub for clues.

I am extremely new to EspoCRM, and would appreciate the help of a veteran! Possibly an easy question.

Thank you very much!

[lazovic]

Hi migrator,

This functionality can be done using Workflow (Advanced Pack extension feature):

1. Go to Administration > API Users and create an API User and select a Role with maximum access for it (you can create Role in Administration > Roles).
2. Copy its API key.
3. Create in all entities, for which it will be necessary to pull out all posts in PDF Templates, the Text field with the Text type (go to Administration > Entity Manager > your entities > Fields and create a new field).
4. Go to Administration > Workflows and create the following Workflow:
   
   
   With the following Actions:
   
   
   
   
   
   In Send HTTP Request Action, instead of http://localhost/espo-master, insert the URL of your EspoCRM instance. Paste the previously copied API Key into the Headers (from point 2).
   
   FULL formula for Execute Formula Script Action:
   
   

   	$parentId = workflow\targetEntity\attribute('parentId');
   	$parentType = workflow\targetEntity\attribute('parentType');
   
   	$getResponce = json\retrieve($_lastHttpResponseBody, 'list');
   	$getResponceEncode = json\encode($getResponce);
   
   	// USERS
   	$i = 0;
   	$usersArray = list();
   
   	while(
   	$i < array\length($getResponce),
   	(
   	$usersArray = array\push($usersArray, json\retrieve($getResponceEncode, string\concatenate('', $i, '.createdByName'))
   	);
   
   	$i = $i + 1;
   
   	)
   	);
   
   	// POSTS
   	$i = 0;
   	$postsArray = list();
   
   	while(
   	$i < array\length($getResponce),
   	(
   	$postsArray = array\push($postsArray, json\retrieve($getResponceEncode, string\concatenate('', $i, '.post'));
   	);
   
   	$i = $i + 1;
   
   	)
   	);
   
   	// DATETIME
   	$i = 0;
   	$createdAtArray = list();
   
   	while(
   	$i < array\length($getResponce),
   	(
   	$createdAtArray = array\push($createdAtArray, json\retrieve($getResponceEncode, string\concatenate('', $i, '.createdAt'))
   	);
   
   	$i = $i + 1;
   
   	)
   	);
   
   	// GENERAL
   	$i = 0;
   	$concatenateUsersAndPosts = list();
   	$pushDataToArray = list();
   	$text = list();
   
   	while(
   	$i < array\length($getResponce),
   	(
   	$concatenateUsersAndPosts = string\concatenate(array\at($usersArray, $i), ' posts ', array\at($postsArray, $i), ' at ', array\at($createdAtArray, $i));
   	$pushDataToArray = array\push($pushDataToArray, $concatenateUsersAndPosts);
   	$text = array\join($pushDataToArray, '\n\n');
   	);
   
   	$i = $i + 1;
   
   	);
   
   	record\update($parentType, $parentId, 'text', $text);​

5. For PDF Templates of the desired entity types, insert a Text placeholder into the body, in which information about posts will be displayed in the following format:
   Admin posts Text at 2023-04-11 13:09:14
   
   Admin posts Text 1 at 2023-04-11 13:05:23
   
   Admin posts Text 2 at 2023-04-11 13:01:11

This workflow can be modified in many ways, and so far it only works for posts of the Post type. That is, it cannot get information about changing the Status, but this can be corrected and supplemented, just let me know.

[migrator]

Wow @iazovic! Thank you so much for the in-depth and highly detailed answer! I will go through this at great length several times, then prepare to go this direction. What an extremely complete answer.

[lazovic]

Push user's notes to the Stream Posts (cStreamPosts) field:





Formula Script:

$getResponse = json\retrieve($_lastHttpResponseBody, 'list');

$pushDataToArray = list();

$i = array\length($getResponse) - 1;
while ($i >= 0) {
    $type = json\retrieve($_lastHttpResponseBody, string\concatenate('list.', $i, '.type'));

    if ($type != 'Post') {
        $i = $i - 1;
    }

    $user = json\retrieve($_lastHttpResponseBody, string\concatenate('list.', $i, '.createdByName'));
    $post = json\retrieve($_lastHttpResponseBody, string\concatenate('list.', $i, '.post'));
    $createdAtRaw = json\retrieve($_lastHttpResponseBody, string\concatenate('list.', $i, '.createdAt'));

    $createdAt = datetime\format($createdAtRaw, 'Europe/Kyiv', 'DD/MM/YYYY HH:mm');

    $attachments = json\retrieve($_lastHttpResponseBody, string\concatenate('list.', $i, '.attachmentsIds'));
    $attachmentCount = array\length($attachments);
    $hasAttachments = $attachmentCount > 0;

    $textEntry = '';

    if ($hasAttachments && $post) {
        $textEntry = string\concatenate($user, ' posts "', $post, '" with ', $attachmentCount, ' attachment(s) at ', $createdAt);
    } else if ($hasAttachments) {
        $textEntry = string\concatenate($user, ' posts ', $attachmentCount, ' attachment(s) at ', $createdAt);
    } else {
        $textEntry = string\concatenate($user, ' posts "', $post, '" at ', $createdAt);
    }

    $pushDataToArray = array\push($pushDataToArray, $textEntry);

    $i = $i - 1;
}

$text = array\join($pushDataToArray, '\n\n');

$parentId = workflow\targetEntity\attribute('id');
$parentType = 'Account';

record\update($parentType, $parentId, 'cStreamPosts', $text);

[lazovic]

Put a new note in the Stream Posts field:





Formula Script:

$noteText = workflow\targetEntity\attribute('post');
$userId = workflow\targetEntity\attribute('createdById');
$user = workflow\targetEntity\attribute('createdBy.name');
$createdAtRaw = workflow\targetEntity\attribute('createdAt');

$createdAt = datetime\format($createdAtRaw, 'Europe/Kyiv', 'DD/MM/YYYY HH:mm');

$attachments = workflow\targetEntity\attribute('attachmentsIds');

$attachmentCount = 0;
if ($attachments) {
    $attachmentCount = array\length($attachments);
}

$parentId = workflow\targetEntity\attribute('parentId');
$parentType = workflow\targetEntity\attribute('parentType');

$currentStream = record\attribute($parentType, $parentId, 'cStreamPosts');

if (!$currentStream) {
    $currentStream = '';
}

if ($attachmentCount > 0 && $noteText) {
    $newEntry = string\concatenate($user, ' posts "', $noteText, '" with ', $attachmentCount, ' attachments at ', $createdAt);
    } else if ($attachmentCount > 0) {
        $newEntry = string\concatenate($user, ' posts ', $attachmentCount, ' attachments at ', $createdAt);
    } else {
        $newEntry = string\concatenate($user, ' posts "', $noteText, '" at ', $createdAt);
    }

$updatedStream = string\concatenate($currentStream, '\n\n', $newEntry);

record\update($parentType, $parentId, 'cStreamPosts', $updatedStream);