DEV Community: Explorer

Dynamic Multi-Select Dropdown Options via BeanShell Options Binder in Joget

Explorer — Wed, 29 Jul 2026 09:53:15 +0000

In Joget DX forms, standard cascading drop-down lists work well when selecting a single parent item. However, when a user selects multiple items in a multi-select box or checkbox group, cascading target options requires a custom BeanShell Form Options Binder.

In this guide, we'll write a Java/BeanShell script that receives an array of selected parent keys, queries related child items from the database using SQL PreparedStatements, and returns a populated FormRowSet formatted for Joget Select Box and Multi-Select elements.

How It Works

Array Inspection: When a parent multi-select field changes, the options binder receives an array of selected keys (values).
Prepared Statement Loop: The script iterates through the selected parent IDs, executing a parameterized query to fetch corresponding child options.
FormRowSet Construction: For each database match, a FormRow object is created with two essential properties:
- FormUtil.PROPERTY_VALUE: The stored value / primary key.
- FormUtil.PROPERTY_LABEL: The display label shown in the UI.

The BeanShell Form Options Binder Script

Open your Joget Form in Form Builder, select the target Multi-Select or Select Box element, set Options Binder to BeanShell Form Options Binder, and paste the script below:

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.apps.form.model.FormRow;
import org.joget.apps.form.model.FormRowSet;
import org.joget.apps.form.service.FormUtil;
import org.joget.commons.util.LogUtil;

FormRowSet optionRows = new FormRowSet();

// Ensure parent selection contains values
if (values != null && values.length > 0) {
    Connection con = null;
    PreparedStatement ps = null;
    ResultSet rs = null;

    try {
        DataSource ds = (DataSource) AppUtil.getApplicationContext().getBean("setupDataSource");
        con = ds.getConnection();

        if (con != null && !con.isClosed()) {
            // Build SQL query for matching child records based on selected parent categories
            String sql = "SELECT id, c_supplier_name, c_registration_code " +
                         "FROM app_fd_registered_suppliers " +
                         "WHERE c_category_id = ? " +
                         "ORDER BY c_supplier_name ASC";

            ps = con.prepareStatement(sql);

            for (Object valObj : values) {
                if (valObj == null) continue;
                String selectedCategoryId = valObj.toString().trim();
                if (selectedCategoryId.isEmpty()) continue;

                ps.setString(1, selectedCategoryId);
                rs = ps.executeQuery();

                while (rs.next()) {
                    String supplierId = rs.getString("id");
                    String supplierName = rs.getString("c_supplier_name");
                    String code = rs.getString("c_registration_code");

                    FormRow row = new FormRow();
                    row.setProperty(FormUtil.PROPERTY_VALUE, supplierId);
                    row.setProperty(FormUtil.PROPERTY_LABEL, supplierName + " (" + code + ")");

                    optionRows.add(row);
                }
                rs.close();
            }
        }
    } catch (Exception e) {
        LogUtil.error("MultiSelect Options Binder", e, "Error fetching dynamic options");
    } finally {
        if (rs != null) try { rs.close(); } catch (Exception e) {}
        if (ps != null) try { ps.close(); } catch (Exception e) {}
        if (con != null) try { con.close(); } catch (Exception e) {}
    }
}

return optionRows;

AJAX Trigger Setup in Form Builder

To update the target multi-select dropdown immediately when the parent field selection changes without reloading the page:

Edit the Target Multi-Select / Select Box in Form Builder.
Under Advanced Options, check AJAX Change Option.
Set Listen to Field to your parent Multi-Select element ID (e.g. category_ids).

Key Advantages

Multi-Parent Filtering: Unlike standard single-select cascading dropdowns, this script handles multi-selection filtering effortlessly.
SQL Injection Safe: Uses PreparedStatement.setString() to execute queries safely.

Preventing Duplicate Column Values in Joget Form Grids via BeanShell

Explorer — Wed, 29 Jul 2026 09:52:54 +0000

When users submit tabular data inside a Joget DX Form Grid (such as multi-line expense claims, asset transfers, or approver lists), users frequently enter duplicate items by accident.

While standard form validators check whether individual cells are empty or match a format, they cannot compare cells across different grid rows.

In this guide, we'll write a custom BeanShell Form Validator script attached to a Form Grid that detects duplicate column values across grid rows, identifies the exact row numbers where duplicates occur, and halts form submission with a clear error message.

How It Works

Row Iteration: The validator receives the submitted grid rows as a FormRowSet collection (rows).
Frequency Map Tracking: Using a HashMap<String, List<Integer>>, the script tracks each value along with a list of row indices where that value appears.
Descriptive Error Reporting: If any value occurs more than once, an error message specifies the exact duplicate value and row locations (e.g., Duplicate Serial Number 'SN-9021' found at Rows: 2, 5).

The BeanShell Form Validator Script

In Joget Form Builder, edit your Form Grid element, set Validator to BeanShell Validator, and paste the script below:

import org.joget.apps.form.model.Element;
import org.joget.apps.form.model.FormData;
import org.joget.apps.form.model.FormRow;
import org.joget.apps.form.service.FormUtil;
import org.joget.commons.util.LogUtil;
import java.util.*;

