Autonomously generate report and send it by e-mail


Userlevel 7
Badge +19

This is an Archived topic. The solution is available in the Thinkstore inside the Software Factory.

Goal

Based on a customer story.

Every day at 06:45 a procedure runs for the customer and during that procedure, errors can occur. These errors are recorded inside the application and viewed by one particular user. This user only opens the application to look at these errors. If there are no errors at all, the user still has to look inside the application to find out. It would be more handy if the user gets an e-mail with a Report of all the errors.

 

Solution

By using the System Flow functionality, we can autonomously let Indicium take care of generating a Report and sending it to the user by e-mail.

In this example I will show you what is needed to achieve this.

Prerequisites:

  • Indicium (Universal) - hereafter called Indicium
  • Application in IAM
  • SMTP server for sending the e-mail - this example uses a free SMTP server by sendinblue

Chapters

  1. Create a View in the Software Factory
  2. Create a DevExpress Report with the DevExpress Report Designer available in TCP
  3. Create a Report in the Software Factory
  4. Create a Process Flow in the Software Factory
    1. HTTP connector (From 2022.1, this can be exchanged with the Generate report process action)
    2. Write disk file (non-file storage) - (This can be replaced in 2022.2 with the Write file that utilizes file storages)
    3. SMTP connector
    4. Process Schedule
  5. Synchronize the project to IAM
  6. Result

Note that this is a basic example and a lot can be improved upon

1. Create a View

When using Reports it is highly likely information has to be combined. Some Reports use a Header (one row) view and a Details (many rows) view. This example focuses on one view containing the Header and Details.

In Datamodel, create a View that has all the columns needed:

Datamodel

The code used for this example:

select r.relation_name              as company_name
, sol.shop_order_id as shop_order_id
, sol.shop_order_line_id as shop_order_line_id
, r.logo_upload as company_logo
, '<img src=data:image/png;base64
, '+CONVERT(varchar(5000), r.logo_storage, 1)
+'>'
as company_logo_blob
, p.product_name as product_name
, sol.quantity_in_basket as quantity_in_basket

from relation r

cross join shop_order so --cross join for the example

join shop_order_line sol
on sol.shop_order_id = so.shop_order_id
join product p
on p.product_id = sol.product_id

where relation_id = 1 --My main relation / Could be replaced by parameter

Note, for the logo I used this - link

 

2. Create a DevExpress report

When succesfully downloading and opening the available DevExpress Report Designer, create a new Report using the Wizard (Ctrl+W).

Next, select the created view and the columns to make them available to use in the Report

Select Finish, now the Report can be designed. The example Report looks like the following

Designer
Preview

Save the Report (.repx) to a desired place.

 

3. Create a Report

To use the DevExpress report, a record for this report is needed in the Software Factory.

For this example api_report has been created and the created DevExpress report has been set as File. Report action was set to Export PDF and the printer is PDF.

 

4. Create a Process Flow

Now we create a Process Flow without user interaction; a System Flow. Be sure to check which process actions are supported. 

The Process Flow:

The example uses 2 Process variables:

  1. content: varbinary(max)
  2. file_path: nvarchar(2048)

1. HTTP connector - link

From 2022.1, you can make use of the Generate report process action instead of the HTTP connector.

   Input

Sending a request to Indicium to start Generating the Report is what the HTTP connector will be doing. Give it the correct URL to Generate the report and set the HTTP method to POST. The input content will be JSON so set the Content-type to application/json

Content will be a JSON string that holds all Report parameters that should get a value. In the example, only one parameter is present and will be given the value 8 as this is the correct ID of the shop order.

As Authentication, Basic is sufficient. Username/Password are the login credentials to the IAM application. In the example, that is SA with password ********* :wink:

HTTP connector - Input parameters

 

   

   Output

Only one parameter is needed to capture in a Process variable, namely Content. The HTTP connector will return in Content the varbinary string that is the Generated report. This varbinary string is captured in the Process variable content

HTTP connector - Output parameters

 

 

2. Write file - link

The content retrieved from the HTTP connector has to be written to Disk. The Write file Process action can do that.

File location is the location where the file is written to + the file name + the extension of the file. Be sure to end this value with .PDF 

Set Content as File data as this is the content of the Report.

Write file - Input parameters

 

 

3. SMTP connector - link

Generating a Report is one thing but it what really would benefit users is the ability to send an e-mail. With the SMTP connector you can do just that. Many companies have their own SMTP server to send e-mails with. In this example, a free one has been used.

Input parameter Value/explanation
SMTP server address

Your SMTP server address

e.g. smtp-relay.sendinblue.com

SMTP server port

Your SMTP server port

e.g. 587

Username Valid username to use SMTP server
Password Self-explanatory
From address

Address were the email will come from

e.g. wisethinking@smtp.com (fake)

From name

From name

e.g. Wisethinking

To recipients

E-mail-address(es) to send the e-mail to

e.g.   a@a.com; b@b.com; c@c.com

Subject

Subject of the e-mail

e.g. Your latest report

Message

Body of the e-mail.

Can be HTML - Use together with Allow HTML

Allow HTML

Yes/No

Set to Yes if Message contains HTML

(Deletable) Attachments

File path to the Report

