Programmatically Cleaning Up Dynamic User Groups in Joget Directory

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:

1. Delete Custom Form Record: Delete the group definition row from the custom application form table (app_fd_group_creation).
2. Remove User Assignments: Delete user-to-group mappings from dir_user_group for the target groupId.
3. 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.