public boolean validateGridDuplicates(Element element, FormData formData, FormRowSet rows) {
    String elementId = FormUtil.getElementParameterName(element);

    if (rows == null || rows.isEmpty()) {
        return true; // No rows to validate
    }

    // Name of the grid column property to check for duplicates
    String targetColumnProperty = "serial_number";

    // Map: Key = Column Value, Value = List of 1-based Row Numbers
    Map valueOccurrencesMap = new HashMap();
    int currentRowNumber = 0;

    for (Object oRow : rows) {
        FormRow row = (FormRow) oRow;
        currentRowNumber++;

        String cellValue = row.getProperty(targetColumnProperty);
        if (cellValue != null && !cellValue.trim().isEmpty()) {
            cellValue = cellValue.trim();

            if (!valueOccurrencesMap.containsKey(cellValue)) {
                valueOccurrencesMap.put(cellValue, new ArrayList());
            }
            List rowList = (List) valueOccurrencesMap.get(cellValue);
            rowList.add(Integer.valueOf(currentRowNumber));
        }
    }

    // Identify duplicates (values appearing in more than 1 row)
    boolean hasDuplicates = false;
    for (Object oEntry : valueOccurrencesMap.entrySet()) {
        Map.Entry entry = (Map.Entry) oEntry;
        String val = (String) entry.getKey();
        List rowIndices = (List) entry.getValue();

        if (rowIndices.size() > 1) {
            hasDuplicates = true;

            // Format row indices (e.g., "2, 5")
            StringBuilder rowsStr = new StringBuilder();
            for (int i = 0; i < rowIndices.size(); i++) {
                rowsStr.append(rowIndices.get(i).toString());
                if (i < rowIndices.size() - 1) {
                    rowsStr.append(", ");
                }
            }

            String errorMsg = "Duplicate '" + val + "' found in grid at Row(s): " + rowsStr.toString() + ". Duplicate entries are not allowed.";
            formData.addFormError(elementId, errorMsg);
            LogUtil.warn("Grid Validator", errorMsg);
        }
    }

    return !hasDuplicates;
}

// Execute validation against submitted rows
return validateGridDuplicates(element, formData, rows);

Key Benefits

Clear Feedback: Users immediately know which rows contain duplicate data without having to scan through 30+ grid rows manually.
Data Integrity: Prevents duplicate primary entries or conflicting allocations from being stored in relational database tables.

Overriding Joget Form Submission for Multi-Field Validation & Smooth Scrolling

Explorer — Wed, 29 Jul 2026 09:52:35 +0000

In long multi-section forms built with Joget DX, standard field validators display error messages next to each input. When a user submits a long form with multiple missing fields, they often have to scroll up and down repeatedly to find which required fields failed.

