Database Wizard Options Data Store

Introduction

Database Wizard for Loading Selections/Options allows users to integrate external databases inside the Form Select Box element without writing SQL code. Users can visually configure database connections, queries, and updates.

Add a Database wizard

To use the Database Wizard, follow these steps:

1. Drag and drop a Select Box element onto the canvas inside the Form Builder.
2. Click the Select Box to display its properties.
   
3. In the properties, select Database Wizard inside Load Data From.
   
4. Configure the Database Wizard accordingly.

Database Wizard Properties

Configuring the Database Wizard involves specifying the data source, setting up connection properties, selecting the table, and defining the columns for the form options.

Configure the following fields:

• Datasource: Specify the target database to execute SQL statements on.
  • Custom Datasource: Setup to connect to an external database. This requires additional configuration.
  • Default Datasource: Connect to the Joget database. The database your Joget is currently using will be selected.
• Custom JDBC Driver: The JDBC driver to load, such as com.mysql.jdbc.Driver
  This field is required when Custom Datasource is selected in the Datasource above.
• Custom JDBC URL: The JDBC URL, such as jdbc:mysql://localhost/jwdb?characterEncoding=UTF8&useSSL=false
  This field is required when Custom Datasource is selected in the Datasource above.
• Custom JDBC Username: The JDBC username.
  This field is required when Custom Datasource is selected in the Datasource above.
• Custom JDBC Password: The JDBC password. 
  This field is required when Custom Datasource is selected in the Datasource above.
• Table: Choose a table from the database as a source to retrieve data.
• Joins Form Data Table:
  

  Field	Description
  Join Type	Condition type to fulfil.
  Table	Target table to join with.
  Column	Field ID from source form to set as option label.
  Join Column	Field ID from source form to set as option grouping value.

• ID Column: Field ID from the source form to set as the option value.
• Label Column: Column to be chosen as the Label.
• Grouping Column: Column to be chosen as the Grouping.

Form options caching

Caching options allow you to manage how often the grade options are refreshed, which can significantly improve performance in environments with large datasets.

Configure the following fields:

• Sync Cache Interval: Duration in seconds for cache synchronization activation.
  For example, with a setting of 180 seconds, background synchronization on the server occurs every 180 seconds to update with the latest data.
• Pause Sync after Idle: Duration to wait before pausing synchronization after idle.

Advanced

Advanced options for dynamically loading options and including an empty option in the select box.

Configure the following fields:

• Use AJAX for cascade options?: When checked, this allows fields to dynamically load available options based on another field's value (grouping column) when dealing with many selections. For more information, see Dynamic-cascade-drop-downs.
• Add Empty Option: If checked, an empty option will be added to the selections.
• Empty Option Label: The label for the empty option. It is only used when the Add Empty Option is checked.

Filter

Configure filter conditions to refine selections based on table column values.

Configure the following fields:

• Filter Conditions:
  • Join Type: Logical operator for the filter conditions.
    
    • And
    • Or
  • Column: Table Column ID.
  • Operator: Comparison operator for the filter condition.
    
    • Equal
    • Not Equal
    • Greater Than
    • Greater Than Or Equal
    • Less Than
    • Less Than Or Equal
    • Like, Not Like
    • In
    • Not In
    • Is True
    • Is False
    • Is Null
    • Is Not Null
  • Value: Filter value.
• Extra Conditions: Additional filter conditions in HQL to refine the selections.

Video tutorial

Here is a video tutorial explaining the Database Wizard Options Data Store configuration process in detail. This tutorial provides a step-by-step guide to ensure you understand how to use this feature effectively.