Create Form From Existing Custom Table

Introduction

You learn how to create a new form by performing a lookup into an existing table in the Joget database. If the table has a large number of fields (e.g., 50 fields), manually dragging and dropping each form element into a new form can be time-consuming.

Here is the sample table structure used in the demo app:

mysql> describe custom_table \G
*************************** 1. row ***************************
  Field: id
   Type: varchar(255)
   Null: NO
    Key: PRI
Default: NULL
  Extra:
*************************** 2. row ***************************
  Field: name
   Type: varchar(255)
   Null: YES
    Key:
Default: NULL
  Extra:
*************************** 3. row ***************************
  Field: address
   Type: varchar(255)
   Null: YES
    Key:
Default: NULL
  Extra:
3 rows in set (0.00 sec)
 
mysql>

How does it work?

To generate a form from an existing custom table, follow these steps:

1. Enter the Custom Table Name into the text field.
2. Click Generate to generate the form definition based on the table structure.
   
3. Copy the content of the generated form definition.
4. Create a new form or open an existing form.
   
5. Go to Form Builder > Advanced Tools > JSON Definition.
6. Paste the copied JSON definition and click Update.
   
   

7. Verify the form design and click Save.

Customization

You can customize the generation script to fit the form definition. Go to the form builder for a demo within this app and go to the Data store to view or modify the script.

Here is the script used to generate the form definition:

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.FormRow;
import org.joget.apps.form.model.FormRowSet;
import org.joget.commons.util.LogUtil;

String fields = "";

// Retrieve connection from the default datasource
DataSource ds = (DataSource) AppUtil.getApplicationContext().getBean("setupDataSource");
Connection con = null;

try {
    con = ds.getConnection();
    // Execute SQL query
    if (!con.isClosed()) {
        PreparedStatement stmt = con.prepareStatement("describe #requestParam.primaryKey?sql#");
        ResultSet rs = stmt.executeQuery();
        while (rs.next()) {
            String field = "                        {\n" +
                "                            \"className\": \"org.joget.apps.form.lib.TextField\",\n" +
                "                            \"properties\": {\n" +
                "                                \"encryption\": \"false\",\n" +
                "                                \"readonly\": \"False\",\n" +
                "                                \"style\": \"\",\n" +
                "                                \"label\": \"" + rs.getString("Field") + "\",\n" +
                "                                \"readonlyLabel\": \"False\",\n" +
                "                                \"storeNumeric\": \"False\",\n" +
                "                                \"id\": \"" + rs.getString("Field") + "\"\n" +
                "                            }\n" +
                "                        }\n,";
            fields += field;
        }
    }
} catch (Exception ex) {
    LogUtil.error("T319", ex, "Error query");
} finally {
    // Always close the connection after use
    try {
        if (con != null) {
            con.close();
        }
    } catch (SQLException e) {
        // ignored
    }
}

if (!fields.isEmpty())
    fields = fields.substring(0, fields.length() - 1);

String formDef = "{\n" +
    "    \"className\": \"org.joget.apps.form.model.Form\",\n" +
    "    \"properties\": {\n" +
    "        \"loadBinder\": {\n" +
    "            \"className\": \"org.joget.apps.form.lib.WorkflowFormBinder\"\n" +
    "        },\n" +
    "        \"name\": \"Demo\",\n" +
    "        \"description\": \"\",\n" +
    "        \"id\": \"demo\",\n" +
    "        \"storeBinder\": {\n" +
    "            \"className\": \"org.joget.apps.form.lib.WorkflowFormBinder\"\n" +
    "        },\n" +
    "        \"tableName\": \"demo\"\n" +
    "    },\n" +
    "    \"elements\": [\n" +
    "        {\n" +
    "            \"elements\": [\n" +
    "                {\n" +
    "                    \"elements\": [\n" +
                            fields +
    "                    ],\n" +
    "                    \"className\": \"org.joget.apps.form.model.Column\",\n" +
    "                    \"properties\": {\n" +
    "                        \"width\": \"100%\"\n" +
    "                    }\n" +
    "                }\n" +
    "            ],\n" +
    "            \"className\": \"org.joget.apps.form.model.Section\",\n" +
    "            \"properties\": {\n" +
    "                \"label\": \"Section\",\n" +
    "                \"id\": \"section1\"\n" +
    "            }\n" +
    "        }\n" +
    "    ]\n" +
    "}";

FormRowSet rows = new FormRowSet();
FormRow row = new FormRow();
row.setProperty("formDef", formDef);
rows.add(row);

return rows;

On line 19, you can modify the connection string if you need to perform the table lookup in a different database.

On line 27, you can change the form elements from TextField to other types based on your needs.

Download sample app