e.g. C:\inetpub\wwwroot\IndiciumUniversal\gen_reports\test.pdf

Use Deletable attachments if the file needs to be deleted after sending. Else use Attachments

 

 

4. Process schedule - link

A schedule is needed for automating the run of the Process Flow by Indicium. This schedule is set to run at 05:45 UTC. Be sure to calculate what time to fill in.

At the time of writing this, Amsterdam time is one hour ahead of UTC, meaning 06:45 CET is 05:45 UTC. Be aware of Daylight saving time.

Schedule

 

 

5. Synchronize

Generate and validate definitions and then Synchronize to IAM. In IAM, create an Application for the Project version, if not done already, and double check if the System Flow is active.

 

6. Result

E-mail
Report attached

 


This topic has been closed for comments

13 replies

Userlevel 3
Badge +2

Does the ‘write file’ work on Azure web Service ? Would I be able to save in a network location inside my Resource groups VNET ?

Userlevel 7
Badge +19

Does the ‘write file’ work on Azure web Service ? Would I be able to save in a network location inside my Resource groups VNET ?

There will be a solution for this in 2022.1. This will be explained in detail in the Release notes for the 2022.1 platform version. It is scheduled to release at the end of the month.

Userlevel 1
Badge

Hi @Mark Jongeling,

Where can I find this the above stated information “Release notes for the 2022.1 platform version” about the ‘write file’ process action?

I am trying to write a report file to an AWS S3 bucket:

The output of the is varbinary(max) data:

And I am using this as input for the ‘write file’ and using a domain linked to the S3 location:

But when executing this error is given (only in the windows GUI):

 

Userlevel 7
Badge +19

Hi @g.c.d.vanderschoot ,

It appears to have missed the release notes, sorry for the inconvenience. On the docs here you can find the method if writing files to a file storage location in 2022.1:

https://docs.thinkwisesoftware.com/docs/sf/process_flows_connectors#write-files-to-an-external-location

From 2022.2, there will be dedicated process actions with file storage configuration for writing files, reading files and more.

Userlevel 5
Badge +2

@Mark Jongeling As a follow up on @g.c.d.vanderschoot: I have set up the Write file connector based on the documentation, but can't get it to work yet. Indicium throws the following error:

2022-05-23T11:44:03.8326016+00:00 80004410-0001-e800-b63f-84710c7967bb [err] Process action 'write_file_to_disk' in processflow 'generate_save_report' returned the following message: Process action 'write_file_to_disk' in processflow 'generate_save_report' was provided with an invalid value 'av_test1.html' for its 'File location' parameter. The target file path must be absolute. (7196cb66)

To clarify the docs:

“Create a process variable. The Process variable should end with a double underscore __ followed by the intended file storage location ID.”

→ does this mean the file_storage_id as set in the file_storage table?

“The value of the process variable must be the file name, as it will be written to the storage location.”

→ is this supposed to be set in the Default constant value like below?

 

“It can also be a relative path that ends with a file name. In that case, the storage location is the base path.”

→ then why is Indicium asking for an absolute path?

 

Looking forward to your help!

Userlevel 1
Badge

Hi @Mark Jongeling ,

It does now work to write reports to AWS S3 buckets. Now the next question is: Is it possible to use the SMTP connector in combination with the AWS location instead of a disk location? 

Userlevel 7
Badge +19

Hi @Mark Jongeling ,

It does now work to write reports to AWS S3 buckets. Now the next question is: Is it possible to use the SMTP connector in combination with the AWS location instead of a disk location? 

Hey @g.c.d.vanderschoot,

In the 2022.1 version, we do not offer a good way of using attachments that are stored in AWS S3 buckets (nor other File storages other than Disk).

However, in 2022.2 that is coming in about 1 month from now, the SMTP Connector is updated, making it possible to add attachments that are stored in any File storage location. The process action can then have a File storage assigned. 😄

I did every step like described here but with the Generate Report option and I write it to a local path (which worked before) and now I get a “GenerateReport is not supported in the Windows or Web gui” error.

What could cause this?

 

gui: 2022.1.16.0

Userlevel 7
Badge +19

I did every step like described here but with the Generate Report option and I write it to a local path (which worked before) and now I get a “GenerateReport is not supported in the Windows or Web gui” error.

What could cause this?

Hi Phil,

The Generate report process action is a System flow action and is run by Indicium. Only Indicium can run this process action, the Windows or Web GUI do not support this process action.

Only Indicium can run this process action

But we have things running with indicium

Userlevel 7
Badge +19

Only Indicium can run this process action

But we have things running with indicium

Indicium (Universal) in particular. This Indicium runs System flows and Process flows for the Universal GUI.

Indicium (Basic) that can work in combination with the Windows GUI does not execute the process flows itself. Windows and Web GUI execute process flows by themselves and do not run any System flows. Therefore the process action cannot be used inside process flows.

Therefore the process action cannot be used inside process flows.

Any reason why and if this might change in the future? Since I still would like to generate a report as PDF and write the file.

Userlevel 7
Badge +19

Any reason why and if this might change in the future? Since I still would like to generate a report as PDF and write the file.

This will indeed change in the future. We are working on making the Windows GUI compatible with the Indicium (Universal). When that is completed, System flows and Process flows can use (most) process actions such as the Generate report.