Building Dynamic User-Hierarchy DataLists in Joget via BeanShell Binder

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

1. 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.
2. Dynamic SQL IN Clause: Based on the user's assigned organizational units, a parameterized SELECT query fetches authorized records.
3. 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.