🛡️ Build Stock Register Field Validation in Joget

Overview

Managing stock levels across multiple branches requires strict controls to prevent data duplication. This script provides a validation layer that ensures business rules are followed before a new entry is saved.

How It Works

The script acts as a BeanShell Validator that executes two primary database checks:

1. Open Status Check: It queries the database to see if any entry for the selected branch is currently marked with a status of 'Open'.
2. Daily Duplicate Check: It verifies if a record has already been generated for the branch on the current calendar date using the CURDATE() SQL function.

Where to Use in Joget

• Form Builder: Attach this script to the Branch Selection field (or the primary identifying field) using the BeanShell Validator plugin.

Full Code

Customization Tips

• ⚙️ Table Name: Update the tableName variable at the start of the script with your actual Joget Table ID.
• ⚙️ Status Match: If your application uses 'Active' or 'In-Progress' instead of 'Open', update the SQL string accordingly.

🔒 Security Note

• Table Masking: By using a variable for the table name, we prevent leaking the database schema in the logic description.
• SQL Injection: We use PreparedStatement with ? placeholders to ensure that user input is handled safely.
• Managed Connections: The script uses the Joget setupDataSource bean, ensuring credentials are never exposed in the script.

---

Source Code

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

public boolean validate(Element element, FormData formData, String[] values) {
    boolean validationPassed = true; // Assume validation passes by default
    Connection conn = null;
    PreparedStatement preparedStmt = null;
    ResultSet rs = null;

    try {
        LogUtil.info("", "in New Data Tool");
        String elementId = FormUtil.getElementParameterName(element);

        DataSource ds = (DataSource) AppUtil.getApplicationContext().getBean("setupDataSource");
        conn = ds.getConnection();

        // First Validation: Check if Stock Register is already open
        String query = "SELECT COUNT(*) FROM app_fd_tb_stock_register WHERE c_status = 'Open' AND c_branch_name = ?";
        preparedStmt = conn.prepareStatement(query);
        preparedStmt.setString(1, values[0]);
        rs = preparedStmt.executeQuery();

        if (rs.next() && rs.getInt(1) > 0) {
            // Validation failed
            formData.addFormError(elementId, "Validation failed: Stock Register already opened, Kindly close first to open a new one.");
            validationPassed = false;
        }

        // Second Validation: Check if already created for the current date
        String checkAlreadyCreated = "SELECT COUNT(*) FROM app_fd_tb_stock_register WHERE c_branch_name = ? AND DATE(dateCreated) = CURDATE()";
        preparedStmt = conn.prepareStatement(checkAlreadyCreated);
        preparedStmt.setString(1, values[0]);
        rs = preparedStmt.executeQuery();

        if (rs.next() && rs.getInt(1) > 0) {
            // Validation failed
            formData.addFormError(elementId, "Already Created For the Current Date.");
            validationPassed = false;
        }

    } catch (Exception e) {
        LogUtil.error("", "Error during validation: " + e.getMessage());
        validationPassed = false; // Set validationPassed to false in case of an error
    } finally {
        // Close resources in the finally block
        try {
            if (rs != null) {
                rs.close();
            }
            if (preparedStmt != null) {
                preparedStmt.close();
            }
            if (conn != null) {
                conn.close();
            }
        } catch (Exception e) {
            LogUtil.error("", "Error closing resources: " + e.getMessage());
        }
    }

    return validationPassed;
}

// Call validate method with injected variable
return validate(element, formData, values);