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

  • No labels