Notification rule to build an HTML table from a dynamic query

Introduction

This page gives an example of how to send a notification containing HTML-formatted data.

The example considers the use case of a business that needs an app to manage a server reboot approval process. The app must be able to send an email to each server's designated system manager. Servers may have more than one designated manager. The email should contain an HTML table showing the servers to be rebooted.

To set up this example, follow the steps in each section.

Configure email settings

To configure your app's email settings, follow these steps:

1. Configure the SMTP server:

   1. Select IDE > Notification Servers.
   2. Click the open record icon for the SMTP entry. The Notification Endpoint dialog opens.
   3. In the From Address field, enter the sender's email address.
   4. In the Uri field, enter your SMTP server's address, including the protocol prefix (smtp:// or smtps://), and port number suffix (usually :587 but can also be one of :25, :465, or :2525).
   5. Expand the Security section.
   6. Enter the SMTP server's credentials in the User Name and Password fields.
   7. Expand the Testing section.
   8. Enter valid email addresses in the From and To fields, then click the Test Email button. This sends an email to and from these addresses via the configured SMTP server.
   9. When the test is successful, close the Notification Endpoint dialog.

2. Configure your email address:

   1. Select > View User Profile. The User Profile dialog opens.
   2. In the Email Address field, enter a valid email address. (This is the address emails appear to be sent from.)
   3. Press Enter, then close the dialog.

3. Set the default site URL:

   1. Select IDE > Security Providers.
   2. In the Configuration panel, select More > Sites.
   3. Click the open record icon for the row marked as default. The Site dialog opens.
   4. Click the Edit button.
   5. In the URL field, enter a value for the app's URL.
   6. Click the Save button.
   7. Close the dialog.

Create an app

1. From the site menu, click the + App button.
2. In the Name field, enter ServerOwners, then click the Create App button.

Create tables

To set up the tables for this example, select > App Workbench > Tables, then use the + Table and + Column buttons to add the tables and columns in this section.

Table: Servers

This table stores the details of your servers:

Table: Owners

This table stores the details of the people responsible for the servers:

Table ServerOwners

This table connects owners with servers:

Add sample data

To add sample data, do the following:

1. From the App Workbench, click the App Settings icon to run the app.

2. On the app's Home page, for each of Owners, Servers, and Server Owners, select the option, click the Create button, then add enough entries for the example to make sense to you. For example:

   

   

   

Create rules

Rule: ServerOwners (Subquery)

This subquery rule outputs the data in the ServerOwners (Business Rule) rule, with the addition of the names of owners and servers:

1. Create the rule:

   1. Open your app, then select > App Workbench > Rules.

   2. Click the + Rule button.

   3. In the Rule panel on the Rule Builder page, set the following values:

      • Name: ServerOwners (Subquery)
      • Purpose: Subquery
      • Source Data Source: ServerOwners
      • Target: ServerOwners

   4. Click the Create button (or press Control+S).

2. Add tables:

   • Owners:

     1. On the Tables Tab, click the + Tables button. A dialog opens.
     2. Click the Add button on the Owners entry. The dialog closes.

   • Servers:

     1. Click the + Tables button.
     2. Click the Add button on the Servers entry.

3. Select columns: Select the following columns:

   • OwnerName in the Owners table.
   • ServerName in the Servers table.

   • ServerOwnerID in the ServerOwners table.

     

4. Check the rule:

   1. Click the Validate button.

   2. (Optional) Check the results of the rule in the Results tab:

      

Rule: ServerOwnersTable (Subquery)

This subquery rule produces the HTML used in the body notification.

1. Create the rule:

   1. Select > App Workbench > Rules.
   2. Click the + Rule button.

   3. In the Rule panel on the Rule Builder page, set the following values:

      • Name: ServerOwnersTable (Subquery)
      • Purpose: Subquery
      • Source Data Source: ServerOwners
      • Target: ServerOwners

   4. Click the Create button.

2. Add tables:

   • ServerOwners (Subquery):

     1. Click the + Tables button.
     2. Click the Add button for the ServerOwners (Subquery) rule.

3. Join tables: Drag and drop the ServerOwnerID column from ServerOwners to the same column on ServerOwners (Subquery).

4. Select columns: Select all columns in the ServerOwners (Subquery) rule:

   

