Generating Multilingual HTML Reports with Attachment Download Links in Joget

Creating customized executive report summaries in Joget DX often requires more than simple database lists. Real-world business reports frequently need to join multiple tables, translate status labels based on the user's active locale (#platform.currentLocale#), and generate secure file download links for form attachments.

In this guide, we'll build a Java/BeanShell script that queries main records and history logs, resolves internationalization (i18n) message keys dynamically, and generates interactive HTML reports embedded with secure attachment links.

---

Key Components

1. Dynamic i18n Translation: Uses AppUtil.processHashVariable("#i18n.key#", null, null, null) to convert database status codes into localized text matching the user's language setting.
2. File Attachment Links: Formats secure file download URLs (/jw/web/client/app/{appId}/{version}/form/download/{tableName}/{recordId}/{fileName}) so users can open uploaded documents directly from the report summary.
3. Multi-Table SQL Join: Merges main request details, audit transaction history, and custom review tables into a clean HTML document layout.

---

The BeanShell Script

Place this code inside a BeanShell Form Bounding Box or an HTML Report Generator tool step:

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

// Helper: Resolve i18n hash variables dynamically
public String getLocalizedText(String messageKey) {
    if (messageKey == null || messageKey.isEmpty()) return "";
    String hashVariable = "#i18n." + messageKey + "#";
    return AppUtil.processHashVariable(hashVariable, null, null, null);
}

String recordId = "#requestParam.id#";
if (recordId == null || recordId.trim().isEmpty()) {
    return "<div style='padding:15px; color:#ff4d4f;'>No valid record ID specified.</div>";
}

AppDefinition appDef = AppUtil.getCurrentAppDefinition();
String appId = appDef.getId();
String appVersion = String.valueOf(appDef.getVersion());

DataSource ds = (DataSource) AppUtil.getApplicationContext().getBean("setupDataSource");
Connection conn = null;
PreparedStatement psMain = null;
ResultSet rsMain = null;
StringBuilder html = new StringBuilder();

try {
    conn = ds.getConnection();

    // 1. Query Main Record & Attachment Filenames
    String mainSql = "SELECT c_title, c_ref_number, c_request_type, c_attachment_file " +
                     "FROM app_fd_inspection_requests WHERE id = ?";
    psMain = conn.prepareStatement(mainSql);
    psMain.setString(1, recordId);
    rsMain = psMain.executeQuery();

    if (rsMain.next()) {
        String title = rsMain.getString("c_title");
        String refNumber = rsMain.getString("c_ref_number");
        String requestTypeKey = rsMain.getString("c_request_type");
        String fileName = rsMain.getString("c_attachment_file");

        String localizedType = getLocalizedText(requestTypeKey);

        html.append("<div style='font-family: Arial, sans-serif; padding: 20px; max-width: 900px; margin: auto;'>");
        html.append("<h2 style='color: #003366; border-bottom: 2px solid #003366; padding-bottom: 8px;'>Inspection Summary Report</h2>");

        html.append("<table style='width:100%; border-collapse: collapse; margin-bottom: 20px;'>");
        html.append("<tr><td style='font-weight:bold; width:200px;'>Reference Number:</td><td>").append(refNumber != null ? refNumber : "").append("</td></tr>");
        html.append("<tr><td style='font-weight:bold;'>Request Title:</td><td>").append(title != null ? title : "").append("</td></tr>");
        html.append("<tr><td style='font-weight:bold;'>Category (Localized):</td><td>").append(localizedType).append("</td></tr>");

        // Format File Attachment Link if file exists
        if (fileName != null && !fileName.trim().isEmpty()) {
            String encodedFileName = URLEncoder.encode(fileName, "UTF-8");
            String downloadUrl = "/jw/web/client/app/" + appId + "/" + appVersion + 
                                "/form/download/app_fd_inspection_requests/" + recordId + "/" + encodedFileName;

            html.append("<tr><td style='font-weight:bold;'>Attached Document:</td><td>");
            html.append("<a href='").append(downloadUrl).append("' target='_blank' style='color:#0066cc; text-decoration:underline;'>");
            html.append("📎 ").append(fileName);
            html.append("</a></td></tr>");
        }
        html.append("</table>");

    } else {
        return "<div style='padding:15px; color:#ff4d4f;'>Record not found: " + recordId + "</div>";
    }

    // 2. Query & Append Transaction History Audit Entries
    String historySql = "SELECT c_status, dateCreated, c_by FROM app_fd_request_history WHERE c_fk = ? ORDER BY dateCreated ASC";
    PreparedStatement psHistory = conn.prepareStatement(historySql);
    psHistory.setString(1, recordId);
    ResultSet rsHistory = psHistory.executeQuery();

    html.append("<h4 style='color: #333;'>Workflow Approval History</h4>");
    html.append("<ul style='list-style-type: square; padding-left: 20px;'>");

    while (rsHistory.next()) {
        String statusKey = rsHistory.getString("c_status");
        String dateCreated = rsHistory.getString("dateCreated");
        String actionBy = rsHistory.getString("c_by");

        String localizedStatus = getLocalizedText(statusKey);

        html.append("<li style='margin-bottom: 6px;'>");
        html.append("<strong>").append(localizedStatus).append("</strong> - ");
        html.append("<span style='color:#666;'>").append(dateCreated).append("</span> ");
        if (actionBy != null) {
            html.append("(by <em>").append(actionBy).append("</em>)");
        }
        html.append("</li>");
    }
    html.append("</ul>");
    html.append("</div>");

} catch (Exception e) {
    LogUtil.error("Report Generator", e, "Error building HTML drill-down report");
} finally {
    if (rsMain != null) try { rsMain.close(); } catch (Exception e) {}
    if (psMain != null) try { psMain.close(); } catch (Exception e) {}
    if (conn != null) try { conn.close(); } catch (Exception e) {}
}

return html.toString();

---

Key Benefits

• Localized Outputs: Dynamically adapts labels to Arabic, English, or French matching the active user session.
• Direct File Downloads: Solves the problem of accessing Joget uploaded files from custom report views.
• Embedded Userview Integration: Plugs directly into HTML container elements inside Joget Userview dashboards.