Generating Dynamic HTML Data Tables for Email Templates & Reports in Joget

When sending approval emails or rendering summary pages in Joget DX, built-in Hash Variables (#form.table.field#) can only display single values. When your form includes child grid items (such as line item requests, order items, or expense rows), you need a way to build a formatted HTML table dynamically.

In this guide, we'll write a Java/BeanShell script that queries child records from the database, builds a responsive HTML <table>, and stores the result inside a Joget App Variable or Form Field for use in email notifications and Userview summary screens.

---

How It Works

1. Child Table Query: Using AppUtil.getApplicationContext().getBean("setupDataSource"), the script queries child grid items associated with the main record ID (c_fk = ?).
2. Conditional Header & Data Rendering: Depending on the approval status (e.g., All Approved, Partially Approved, Rejected), the query filters relevant child rows and appends them to a StringBuilder constructing an HTML table.
3. Email & Form Integration: The generated HTML string is stored into an App Variable (#variable.html_report#) or form field, allowing Joget's Email Tool to send clean, pre-styled HTML email summaries.

---

The BeanShell Script

Place this code inside an App Variable / Form Load Binder or a BeanShell Tool step preceding your Email Tool:

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import javax.sql.DataSource;
import org.joget.apps.app.service.AppUtil;
import org.joget.commons.util.LogUtil;

String parentRecordId = "#form.main_request.id#";
String requestStatus = "#form.main_request.c_status#";

DataSource ds = (DataSource) AppUtil.getApplicationContext().getBean("setupDataSource");
Connection conn = null;
PreparedStatement ps = null;
ResultSet rs = null;

StringBuilder html = new StringBuilder();

try {
    conn = ds.getConnection();

    // Filter child items based on overall request status
    String sql = "";
    String statusTitle = "";

    if ("Approved".equalsIgnoreCase(requestStatus)) {
        sql = "SELECT * FROM app_fd_product_items WHERE c_fk = ? AND c_item_status = 'Approved'";
        statusTitle = "Approved Items Summary";
    } else if ("Rejected".equalsIgnoreCase(requestStatus)) {
        sql = "SELECT * FROM app_fd_product_items WHERE c_fk = ? AND c_item_status = 'Rejected'";
        statusTitle = "Rejected Items Notice";
    } else {
        sql = "SELECT * FROM app_fd_product_items WHERE c_fk = ?";
        statusTitle = "Item Processing Summary";
    }

    ps = conn.prepareStatement(sql);
    ps.setString(1, parentRecordId);
    rs = ps.executeQuery();

    // Construct Responsive HTML Table Output
    html.append("<div style='font-family: Arial, sans-serif; font-size: 14px; color: #333;'>");
    html.append("<h3 style='color: #0056b3;'>").append(statusTitle).append("</h3>");
    html.append("<table style='width:100%; border-collapse: collapse; margin-top: 10px;'>");
    html.append("<thead>");
    html.append("<tr style='background-color: #f2f4f7; text-align: left;'>");
    html.append("<th style='padding: 8px; border: 1px solid #ddd;'>#</th>");
    html.append("<th style='padding: 8px; border: 1px solid #ddd;'>Item Code</th>");
    html.append("<th style='padding: 8px; border: 1px solid #ddd;'>Description</th>");
    html.append("<th style='padding: 8px; border: 1px solid #ddd;'>Quantity</th>");
    html.append("<th style='padding: 8px; border: 1px solid #ddd;'>Unit Price</th>");
    html.append("<th style='padding: 8px; border: 1px solid #ddd;'>Status</th>");
    html.append("</tr>");
    html.append("</thead>");
    html.append("<tbody>");

    int itemNumber = 1;
    while (rs.next()) {
        String itemCode = rs.getString("c_item_code");
        String description = rs.getString("c_description");
        String quantity = rs.getString("c_quantity");
        String price = rs.getString("c_unit_price");
        String itemStatus = rs.getString("c_item_status");

        html.append("<tr style='border-bottom: 1px solid #eee;'>");
        html.append("<td style='padding: 8px; border: 1px solid #ddd;'>").append(itemNumber++).append("</td>");
        html.append("<td style='padding: 8px; border: 1px solid #ddd;'>").append(itemCode != null ? itemCode : "").append("</td>");
        html.append("<td style='padding: 8px; border: 1px solid #ddd;'>").append(description != null ? description : "").append("</td>");
        html.append("<td style='padding: 8px; border: 1px solid #ddd;'>").append(quantity != null ? quantity : "1").append("</td>");
        html.append("<td style='padding: 8px; border: 1px solid #ddd;'>$").append(price != null ? price : "0.00").append("</td>");
        html.append("<td style='padding: 8px; border: 1px solid #ddd;'>").append(itemStatus != null ? itemStatus : "Pending").append("</td>");
        html.append("</tr>");
    }

    html.append("</tbody>");
    html.append("</table>");
    html.append("</div>");

} catch (Exception e) {
    LogUtil.error("HTML Report Builder", e, "Error generating HTML table summary");
} finally {
    if (rs != null) try { rs.close(); } catch (Exception e) {}
    if (ps != null) try { ps.close(); } catch (Exception e) {}
    if (conn != null) try { conn.close(); } catch (Exception e) {}
}

return html.toString();

---

How to Use in Email Templates

1. In Joget Email Tool configuration, set Body Type to HTML.
2. Insert #variable.html_report# directly inside your email message body template:

<p>Dear Customer,</p>
<p>Your request has been processed. Here is the breakdown of your submitted items:</p>

#variable.html_report#

<p>Thank you,</p>
<p>Operations Team</p>