Grid Integration & Data Management

Introduction

Explore how to seamlessly integrate a grid into your application and efficiently manage form data using the Bean Shell Form Binder. This comprehensive guide covers loading and storing data within the grid, empowering you to optimize data management within your Joget applications.

Add a grid to your application

Establishing a clear relationship between different forms is important when designing complex applications like a leave management system in Joget. Here’s how to seamlessly integrate a grid that links a single leave application to multiple leave details.

Understanding relationships

In applications that involve multiple forms, understanding the relationships between these forms is important. The example provided here demonstrates the relationship between a primary form and a secondary form:

• Primary Form: leaveApplication
• Secondary Form: leaveDetails
  
• Relationship: Each leave application encompasses various details, forming a one-to-many relationship.

Configure the grid

Setting up your grid involves two main steps:

Storing data

By default, the system stores data in JSON format directly in the leaveApplication parent table, ensuring quick access and integrity.

Storing in a separate table

For enhanced flexibility, you might opt to store entries in a separate table:

• Binder Configuration: Employ the Multirow Form Binder as the Store Binder and Load Binder.
• Parent ID Setup: Create a dedicated field to maintain the parent ID in the chosen table. This field ensures each entry in the grid is linked to its respective leave application.
• Field Consistency: Ensuring that each "value" in the grid's properties precisely matches the corresponding fields in your table is critical.
  

Practical example

Let’s visualize how data appears in our tables:

• Parent Table (leave_application):

  Primary Key	Name	...
  1550	David	...

• Child Table (leave_details):

  Primary Key	Foreign Key	Other Fields
  800	1550	...
  801	1550	...
  802	1550	...

Each detail record has a unique identifier that is distinct and traceable to its leave application.

Load & store form grid data using bean shell form data store

Learn how to load and store data in a Form Grid using the Bean Shell Form Binder within Joget. This functionality is essential for efficiently managing and interacting with data across multiple database tables. It provides a practical approach for developers and administrators looking to enhance data operations in their applications.

Load data store using Bean Shell form data store

Import Necessary Classes and Establish Connection:

import org.joget.apps.form.model.*;
import org.joget.apps.form.service.*;
import java.sql.*;
import java.util.*;
import javax.sql.DataSource;
import org.joget.apps.app.service.AppUtil;

FormRowSet f = new FormRowSet();
f.setMultiRow(true);

// Retrieve current datasource configurations from Joget
DataSource ds = (DataSource) AppUtil.getApplicationContext().getBean("setupDataSource");
Connection con = ds.getConnection();

if (!con.isClosed()) {
    // Retrieve URL parameter
    String recordId = "#requestParam.id#";

    // Here you can query from one or multiple tables using JOIN, etc.
    String sql = "SELECT * FROM your_table_name WHERE id=?";
    PreparedStatement stmt = con.prepareStatement(sql);
    stmt.setString(1, recordId);

    // Execute the SELECT SQL statement
    ResultSet rs = stmt.executeQuery();

    // Get value from columns of the record(s)
    while (rs.next()) {
        FormRow r1 = new FormRow();
        r1.put("gridColumn1", rs.getString(1));
        r1.put("gridColumn2", rs.getString(2));
        r1.put("gridColumn3", rs.getString(3));
        f.add(r1);
    }
}

return f;

Store data store using Bean Shell form data store

Save Grid Rows and Close Connections:

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import javax.sql.DataSource;
import org.joget.apps.app.service.AppUtil;
import org.joget.apps.form.model.Form;
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;
import org.joget.commons.util.UuidGenerator;

public void saveGridRows(Element element, FormRowSet rows, FormData formData) {
    
    String recordId = null;
    Connection con = null;
    
    try {
        // Retrieve current datasource configurations from Joget
        DataSource ds = (DataSource) AppUtil.getApplicationContext().getBean("setupDataSource");
 
        con = ds.getConnection();
         
        if(!con.isClosed()) {
            // Generate new record IDs for storing into child table
            UuidGenerator uuid = UuidGenerator.getInstance();
             
            // Iterate to add new records
            Iterator i = rows.iterator();
            while (i.hasNext()) {
                FormRow row = (FormRow) i.next();
                 
                String pId = uuid.getUuid();
                String gridColumn1 = row.get("gridColumn1");
                String gridColumn2 = row.get("gridColumn2");
                String gridColumn3 = row.get("gridColumn3");
                 
                String insertSql = "INSERT INTO your_table_name (id, gridColumn1, gridColumn2, gridColumn3) VALUES (?, ?, ?, ?);";
                 
                PreparedStatement stmtInsert = con.prepareStatement(insertSql);
                 
                stmtInsert.setString(1, pId);
                stmtInsert.setString(2, gridColumn1);
                stmtInsert.setString(3, gridColumn2);
                stmtInsert.setString(4, gridColumn3);
                 
                // Execute SQL statement
                stmtInsert.executeUpdate();
            }
        }
    } catch (Exception ex) {
        LogUtil.error("Your App/Plugin Name", ex, "Error storing using jdbc");
    } finally {
        try {
            if (con != null) {
                con.close();
            }
        } catch (Exception ex) {
            LogUtil.error("Your App/Plugin Name", ex, "Error closing the jdbc connection");
        }
    }
}

// Process and store grid rows
saveGridRows(element, rows, formData);

Grid design

The correct grid design should look like this:

Example of a Simple Load Binder BeanShell Script

import org.joget.apps.form.model.FormRow;
import org.joget.apps.form.model.FormRowSet;

FormRowSet f = new FormRowSet();
f.setMultiRow(true);

FormRow r1 = new FormRow();
r1.put("gridColumn1", "your_value");
r1.put("gridColumn2", "your_value");
r1.put("gridColumn3", "your_value");
f.add(r1);

FormRow r2 = new FormRow();
r2.put("gridColumn1", "your_value");
r2.put("gridColumn2", "your_value");
r2.put("gridColumn3", "your_value");
f.add(r2);

return f;

Related documentation

To further enhance your understanding and capabilities with Joget, here are some valuable resources that delve deeper into specific functionalities. Whether you want to refine your skills in managing form grids or explore the intricacies of using Bean Shell for data operations.

• Form Grid
• Bean Shell Form Data Store
• Grid