Pre-populate Form Fields with Data from External Source

Introduction

Pre-populating form fields with data from external sources can enhance user experience by automatically filling out information based on existing data. In Joget, you can achieve this by using the Bean Shell Form Data Store. This method allows you to initialize a form with specific fields pre-populated from external databases or other sources. In this article, you will guide you through the process of configuring a Bean Shell Form Data Store to populate form fields with external data.

How does it work?

To pre-populate form fields with data from an external source, follow these steps:

1. Go to the section in your form where you want to pre-populate fields. Edit the section properties, and in Load Data Store, select Bean Shell Form Data Store as the Load Data Store.
   
   
   

2. Use Bean Shell scripting to write the code required to retrieve data from your external source. The provided example code demonstrates how to load data from a database and populate form fields.

   import org.joget.apps.app.service.*;
   import org.joget.apps.form.model.*;
   import org.joget.apps.form.service.*;
   import java.sql.*;
   import java.util.*;
    
   public FormRowSet getData() {
    
       //-----------------------------------------------------------------------------------
       //In this part of code, it trying to load the original data from form data table.
    
       FormRowSet results = null;
       if (primaryKey != null && primaryKey.trim().length() > 0) {
           AppService appService = (AppService) FormUtil.getApplicationContext().getBean("appService");
           Form form = FormUtil.findRootForm(element);
           if (form.equals(element) && form.getParent() != null) {
               form = FormUtil.findRootForm(form.getParent());
           }
           if (form != null) {
               results = appService.loadFormDataWithoutTransaction(form, primaryKey);
           }
       }
       //------------------------------------------------------------------------------------
    
       //------------------------------------------------------------------------------------
       //In this second part of code, it will load the data from external source by using
       //JDBC. It will run only when the first part of code fail to retrieve data from
       //form data table. This example use dir_user table of Joget as external source.
    
       if (results == null) {
           results = new FormRowSet();
    
           Connection con = null;
           try {
               Class.forName("com.mysql.jdbc.Driver").newInstance();
               con = DriverManager.getConnection("jdbc:mysql://localhost:3306/jwdb2?characterEncoding=UTF-8", "root", "root");
    
               if(!con.isClosed()){
                   String pId = "#currentUser.username#";
                   String sql = "SELECT firstName, lastName, email FROM dir_user WHERE username=?";
                   PreparedStatement stmt = con.prepareStatement(sql);
                   stmt.setString(1, pId);
    
                   ResultSet rs = stmt.executeQuery();
                   while (rs.next()) {
                       FormRow row = new FormRow();
                       row.put("firstName", (rs.getString(1) != null)?rs.getString(1):"");
                       row.put("lastName", (rs.getString(2) != null)?rs.getString(2):"");
                       row.put("email", (rs.getString(3) != null)?rs.getString(3):"");
                       results.add(row);
                   }
               }
    
           } catch(Exception ex) {
               System.err.println("Exception: " + ex.getMessage());
           } finally {
               try {
                   if(con != null)
                       con.close();
               } catch(SQLException e) {}
           }
       }
       //------------------------------------------------------------------------------------
    
       return results;
   }
    
   return getData();

   
   

Expected outcome

 If the coding is properly written and tested, you should get this result:

Related documentation

• Bean Shell Form Data Store