Font Size:

Creating a BeanShell Spreadsheet Validator

Introduction

This article demonstrates how to create a BeanShell Spreadsheet Validator.

How does it work?

The validator in the sample app ensures that all fields are populated and that there is no duplicate email addresses or phone numbers.

Prerequisites / Assumptions

In this exercise, we assume that you are familiar working with Form Builder and have already created empty forms for:

Child Form
Parent Form

Step 1: Child Form

In the Child Form, drag and drop Text Fields for First Name, Last Name, Email, Phone, and a Hidden Field for foreign key (fk) to link it with the Parent Form, which will have the Spreadsheet Element.

Step 2: Parent Form

In the Parent Form, drag a Spreadsheet Element, then add First Name, Last Name, Email, and Phone into the Configure Spreadsheet > Columns settings section.

Next, scroll down to the Data & Validation settings section. Set the Validator to BeanShell (Multirow) and copy & paste the provided full BeanShell script into the Script content as shown below.

The full BeanShell script for the spreadsheet validation:

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

Lastly, scroll down further to the Data Store settings section. Set the Load Data From and Save Data To settings as shown below