Generating Formatted Sequential Document Numbers Across Multiple Tables in Joget

Default ID generators in Joget DX produce random UUIDs for record keys. While UUIDs are ideal for database primary keys, business users and compliance teams require human-readable, formatted sequential reference numbers such as DOC-DEPT-Q3-2026-20260729-001.

Generating sequential reference numbers across multiple tables while preventing sequence gaps or duplicates requires checking existing records for the current year, incrementing the sequence counter safely, and formatting the final string.

In this guide, we'll write a Java/BeanShell Form Store binder script that generates formatted sequential document numbers across multiple Joget tables.

---

Document Number Structure

The generated reference string follows a standardized enterprise format:

DOC-[DeptID]-[Quarter]-[Date]-[Sequence]

• Prefix: Static prefix (e.g., DOC)
• Department: Dynamic Joget Hash Variable (#variable.dept_id#)
• Quarter & Year: Current quarter and year (e.g., Q3-2026)
• Date: Compact date string (20260729)
• Sequence: 3-digit padded counter (001, 002, 003) reset annually.

---

The BeanShell Script

Place this code inside a BeanShell Form Store Binder plugin or execute it within a workflow Tool step upon document submission:

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.time.LocalDateTime;
import java.time.format.DateTimeFormatter;
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.commons.util.LogUtil;

Connection con = null;
PreparedStatement psSelect = null;
PreparedStatement psUpdate = null;
ResultSet rs = null;

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

    if (rows != null && !con.isClosed()) {
        for (FormRow row : rows) {
            String recordType = row.getProperty("type"); // e.g., "complaint" or "report"
            String recordId = row.getId();
            String isApproved = row.getProperty("is_approved");

            if ("true".equalsIgnoreCase(isApproved)) {
                String targetTable = "complaint".equalsIgnoreCase(recordType) 
                    ? "app_fd_complaint_records" 
                    : "app_fd_inspection_reports";

                String prefix = "DOC";
                String deptCode = "#variable.user_dept_code#";

                // Date and Quarter Calculations
                LocalDateTime now = LocalDateTime.now();
                int month = now.getMonthValue();
                int quarter = (month - 1) / 3 + 1;
                String year = String.valueOf(now.getYear());
                String dateStr = now.format(DateTimeFormatter.ofPattern("yyyyMMdd"));
                String quarterStr = "Q" + quarter + "-" + year;

                // Query max existing sequence counter for current year across both tables
                int nextSeq = 1;
                String seqQuery = "SELECT c_doc_number FROM ( " +
                                  "  SELECT c_doc_number FROM app_fd_complaint_records WHERE c_doc_number LIKE ? " +
                                  "  UNION ALL " +
                                  "  SELECT c_doc_number FROM app_fd_inspection_reports WHERE c_doc_number LIKE ? " +
                                  ") combined ORDER BY c_doc_number DESC LIMIT 1";

                psSelect = con.prepareStatement(seqQuery);
                psSelect.setString(1, prefix + "-%" + year + "%");
                psSelect.setString(2, prefix + "-%" + year + "%");
                rs = psSelect.executeQuery();

                if (rs.next()) {
                    String lastCode = rs.getString("c_doc_number");
                    if (lastCode != null && lastCode.contains("-")) {
                        String[] parts = lastCode.split("-");
                        String lastSeqStr = parts[parts.length - 1];
                        try {
                            nextSeq = Integer.parseInt(lastSeqStr) + 1;
                        } catch (NumberFormatException e) {
                            nextSeq = 1;
                        }
                    }
                }
                rs.close();
                psSelect.close();

                // Format sequence counter with zero-padding (e.g. 001)
                String paddedSeq = String.format("%03d", nextSeq);
                String fullDocNumber = prefix + "-" + deptCode + "-" + quarterStr + "-" + dateStr + "-" + paddedSeq;

                // Persist the generated document number back to the database record
                String updateQuery = "UPDATE " + targetTable + " SET c_doc_number=? WHERE id=?";
                psUpdate = con.prepareStatement(updateQuery);
                psUpdate.setString(1, fullDocNumber);
                psUpdate.setString(2, recordId);
                psUpdate.executeUpdate();
                psUpdate.close();

                LogUtil.info("Document Numbering", "Generated Reference Code for " + recordId + ": " + fullDocNumber);
            }
        }
    }
} catch (Exception e) {
    LogUtil.error("Document Numbering", e, "Error generating sequential document number");
} finally {
    if (rs != null) try { rs.close(); } catch (Exception e) {}
    if (psSelect != null) try { psSelect.close(); } catch (Exception e) {}
    if (psUpdate != null) try { psUpdate.close(); } catch (Exception e) {}
    if (con != null) try { con.close(); } catch (Exception e) {}
}

---

Best Practices

• Zero Padding: Using %03d formats 1 as 001, ensuring standard alphabetical sorting in DataLists matches numerical order.
• Combined UNION Queries: Querying multiple tables simultaneously ensures sequence numbers remain synchronized across related module tables.