Cofiguration of the Item list and Data table form field in e-mail templates

Facebooktwitterpinterestlinkedinmail
Applies to version: 2020.3.x and above; author: Franciszek Sakławski

 

Introduction

When creating the configurable e-mail templates there is the option to add fields from the form. This article describes how to add an item list and SQL data table fields.

Fig. 1. A list of fields available in the system

 

To enter these types of fields in the template is needed:

  1. Basic knowledge of HTML language (specifically building tables)
  2. A process that contains an item list and a data table

After defining these types of fields on the form, create the “Send a custom email” action that contains the HTML code creating a table with data from the form.

Configuration

The first field to define is the Item list.

Fig. 2. The item list in WEBCON BPS Designer Studio

 

Then, add four columns containing the order data to the list – two of them are choice field type, one – text field, and one – date. On the form, three rows showing the orders have been added:

Fig. 3. The item list on the MODERN form

 

The next step is to define the data table field.

Fig. 4. The configuration of the data table field

 

The data table belongs to the “Data presentation” group of fields – so must be supplied with a data set. We use a SQL query that refers to the previously defined item list:

Fig. 5. A SQL query

 

In the “Data source” field, select the “<Current BPS database>” option, and then create a query. In this example, the query refers to the WElementDetails table which contains entries from the item list. In the first part, return the columns from the list. In the last line of the SELECT clause, change the data display format to be more user-friendly.

In the WHERE clause, limit the query to the instance containing the table and to the list from which data are loaded:

Fig. 6. The item list and data table on the form

 

Then on the transition path, define the action of sending a custom e-mail and click the “Configure” button, and then go to the “Message content” tab. In the “Content” section select the HTML tab and enter the code creating a table with your data.

Fig. 7. The configuration of the “Send a custom email” action

 

The HTML language allows you to create and configure a table according to the user’s needs – we want to add two tables in the e-mails – so the content will be closed in one section (DIV) and then divided into two sub-sections. There will be a table in each of them.

Fig. 8. The e-mail graphic structure

 

To divide e-mail into sections, use the <div> element:

Fig. 9. The element uses to divide e-mail into sections

 

In the sub-section style, information to leave a break of 20 pixels from the section border to the section content was added, respectively for the top at the bottom and the bottom at the top.

Then, in each of the sub-sections, you will put a table which, after correct configuration, will display your data (both tables look identical). At the start, create a table and set their basic style Parameters:

Fig. 10. HTML style parameters

 

Border-(top, bottom, left, right)-color: the color of the border of our table
Margin-(top, bottom): the size of the margins in pixels
Border: the width of the side borders of the table in pixels
Width: the width of the table in pixels
Cellspacing: the distance in pixels between the table contents and the borders
Cellpadding: the distance in pixels between the cell content and their borders

Then create the table content with the <tbody></tbody> tag and put the first row in their, which the column headers will be stored:

Fig. 11. The column headers

 

In the <tr> tag define the row style:

Font-weight: the font weight
color: the font color
background-color: the background color

After defining the table and header, the table looks like this:

Fig. 12. The table with column headers

 

To put data into the table, define an additional row that stores values from the form. The syntax is the same as for the header, with the difference that in each column you must define what should be entered in it. So enter the column names from the data table in the column names. Then let’s go to WEBCON BPS Designer Studio to check the headers.

In the SELECT clause, columns are downloaded with list’s column names and their aliases are the column names in the data table:

Fig. 13. SQL query

 

The columns are presented as follows:

Column name on the item list Column name on the data table
DET_Att1 NameAndSurname
DET_Att2 ShippingAddress
DET_Att3 Status
DET_Att4 DateOfOrder

 

The column names must be entered in braces. When it comes to the three columns from the item list (choice columns), you have to modify the way of reading data. In WEBCON BPS data from the select fields are saved in the “ID#Name” scheme – to display the only name you must add “N” before the tag.

The code with added form fields look like this:

Fig. 14. Two tables in the HTML format

 

After adding code, you have already configured tables displaying your table and list:

Fig. 15. Configured tables

 

The last element is to inform the system from which list and table data should be loaded. To do this, add tags that direct the system to the specified values:

Fig. 16. Tables after adding tags

 

In Fig. 8 next to the tags are GRIDID and FIELDID (in our case 5400 and 4754) – they are table and item list identifiers. You can find them in WEBCON BPS Designer Studio:

Fig. 17. The item list and data table configuration window

 

Copy the configured HTML code to the field shown in Fig. 7. After going through the path that contains the action of sending e-mail – an e-mail should be sent to the defined e-mail address, presented as follows:

Fig. 18. The custom e-mail content

 

Summary

By using HTML knowledge you can create configurable e-mail according to your needs. This functionality allows you to control how data on the item lists and data tables will be displayed on the form.

The HTML code

<div>
<div style=”margin-bottom: 10px; padding-bottom: 10px”>
   <table style=”margin-bottom: 5px; border-top-color: green; margin-top: 5px; border-left-color: green; border-bottom-color: green; border-right-color: green” cellspacing=”1″ cellpadding=”4″ width=”600″ border=”10″>
     <tbody>{SQLGRIDHEADERTEMPLATE:5400}
       <tr style=”font-weight: bold; color: white; BACKGROUND-color: blue”>
         <td>Name and Surname</td>
         <td>Shipping address</td>
         <td>Status</td>
         <td>Date of order</td>
       </tr>{/SQLGRIDHEADERTEMPLATE}
       <tr style=”font-weight: bold;”>{SQLGRIDROWTEMPLATE:5400}
         <td>{NameAndSurname}</td>
         <td>{ShippingAddress}</td>
         <td>{Status}</td> <td>{DateOfOrder}</td>
       </tr>{/SQLGRIDROWTEMPLATE}
     </tbody>
   </table>
</div>
<div style=” padding-top: 10px”>
   <table style=”margin-bottom: 5px; border-top-color: green; margin-top: 5px; border-left-color: green; border-bottom-color: green; border-right-color: green” cellspacing=”1″ cellpadding=”4″ width=”600″ border=”10″>
     <tbody>{SUBELEMHEADERTEMPLATE:4754}
       <tr style=”font-weight: bold; color: white; BACKGROUND-color: blue”>
         <td>Name and Surname</td>
         <td>Shipping address</td>
         <td>Status</td>
         <td>Date of order</td>
       </tr>{/SUBELEMHEADERTEMPLATE}
       <tr style=”font-weight: bold;”>{SUBELEMROWTEMPLATE:4754}
         <td>{N:DET_Att1}</td>
         <td>{DET_Att2}</td>
         <td>{N:DET_Att3}</td>
         <td>{N:DET_Att4}</td>
       </tr>{/SUBELEMROWTEMPLATE}
     </tbody>
   </table>
</div>
</div>

Leave a Reply

Your email address will not be published. Required fields are marked *