Introduction
In this article, we will demonstrate how to create a BeanShell Spreadsheet Validator. The validator in the sample app ensures that all fields are populated and that there are no duplicate email addresses or phone numbers.
App Structure
Figure 1: Child Form
Figure 2: Spreadsheet Field
Figure 3: BeanShell ValidatorÂ
Implementation
Here is what the script inside the spreadsheet looks like:
BeanShell
import java.util.HashSet; import java.util.Set; import org.joget.apps.app.service.AppUtil; import org.joget.apps.form.model.Element; import org.joget.apps.form.model.FormData; import org.joget.apps.form.model.FormRow; import org.joget.apps.form.model.FormRowSet; import org.joget.apps.form.service.FormUtil; public boolean validate(Element element, FormRowSet rows, FormData formData) { boolean result = true; String elementId = element.getPropertyString("id"); // Get the element ID if (rows != null && !rows.isEmpty()) { Set emailSet = new HashSet(); Set phoneSet = new HashSet(); // Iterate over rows using a standard for loop for BeanShell compatibility for (int i = 0; i < rows.size(); i++) { FormRow row = rows.get(i); String email = row.getProperty("email"); String phone = row.getProperty("phone"); String firstName = row.getProperty("first_name"); String lastName = row.getProperty("last_name"); // Check if required fields are empty if (firstName == null || firstName.equals("") || lastName == null || lastName.equals("") || email == null || email.equals("")) { result = false; } // Check for duplicate emails if (email != null && !email.equals("") && !emailSet.add(email)) { result = false; } // Check for duplicate phone numbers, ignoring empty values if (phone != null && !phone.equals("") && !phoneSet.add(phone)) { result = false; } } } if (!result) { formData.addFormError(elementId, "Ensure that the first name, last name, and email fields are filled out, and verify that there are no duplicate email addresses or phone numbers."); } return result; } // Call validate method with injected variables return validate(element, rows, formData);
Runtime
Figure 5: Invalid Input (Duplicate Phone)
Figure 6: Invalid Input (Missing Field Input)
Download the Sample App