🛡️ Build Stock Register Field Validation in Joget

DEV Community

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);