5. Add columns:

   1. Column count:

      1. Click the + Column button.

      2. In the Column or Expression panel, enter the following:

         count(1)
         

      3. Click the Save button.

   2. Column TableHeader:

      1. Click the + Column button.

      2. In the Column or Expression panel, enter the following:

         '<table><tbody><tr><td>Server Owner</td><td>Server Name</td></tr>'
         

      3. In the Alias field, enter TableHeader.

      4. Click the Save button.

   3. Column TableBody:

      1. Click the + Column button.

      2. In the Column or Expression panel, enter the following:

         GroupConcat('<tr><td>' || SOS.OwnerName || '</td><td>' || SOS.ServerName || '</td></tr>','')
         

      3. In the Alias field, enter TableBody.

      4. Click the Save button.

   4. Column TableClose:

      1. Click the + Column button.

      2. In the Column or Expression panel, enter the following:

         '</table><div>&nbsp;</div> <div>Thanks,</div> <div>Server Team</div>'
         

      3. In the Alias field, enter TableClose.

      4. Click the Save button.

   5. Column TableStyle:

      1. Click the + Column button.

      2. In the Column or Expression panel, enter the following:

         '<style>table, th, td { border: 1px solid black; border-collapse: collapse; }</style>'
         

         Note

         This provides some minimal table styling.

      3. In the Alias field, enter TableStyle.

      4. Click the Save button.

         

6. Check the rule:

   1. Click the Validate button.

   2. (Optional) Check the results of the rule in the Results tab:

      

Rule: ServerOwners (Notification)

This rule generates the notification sent to server owners.

1. Create the rule:

   1. Select > App Workbench > Notifications.
   2. Click the + Notification button.

   3. In the Rule panel on the Rule Builder page, set the following values:

      • Name: ServerOwners (Notification)
      • Purpose: Notification

      • Delivery Method: Email

        Tip

        While testing, or when you don't have an SMTP server, set Delivery Method to App Builder Alert.

   4. Click the Create button.

2. Add tables:

   • ServerOwnersTable (Subquery):

     1. Click the + Tables button.
     2. Click the Add button for the ServerOwnersTable (Subquery) entry.

   • ServerOwners (Business Object):

     1. Click the + Tables button.
     2. Click the Add button for the ServerOwners (Business Object) entry.

   • Owners (Business Object):

     1. Click the + Tables button.
     2. Click the Add button for the Owners (Business Object) entry.

3. Join tables: Drag and drop the ServerOwnerID column from ServerOwnersTable (Subquery) to the same column on ServerOwners (Business Object).

   Tip

   The OwnerID join between ServerOwners (Business Object) and Owners (Business Object) is made automatically.

4. Add columns:

   1. Column To():

      1. Select the Columns tab.
      2. Click the + Column button.

      3. In the Column or Expression panel, enter the following:

         OBO.OwnerEmailAddress
         

      4. In the Alias field, enter OwnerEmail.

      5. Open the Target menu, then select To().
      6. Click the Save button.

   2. Column Binding():

      1. Click the + Column button.

      2. In the Column or Expression panel, enter the following:

         SOBO.ServerOwnerID
         

      3. Open the Target menu, then select Binding().

      4. Click the Save button.

   3. Column Table:

      1. Click the + Column button.

      2. In the Column or Expression panel, enter the following:

         GroupConcat(SOTS.TableStyle || SOTS.TableHeader || SOTS.TableBody || SOTS.TableClose, '')
         

      3. In the Alias field, enter Table.

      4. Click the Save button.

         

   4. Check the rule:

      1. Click the Validate button.

      2. (Optional) Check the results of the rule in the Results tab:

         

Test

1. (Optional) Run the app, select the Owners page, then check the owner email addresses are valid.
2. Select > App Workbench > Notifications.
3. In the Notification Rules panel, select ServerOwners (Notification). (This is already selected if it's the only notification rule.)
4. In the Message panel, click the Edit button.
5. In the Subject field, enter your choice of subject.

6. In the Body edit panel, enter the following:

   {{Table|HTML}}
   

7. Click the Save button.

8. Click the Test button, then click the Proceed button.
9. Check your email inbox for the message. (If you are testing with an App Builder Alert, a toast message appears containing the body of the message.)