Home > Knowledge Base > Forms > Configuration of the Item list and Data table form field in e-mail templates

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

01.09.2020

Forms

#data table #item list #SQL table

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:

Basic knowledge of HTML language (specifically building tables)
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 ist 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 “” 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 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 tag and put the first row in their, which the column headers will be stored:

Fig. 11. The column headers

In the 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 columns 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. Is it not beautiful that the digital transformation can be that simple when you are using WEBCON BPS?

The HTML code