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 are no duplicate email addresses or phone numbers.

Prerequisites / Assumptions

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

• Child Form
• Parent Form

Steps

Setting up the Child Form

1. 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.

2. For the Hidden Field, set the ID as "fk".

3. For the rest of Text Fields, set the ID and Label as example for Email Text Field:

• Label: Email
• ID: email

Setting up the Parent Form

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

2. On each columns, the Field ID should match exactly as configured in Child Form Text Fields ID. In this example, the IDs are first_name, last_name, phone and email.

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

4. Lastly, scroll down further to the Data Store settings section and select Multiple Form Row for Load Data From.
5. Set the Load Data From and Save Data To settings to Child Form, and Foreign Key as the Hidden Field (fk) as shown below:

Expected outcome

Launch the app. In the Manage Parent Form section, fill in duplicate values in the columns. A "Validation Error" message is displayed when clicking Save.

The image below displays Invalid Input (Duplicate Phone).

The image below displays Invalid Input (Missing Field Input)

Download the Sample App