In this guide, we'll write a JavaScript snippet that intercepts Joget's submit button (#section-actions input), collects all unfulfilled field requirements, displays a clean SweetAlert popup summary, and smooth-scrolls directly to the first invalid field.

How It Works

Event Interception: The script unbinds Joget's default click handler on section action buttons ($("#section-actions input").not("#cancel, #saveAsDraft").off("click")) and attaches a custom validator wrapper.
Dynamic Inspection: It iterates through a validation matrix, automatically skipping fields that are currently disabled (disabled) or read-only (readonly).
Modal Alert & Smooth Scroll: If validation fails, all missing field notifications are concatenated into a modal alert. Upon closing the alert, element.scrollIntoView({ behavior: "smooth", block: "center" }) automatically focuses the user's screen on the first invalid input.

The Code

Add a Custom HTML element at the bottom of your Joget form and paste the code below:

<!-- Include SweetAlert2 for clean modal notifications (Optional) -->
<script src="https://cdn.jsdelivr.net/npm/sweetalert2@11"></script>

<script>
$(document).ready(function () {
  // Allow Joget internal scripts to finish initializing
  setTimeout(function () {
    const $submitButtons = $("#section-actions input").not("#cancel, #saveAsDraft");

    // Remove default submit handler and attach custom validation suite
    $submitButtons.off("click").on("click", function (event) {
      event.preventDefault();
      const $clickedButton = $(this);

      // Define validation rules array
      const validationRules = [
        {
          element: "input[name=request_date]",
          value: $("input[name=request_date]").val()?.trim(),
          isDisabled: $("input[name=request_date]").prop("disabled") || $("input[name=request_date]").prop("readonly"),
          message: "• Please select the Request Date."
        },
        {
          element: "select[name=department_category]",
          value: $("select[name=department_category]").val()?.trim(),
          isDisabled: $("select[name=department_category]").prop("disabled"),
          message: "• Please select a Department Category."
        },
        {
          element: "textarea[name=justification]",
          value: $("textarea[name=justification]").val()?.trim(),
          isDisabled: $("textarea[name=justification]").prop("disabled"),
          message: "• Please provide a business Justification."
        }
      ];

      let firstInvalidSelector = null;
      const errorList = [];

      // Inspect enabled fields only
      validationRules.forEach(rule => {
        if (!rule.isDisabled) {
          const isValid = !!rule.value;
          if (!isValid) {
            errorList.push(rule.message);
            if (!firstInvalidSelector) {
              firstInvalidSelector = rule.element;
            }
          }
        }
      });

      // Display validation modal if errors are present
      if (errorList.length > 0) {
        if (typeof Swal !== "undefined") {
          Swal.fire({
            title: "Required Fields Missing",
            html: "<div style='text-align:left; font-size:14px; line-height:1.6;'>" + errorList.join("<br>") + "</div>",
            icon: "warning",
            confirmButtonColor: "#0056b3",
            confirmButtonText: "Review Form"
          }).then(() => {
            // Smooth-scroll screen to first invalid field
            setTimeout(() => {
              const targetElem = document.querySelector(firstInvalidSelector);
              if (targetElem) {
                targetElem.scrollIntoView({ behavior: "smooth", block: "center" });
                targetElem.focus();
              }
            }, 200);
          });
        } else {
          alert("Please complete the following required fields:\n\n" + errorList.join("\n"));
        }
        return false;
      }

      // All validations passed - submit form normally
      $clickedButton.closest("form").submit();
    });
  }, 800);
});
</script>

Key Benefits

Better User Experience: Displays a single consolidated popup summary rather than multiple scattered error labels.
Auto Focus: Automatically scrolls the browser window to the first field needing attention.
Conditional Aware: Smartly ignores fields hidden or disabled by Joget Form Visibility rules.

Sending Programmatic HTML Emails with Base64 File Attachments in Joget

Explorer — Wed, 29 Jul 2026 09:52:13 +0000

Joget DX provides a standard Email Tool plugin for basic email dispatch. However, when enterprise applications need to convert form file attachments (PDFs, images) into Base64 payloads and dispatch responsive HTML email templates through a custom REST API endpoint, a BeanShell script provides full programmatic control.

In this guide, we'll write a Java/BeanShell script that validates file types, reads form attachments using Joget's FileUtil service, converts them to Base64, and dispatches a JSON POST payload to a custom email service API.

How It Works

Attachment Discovery: Using FileUtil.getFile(fileName, formId, recordId), the script locates uploaded files on the Joget server storage path.
File Validation & Base64 Encoding: The script checks allowed file extensions (.pdf, .png, .jpg), reads the file bytes, and converts them to Base64 strings.
HTML Email Formatting: A responsive HTML string is built using StringBuilder with embedded CSS styling.
REST API Dispatch: An HttpURLConnection dispatches the JSON payload (recipients, subject, HTML body, Base64 attachments) to the target email API endpoint (#appVariable.send_email_api_url#).

The BeanShell Script

Place this code inside a BeanShell Tool workflow step:

import org.joget.apps.app.service.AppUtil;
import org.joget.commons.util.LogUtil;
import org.joget.apps.app.model.AppDefinition;
import org.joget.apps.form.service.FileUtil;
import java.io.File;
import java.io.OutputStream;
import java.net.HttpURLConnection;
import java.net.URL;
import java.nio.charset.StandardCharsets;
import java.nio.file.Files;
import java.util.Base64;
import org.json.JSONObject;
import org.json.JSONArray;

// 1. Helper: Validate allowed attachment file types
public boolean isSupportedFileType(String fileName) {
    if (fileName == null) return false;
    String[] allowedExts = {".pdf", ".png", ".jpg", ".jpeg", ".docx"};
    String lower = fileName.toLowerCase();
    for (String ext : allowedExts) {
        if (lower.endsWith(ext)) return true;
    }
    return false;
}

// 2. Helper: Encode form attachment into Base64 JSON object
public void appendFileAttachment(JSONArray attachmentArray, String fileName, String formId, String recordId) {
    try {
        if (!isSupportedFileType(fileName)) {
            LogUtil.warn("Email Service", "Skipped unsupported file attachment: " + fileName);
            return;
        }

        File fileObj = FileUtil.getFile(fileName, formId, recordId);
        if (fileObj != null && fileObj.exists()) {
            byte[] fileBytes = Files.readAllBytes(fileObj.toPath());
            String base64Data = Base64.getEncoder().encodeToString(fileBytes);

            JSONObject item = new JSONObject();
            item.put("name", fileName);
            item.put("bytes", base64Data);
            attachmentArray.put(item);

            LogUtil.info("Email Service", "Successfully encoded attachment: " + fileName);
        }
    } catch (Exception e) {
        LogUtil.error("Email Service", e, "Error encoding file attachment: " + fileName);
    }
}

// 3. Prepare Email Payload
JSONArray attachments = new JSONArray();
String formId = "purchase_request";
String recordId = "#form.purchase_request.id#";
String attachmentFileName = "#form.purchase_request.attachment#";

// Add attachment if present
if (attachmentFileName != null && !attachmentFileName.trim().isEmpty()) {
    appendFileAttachment(attachments, attachmentFileName, formId, recordId);
}

// 4. Construct Styled HTML Email Body
StringBuilder htmlBody = new StringBuilder();
htmlBody.append("<!DOCTYPE html><html><head><meta charset='UTF-8'>");
htmlBody.append("<style>");
htmlBody.append("body { font-family: Arial, sans-serif; background-color: #f4f6f8; margin: 0; padding: 20px; }");
htmlBody.append(".card { max-width: 600px; background: #ffffff; padding: 30px; border-radius: 8px; border: 1px solid #e1e4e8; margin: auto; }");
htmlBody.append(".header { background: #0056b3; color: #ffffff; padding: 15px 20px; border-radius: 6px 6px 0 0; font-size: 20px; font-weight: bold; }");
htmlBody.append(".content { padding: 20px 0; color: #333333; line-height: 1.6; }");
htmlBody.append(".btn { display: inline-block; background: #0056b3; color: #ffffff; padding: 10px 20px; text-decoration: none; border-radius: 4px; font-weight: bold; margin-top: 15px; }");
htmlBody.append("</style></head><body>");
htmlBody.append("<div class='card'>");
htmlBody.append("<div class='header'>Request Approval Notice</div>");
htmlBody.append("<div class='content'>");
htmlBody.append("<p>Dear Approver,</p>");
htmlBody.append("<p>A new purchase request (<strong>#form.purchase_request.ref_no#</strong>) requires your review.</p>");
htmlBody.append("<p><strong>Requested Amount:</strong> $#form.purchase_request.amount#</p>");
htmlBody.append("<p><strong>Department:</strong> #form.purchase_request.department#</p>");
htmlBody.append("<a href='#appVariable.userview_approval_url#' class='btn'>Open Approval View</a>");
htmlBody.append("</div>");
htmlBody.append("</div></body></html>");

// 5. Dispatch JSON Payload via REST API
String apiUrl = "#appVariable.send_email_api_url#"; // e.g. https://api.your-domain.com/v1/send-email
JSONObject requestJson = new JSONObject();
requestJson.put("to", "#form.purchase_request.approver_email#");
requestJson.put("subject", "Action Required: Request #form.purchase_request.ref_no#");
requestJson.put("htmlBody", htmlBody.toString());
requestJson.put("attachments", attachments);

try {
    URL url = new URL(apiUrl);
    HttpURLConnection conn = (HttpURLConnection) url.openConnection();
    conn.setRequestMethod("POST");
    conn.setRequestProperty("Content-Type", "application/json; charset=UTF-8");
    conn.setDoOutput(true);
    conn.setConnectTimeout(8000);

    OutputStream os = conn.getOutputStream();
    os.write(requestJson.toString().getBytes(StandardCharsets.UTF_8));
    os.flush();
    os.close();

    int responseCode = conn.getResponseCode();
    LogUtil.info("Email Service", "Dispatched email REST request. Response Code: " + responseCode);

} catch (Exception e) {
    LogUtil.error("Email Service", e, "Failed to dispatch REST email API request");
}

Key Benefits

File Attachment Support: Automatically converts server-stored form files (FileUtil.getFile()) into Base64 strings for external API delivery.
Custom HTML Email Branding: Delivers clean inline CSS styling across desktop and mobile email clients.

Handling Asynchronous Webhook Notifications & Callbacks in Joget via BeanShell

Explorer — Wed, 29 Jul 2026 09:51:54 +0000

When integrating Joget DX with external platforms—such as payment gateways, SMS providers, or ERP systems—requests are often processed asynchronously. The external system accepts a request immediately and dispatches an HTTP POST webhook callback to Joget minutes or hours later when processing completes.

Receiving webhook callbacks inside BeanShell API endpoints requires two key tasks:

Safe Variable Type Coercion: Handling parameter arrays (String[]) versus single strings (String) safely without throwing ClassCastException.
FormDataDao Persistence: Saving or updating the notification payload inside a Joget form database table using FormDataDao.

In this guide, we'll write a defensive Java/BeanShell script that receives asynchronous webhook callbacks and logs them cleanly into Joget.

Architecture Overview

Webhook Endpoint: An external system hits your Joget API endpoint with callback parameters (e.g. process_id, status, response_payload, recipient).
Type Extraction: A safe helper function handles parameter type variations (whether passed via URL query params or JSON request bodies).
FormDataDao Save: Instead of executing raw JDBC queries, the script uses FormDataDao to persist a FormRowSet directly into Joget's form storage engine.

The BeanShell Script

Place this code inside your API Builder BeanShell script or custom REST endpoint:

import org.joget.apps.app.service.AppUtil;
import org.joget.apps.form.dao.FormDataDao;
import org.joget.apps.form.model.FormRow;
import org.joget.apps.form.model.FormRowSet;
import org.joget.commons.util.LogUtil;
import java.util.UUID;

// 1. Safe Type Extraction Helper
public String safeExtract(Object param) {
    if (param == null) return "";
    try {
        if (param instanceof String[]) {
            String[] arr = (String[]) param;
            return arr.length > 0 ? arr[0] : "";
        }
        if (param instanceof String) {
            return (String) param;
        }
    } catch (Throwable t) {
        LogUtil.error("Webhook Receiver", t, "Error extracting parameter");
    }
    return "";
}

// 2. Extract Webhook Callback Parameters Safely
String processId    = safeExtract(request.getParameter("process_id"));
String recordId     = safeExtract(request.getParameter("record_id"));
String statusValue  = safeExtract(request.getParameter("status"));
String recipient    = safeExtract(request.getParameter("recipient"));
String responseMsg  = safeExtract(request.getParameter("response_message"));
String moduleName   = safeExtract(request.getParameter("module"));

LogUtil.info("Webhook Receiver", "Received callback for processId: " + processId + " | Status: " + statusValue);

// 3. Persist Log using Joget FormDataDao API
try {
    FormDataDao formDataDao = (FormDataDao) AppUtil.getApplicationContext().getBean("formDataDao");

    String formDefId = "notification_log"; // Joget Form ID
    String tableName = "app_fd_notification_log"; // Database Table Name

    FormRow row = new FormRow();
    row.setId(UUID.randomUUID().toString()); // Generate primary key
    row.setProperty("c_process_id", processId);
    row.setProperty("c_record_id", recordId);
    row.setProperty("c_status", statusValue);
    row.setProperty("c_recipient", recipient);
    row.setProperty("c_response_message", responseMsg);
    row.setProperty("c_module", moduleName);

    FormRowSet rowSet = new FormRowSet();
    rowSet.add(row);

    // Save record to Joget database
    formDataDao.saveOrUpdate(formDefId, tableName, rowSet);
    LogUtil.info("Webhook Receiver", "Successfully saved callback notification log.");

} catch (Exception e) {
    LogUtil.error("Webhook Receiver", e, "Error persisting webhook callback log");
}

Best Practices

Use FormDataDao for Integrity: Utilizing FormDataDao.saveOrUpdate() automatically updates Joget's internal metadata columns (dateCreated, dateModified) and triggers active plugins.
Always Validate Inputs: Ensure process_id or record_id is validated to prevent invalid records from polluting your notification logs.

Sending Automated SMS Notifications from Joget Workflows via BeanShell

Explorer — Wed, 29 Jul 2026 09:51:35 +0000

While email notifications work well for standard office approvals, urgent alerts—such as appointment reminders, multi-factor OTP codes, or emergency escalations—require SMS messaging.

Joget DX enables developers to connect to any external SMS Gateway REST API (e.g., Twilio, Infobip, or local telecom providers) directly from a workflow BeanShell Tool step.

In this guide, we'll build a Java/BeanShell script that dynamically aggregates recipient phone numbers, constructs a bilingual SMS message, and dispatches a JSON POST payload to a REST SMS Gateway API.

How It Works

Phone Number Sanitization & Aggregation: The script retrieves recipient phone numbers from multiple form fields using Hash Variables (#form.request.mobile_no#), strips empty entries, and formats a comma-separated list.
Supplemental Data Lookup: JDBC connection queries additional appointment details (e.g., meeting location or reference status) from internal Joget database tables.
HTTP REST Dispatch: Using java.net.HttpURLConnection, the script sends a UTF-8 encoded JSON POST request to the Gateway URL configured in Joget App Variables (#appVariable.sms_gateway_url#).

The BeanShell Script

Place this code inside a BeanShell Tool step within your Joget workflow:

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.net.HttpURLConnection;
import java.net.URL;
import java.io.OutputStream;
import java.io.InputStreamReader;
import java.io.BufferedReader;
import java.nio.charset.StandardCharsets;
import org.json.JSONObject;
import org.joget.apps.app.service.AppUtil;
import org.joget.commons.util.LogUtil;

// 1. Collect and sanitize phone numbers from form fields
String primaryMobile = "#form.appointment.primary_phone#";
String secondaryMobile = "#form.appointment.secondary_phone#";

StringBuilder mobileBuilder = new StringBuilder();
if (primaryMobile != null && !primaryMobile.trim().isEmpty()) {
    mobileBuilder.append(primaryMobile.trim());
}
if (secondaryMobile != null && !secondaryMobile.trim().isEmpty()) {
    if (mobileBuilder.length() > 0) mobileBuilder.append(",");
    mobileBuilder.append(secondaryMobile.trim());
}

String recipientMobiles = mobileBuilder.toString();
if (recipientMobiles.isEmpty()) {
    LogUtil.warn("SMS Gateway", "No valid mobile numbers provided. Skipping SMS send.");
    return;
}

// 2. Fetch supplemental data from database if needed
String processId = "#process.recordId#";
String location = "Main Headquarters";

Connection con = null;
PreparedStatement ps = null;
ResultSet rs = null;

try {
    javax.sql.DataSource ds = (javax.sql.DataSource) AppUtil.getApplicationContext().getBean("setupDataSource");
    con = ds.getConnection();

    String sql = "SELECT c_location FROM app_fd_appointment_details WHERE c_fk = ?";
    ps = con.prepareStatement(sql);
    ps.setString(1, processId);
    rs = ps.executeQuery();
    if (rs.next() && rs.getString("c_location") != null) {
        location = rs.getString("c_location");
    }
} catch (Exception e) {
    LogUtil.error("SMS Gateway", e, "Error fetching appointment location");
} finally {
    if (rs != null) try { rs.close(); } catch (Exception e) {}
    if (ps != null) try { ps.close(); } catch (Exception e) {}
    if (con != null) try { con.close(); } catch (Exception e) {}
}

// 3. Build SMS Body
String appointmentRef = "#form.appointment.ref_no#";
String appointmentDate = "#form.appointment.date#";

String messageBody = "Reminder: Your appointment (Ref: " + appointmentRef + ") is scheduled for " +
                     appointmentDate + " at " + location + ". Thank you.";

// 4. Send REST POST Payload to SMS Gateway
String smsGatewayUrl = "#appVariable.sms_gateway_endpoint#"; // e.g. https://api.sms-provider.com/v1/send
JSONObject jsonPayload = new JSONObject();
jsonPayload.put("recipients", recipientMobiles);
jsonPayload.put("message", messageBody);

try {
    URL url = new URL(smsGatewayUrl);
    HttpURLConnection conn = (HttpURLConnection) url.openConnection();
    conn.setRequestMethod("POST");
    conn.setRequestProperty("Content-Type", "application/json; charset=UTF-8");
    conn.setRequestProperty("Authorization", "Bearer #appVariable.sms_api_key#");
    conn.setDoOutput(true);
    conn.setConnectTimeout(5000);
    conn.setReadTimeout(5000);

    OutputStream os = conn.getOutputStream();
    os.write(jsonPayload.toString().getBytes(StandardCharsets.UTF_8));
    os.flush();
    os.close();

    int responseCode = conn.getResponseCode();
    LogUtil.info("SMS Gateway", "SMS API Response Code: " + responseCode + " for recipients: " + recipientMobiles);

    if (responseCode >= 200 && responseCode < 300) {
        BufferedReader in = new BufferedReader(new InputStreamReader(conn.getInputStream(), StandardCharsets.UTF_8));
        String inputLine;
        StringBuilder responseStr = new StringBuilder();
        while ((inputLine = in.readLine()) != null) {
            responseStr.append(inputLine);
        }
        in.close();
        LogUtil.info("SMS Gateway", "SMS Gateway Output: " + responseStr.toString());
    } else {
        LogUtil.error("SMS Gateway", null, "Failed to dispatch SMS. HTTP Response Code: " + responseCode);
    }

} catch (Exception e) {
    LogUtil.error("SMS Gateway", e, "Error dispatching HTTP POST request to SMS Gateway");
}

Best Practices

Timeout Configuration: Always set explicit connect (setConnectTimeout) and read timeouts (setReadTimeout) so an unresponsive gateway doesn't lock up active workflow threads.
Store Credentials Securely: Store API keys and endpoint URLs in App Variables (#appVariable.sms_api_key#) rather than hardcoding them in scripts.

Building Dynamic User-Hierarchy DataLists in Joget via BeanShell Binder

Explorer — Wed, 29 Jul 2026 09:51:18 +0000

Standard Joget DX DataLists support simple SQL queries, but complex enterprise reporting frequently requires dynamic permission filtering. For instance, a regional manager should automatically view records matching their department or parent organization hierarchy without exposing data from other branches.

In this guide, we'll write a custom BeanShell DataList Binder script that inspects the logged-in user's department hierarchy in dir_employment, queries matching report records dynamically, and returns a native DataListCollection object.

How It Works

User Hierarchy Lookup: The script queries dir_employment and dir_department using #currentUser.username# to discover the logged-in user's department and parent department IDs.
Dynamic SQL IN Clause: Based on the user's assigned organizational units, a parameterized SELECT query fetches authorized records.
DataListCollection Construction: Each database ResultSet row is mapped into a FormRow object and appended to a DataListCollection instance, which Joget renders natively inside the Userview DataList element.

The BeanShell DataList Binder Script

Open your Joget DataList in DataList Builder, set Data Binder to BeanShell Data Binder, and paste the script below into the Data Rows Source Script:

import org.joget.apps.datalist.model.DataListCollection;
import org.joget.apps.form.model.FormRow;
import org.joget.apps.app.service.AppUtil;
import org.joget.commons.util.LogUtil;
import javax.sql.DataSource;
import java.sql.*;
import java.util.*;

DataListCollection resultList = new DataListCollection();
Connection con = null;
PreparedStatement psDept = null;
PreparedStatement psData = null;
ResultSet rs = null;

try {
    DataSource ds = (DataSource) AppUtil.getApplicationContext().getBean("setupDataSource");
    con = ds.getConnection();

    String currentUsername = "#currentUser.username#";
    String userDeptId = null;
    String parentDeptId = null;

    // 1. Fetch department hierarchy for logged-in user
    String deptSql = "SELECT d1.id AS dept_id, d1.parentId AS parent_id " +
                     "FROM dir_employment e " +
                     "JOIN dir_department d1 ON d1.id = e.departmentId " +
                     "WHERE e.userId = ?";

    psDept = con.prepareStatement(deptSql);
    psDept.setString(1, currentUsername);
    rs = psDept.executeQuery();

    if (rs.next()) {
        userDeptId = rs.getString("dept_id");
        parentDeptId = rs.getString("parent_id");
    }
    rs.close();
    psDept.close();

    // Determine target department filter (use parent department if user is in a sub-unit)
    String effectiveDept = parentDeptId != null ? parentDeptId : userDeptId;

    // 2. Fetch authorized data records
    if (effectiveDept != null && !effectiveDept.isEmpty()) {
        String dataSql = "SELECT id, c_report_title, c_category, c_amount, dateCreated " +
                         "FROM app_fd_department_reports " +
                         "WHERE c_department_id = ? " +
                         "ORDER BY dateCreated DESC";

        psData = con.prepareStatement(dataSql);
        psData.setString(1, effectiveDept);
        rs = psData.executeQuery();

        ResultSetMetaData metaData = rs.getMetaData();
        int columnCount = metaData.getColumnCount();

        // Map SQL results to FormRow objects
        while (rs.next()) {
            FormRow row = new FormRow();
            for (int i = 1; i <= columnCount; i++) {
                String columnName = metaData.getColumnName(i);
                Object val = rs.getObject(i);
                row.setProperty(columnName, val != null ? val.toString() : "");
            }
            resultList.add(row);
        }
    } else {
        LogUtil.warn("DataList Binder", "No department mapping found for user: " + currentUsername);
    }

} catch (Exception e) {
    LogUtil.error("DataList Binder", e, "Error executing custom DataList row binder script");
} finally {
    if (rs != null) try { rs.close(); } catch (Exception e) {}
    if (psData != null) try { psData.close(); } catch (Exception e) {}
    if (con != null) try { con.close(); } catch (Exception e) {}
}

return resultList;

Total Row Count Script

In Joget's Total Data Rows Script tab, return the integer count of available rows for pagination:

import javax.sql.DataSource;
import java.sql.*;
import org.joget.apps.app.service.AppUtil;

Connection con = null;
int totalRows = 0;

try {
    DataSource ds = (DataSource) AppUtil.getApplicationContext().getBean("setupDataSource");
    con = ds.getConnection();

    // Execute matching COUNT(*) query
    PreparedStatement ps = con.prepareStatement("SELECT COUNT(*) FROM app_fd_department_reports");
    ResultSet rs = ps.executeQuery();
    if (rs.next()) {
        totalRows = rs.getInt(1);
    }
    rs.close();
    ps.close();
} catch (Exception e) {
    totalRows = 0;
} finally {
    if (con != null) try { con.close(); } catch (Exception e) {}
}

return totalRows;

Key Advantages

Role-Based Security: Prevents users from accessing unauthorized rows by filtering data before rendering.
Dynamic Columns: Allows adding computed or formatted columns to FormRow before sending data to the DataList view.

Generating Multilingual HTML Reports with Attachment Download Links in Joget

Explorer — Wed, 29 Jul 2026 09:50:58 +0000

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

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.
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.
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.

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

Explorer — Wed, 29 Jul 2026 09:50:37 +0000

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

Child Table Query: Using AppUtil.getApplicationContext().getBean("setupDataSource"), the script queries child grid items associated with the main record ID (c_fk = ?).
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.
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

In Joget Email Tool configuration, set Body Type to HTML.
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>

Validating Username Existence in Joget Forms via BeanShell

Explorer — Wed, 29 Jul 2026 09:50:16 +0000

When users manually type a username into a Joget form field—such as assigning a person in charge, manager, or auditor—standard text inputs do not check if that username actually exists in Joget Directory Manager. If a user mistypes a username, the workflow will throw an error when trying to assign the subsequent activity.

In this guide, we'll write a custom BeanShell Form Validator script that checks dir_user in real time during form submission and displays an inline validation error if the entered username does not exist.

How It Works

Interpreting Input Values: The validator signature validate(Element element, FormData formData, String[] values) receives the user's input from the form field.
Directory Lookup: A parameterized SQL query (SELECT COUNT(*) FROM dir_user WHERE username = ?) checks the Joget Directory user database.
Form Error Injection: If the count is zero, formData.addFormError(elementId, errorMessage) marks the field as invalid and stops form submission.

The BeanShell Form Validator Script

Open your Joget Form in Form Builder, select the target Username field, set Validator to BeanShell Validator, and paste the script below:

import org.joget.apps.form.model.Element;
import org.joget.apps.form.model.FormData;
import org.joget.apps.form.service.FormUtil;
import org.joget.commons.util.LogUtil;
import org.joget.apps.app.service.AppUtil;
import javax.sql.DataSource;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;

public boolean validateUsername(Element element, FormData formData, String[] values) {
    String elementId = FormUtil.getElementParameterName(element);

    // 1. Check for empty input
    if (values == null || values.length == 0 || values[0] == null || values[0].trim().isEmpty()) {
        formData.addFormError(elementId, "Username is required.");
        return false;
    }

    String inputUsername = values[0].trim();
    Connection con = null;
    PreparedStatement stmt = null;
    ResultSet rs = null;
    boolean userFound = false;

    try {
        // 2. Obtain DataSource and check Directory user table
        DataSource ds = (DataSource) AppUtil.getApplicationContext().getBean("setupDataSource");
        con = ds.getConnection();

        if (con != null && !con.isClosed()) {
            String sql = "SELECT COUNT(*) AS user_count FROM dir_user WHERE username = ?";
            stmt = con.prepareStatement(sql);
            stmt.setString(1, inputUsername);
            rs = stmt.executeQuery();

            if (rs.next() && rs.getInt("user_count") > 0) {
                userFound = true;
            }
        }

        // 3. Highlight field with inline error if user does not exist
        if (!userFound) {
            formData.addFormError(elementId, "User '" + inputUsername + "' does not exist in the system directory.");
        }

    } catch (Exception e) {
        LogUtil.error("Directory User Validator", e, "Database error during username validation");
        return false;
    } finally {
        if (rs != null) try { rs.close(); } catch (Exception e) {}
        if (stmt != null) try { stmt.close(); } catch (Exception e) {}
        if (con != null) try { con.close(); } catch (Exception e) {}
    }

    return userFound;
}

// Execute validation
return validateUsername(element, formData, values);

Alternative: Using DirectoryManager API

You can also perform the user lookup using Joget's DirectoryManager API directly:

import org.joget.apps.app.service.AppUtil;
import org.joget.directory.model.User;
import org.joget.directory.model.service.DirectoryManager;

DirectoryManager dirManager = (DirectoryManager) AppUtil.getApplicationContext().getBean("directoryManager");
User user = dirManager.getUserByUsername(inputUsername);

if (user == null) {
    formData.addFormError(elementId, "Specified user was not found.");
    return false;
}
return true;

Pro Tip

Use the direct SQL validator for high-speed bulk form submissions, and dirManager.getUserByUsername() when you also need to check whether the user is currently active (user.getGroupList(), user.getRoleList()).

Programmatically Cleaning Up Dynamic User Groups in Joget Directory

Explorer — Wed, 29 Jul 2026 09:49:58 +0000

In complex Joget DX applications, user groups are often created dynamically—for example, creating temporary project reviewer groups or vendor assignment groups via custom forms.

However, when a project completes or a custom group record is deleted from your form table (app_fd_...), the underlying group mapping still exists inside Joget's Directory Manager (dir_group and dir_user_group). Left unhandled, this leaves orphaned groups in your admin user directory.

In this guide, we'll write a Java/BeanShell script for a Datalist Row Action Binder that cleanly deletes dynamic groups and their user assignments from Joget's Directory Manager.

Deletion Sequence & Relational Integrity

To avoid foreign key constraint errors or orphaned records, the script executes deletion in 3 distinct steps:

Delete Custom Form Record: Delete the group definition row from the custom application form table (app_fd_group_creation).
Remove User Assignments: Delete user-to-group mappings from dir_user_group for the target groupId.
Delete Directory Group: Delete the main group record from dir_group.

The BeanShell Script

Place this code inside a BeanShell Datalist Action or a custom Row Delete Action:

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

// Selected row keys passed from Datalist Row Action
FormRowSet rows = new FormRowSet();

for (String recordId : rowKeys) {
    Connection con = null;
    PreparedStatement psFetch = null;
    PreparedStatement psDeleteForm = null;
    PreparedStatement psDeleteUserGroup = null;
    PreparedStatement psDeleteGroup = null;
    ResultSet rs = null;

    try {
        DataSource ds = (DataSource) AppUtil.getApplicationContext().getBean("setupDataSource");
        con = ds.getConnection();

        if (con != null && !con.isClosed()) {
            // 1. Fetch Directory Group ID associated with the form record
            String fetchSql = "SELECT c_group_id, c_group_name FROM app_fd_dynamic_groups WHERE id = ?";
            psFetch = con.prepareStatement(fetchSql);
            psFetch.setString(1, recordId);
            rs = psFetch.executeQuery();

            String groupId = null;
            if (rs.next()) {
                groupId = rs.getString("c_group_id");
                LogUtil.info("Group Cleanup", "Cleaning up group: " + groupId + " (" + rs.getString("c_group_name") + ")");
            }

            // 2. Delete custom form record
            String deleteFormSql = "DELETE FROM app_fd_dynamic_groups WHERE id = ?";
            psDeleteForm = con.prepareStatement(deleteFormSql);
            psDeleteForm.setString(1, recordId);
            psDeleteForm.executeUpdate();

            // 3. Clean up Directory user-to-group mappings if groupId exists
            if (groupId != null && !groupId.trim().isEmpty()) {
                String deleteUserGroupSql = "DELETE FROM dir_user_group WHERE groupId = ?";
                psDeleteUserGroup = con.prepareStatement(deleteUserGroupSql);
                psDeleteUserGroup.setString(1, groupId);
                int userMappingsRemoved = psDeleteUserGroup.executeUpdate();
                LogUtil.info("Group Cleanup", "Removed " + userMappingsRemoved + " user assignments for group: " + groupId);

                // 4. Delete Directory Group record
                String deleteGroupSql = "DELETE FROM dir_group WHERE id = ?";
                psDeleteGroup = con.prepareStatement(deleteGroupSql);
                psDeleteGroup.setString(1, groupId);
                psDeleteGroup.executeUpdate();
                LogUtil.info("Group Cleanup", "Successfully deleted group: " + groupId);
            }
        }
    } catch (Exception e) {
        LogUtil.error("Group Cleanup", e, "Error deleting group record: " + recordId);
    } finally {
        if (rs != null) try { rs.close(); } catch (Exception e) {}
        if (psFetch != null) try { psFetch.close(); } catch (Exception e) {}
        if (psDeleteForm != null) try { psDeleteForm.close(); } catch (Exception e) {}
        if (psDeleteUserGroup != null) try { psDeleteUserGroup.close(); } catch (Exception e) {}
        if (psDeleteGroup != null) try { psDeleteGroup.close(); } catch (Exception e) {}
        if (con != null) try { con.close(); } catch (Exception e) {}
    }
}

Alternative: Using DirectoryManager Plugin API

If you prefer using Joget's Java API instead of direct SQL statements, you can retrieve DirectoryManager:

import org.joget.apps.app.service.AppUtil;
import org.joget.directory.model.service.DirectoryManager;

DirectoryManager dirManager = (DirectoryManager) AppUtil.getApplicationContext().getBean("directoryManager");
if (dirManager != null && groupId != null) {
    dirManager.deleteGroup(groupId);
}

Why Use SQL PreparedStatements vs API?

Direct SQL prepared statements are ideal when bulk-deleting custom groups in high-volume enterprise apps, whereas dirManager.deleteGroup() handles internal event listeners automatically.

How to Update Joget App Environment Variables Programmatically in BeanShell

Explorer — Wed, 29 Jul 2026 09:49:39 +0000

In Joget DX, App Environment Variables are commonly used to store global configuration values—such as API endpoints, tax rates, batch counter sequences, or feature flags.

While administrators can update these variables manually through Joget App Center, enterprise workflows often need to update environment variables programmatically (for example, incrementing a daily batch sequence counter or updating an OAuth access token).

In this guide, we'll write a short BeanShell script using Joget's EnvironmentVariableDao to fetch and update App Environment Variables dynamically.

How It Works

Obtain App Context: AppUtil.getCurrentAppDefinition() retrieves the active application definition.
Access the DAO Bean: AppUtil.getApplicationContext().getBean("environmentVariableDao") retrieves Joget's internal DAO for environment variables.
Load & Update: environmentVariableDao.loadById(envVarId, appDef) retrieves the target variable instance. Modifying .setValue() and executing environmentVariableDao.update(envVar) persists the updated value immediately.

The Code

Place this BeanShell snippet inside a BeanShell Tool workflow step or a Form Post-Processing Tool:

import org.joget.apps.app.dao.EnvironmentVariableDao;
import org.joget.apps.app.model.AppDefinition;
import org.joget.apps.app.model.EnvironmentVariable;
import org.joget.apps.app.service.AppUtil;
import org.joget.commons.util.LogUtil;

public void updateAppEnvironmentVariable(String variableId, String newValue) {
    AppDefinition appDef = AppUtil.getCurrentAppDefinition();

    if (appDef != null) {
        // Retrieve Joget's Environment Variable DAO bean
        EnvironmentVariableDao envDao = (EnvironmentVariableDao) AppUtil.getApplicationContext().getBean("environmentVariableDao");

        // Load target environment variable by ID
        EnvironmentVariable envVar = envDao.loadById(variableId, appDef);

        if (envVar != null) {
            LogUtil.info("EnvVar Manager", "Current value for '" + variableId + "': " + envVar.getValue());

            // Update value and save to database
            envVar.setValue(newValue);
            envDao.update(envVar);

            LogUtil.info("EnvVar Manager", "Updated '" + variableId + "' to: " + newValue);
        } else {
            LogUtil.warn("EnvVar Manager", "Environment variable '" + variableId + "' not found in app: " + appDef.getId());
        }
    }
}

// Example 1: Update a batch sequence counter
updateAppEnvironmentVariable("last_batch_sequence", "10045");

// Example 2: Update an API auth token
updateAppEnvironmentVariable("api_access_token", "token_xyz98765");

Example Use Cases

Dynamic Sequence Counters: Incrementing custom sequential counters when generating daily batch numbers.
System Feature Toggles: Dynamically enabling or disabling form features (e.g. maintenance_mode = true) during scheduled maintenance.
Cached API Credentials: Saving refreshed access tokens after executing OAuth authentication flows.

Important Note

Environment variables updated via EnvironmentVariableDao.update() take effect immediately across all subsequent form loads and workflow executions within the app.