How-To Guide: Configuring the 'Field Selection of an SQL' Field Type in Data Forms

Overview

The 'Field Selection of an SQL' field type enables dynamic, data-driven field options. It is ideal for scenarios where the available options depend on data from your database or the user's context (e.g., session). It includes features like:

• Dynamic labels and values using SQL.
• User session-based personalization.
• Advanced selector plugins for improved usability.

Steps to Configure the Field

Step 1: Configure Field Details

1. Field Name: Choose a descriptive name (e.g., user_name).
2. Explanation of the Empty Field:
   • Add a user-friendly explanation (e.g., "This field dynamically fetches user-specific data. If no options appear, check user permissions or SQL query conditions.").
3. Field Label: Set a clear label for the field (e.g., "Name").
4. Alignment and Field Width:
   • Alignment: Choose left, center, or right alignment.
   • Field Width: Use 100% width for full-screen layouts.
5. Required Field: Check this option to make the field mandatory.

Step 2: SQL Query Configuration

The SQL query determines the data displayed in the field. Here’s an example:

SELECT
    CONCAT(t1.company_name, ' | ', t1.contact_name) AS label,
    t1.company_id AS value
FROM companies t1
LEFT JOIN employees t2 ON t2.id = t1.account_manager_id
WHERE t2.email = '#sess_username#';

Key Elements in the Query

• Dynamic Labels:
  • Use CONCAT or similar SQL functions to create user-friendly labels.
  • Example: Display "Company Name | Contact Name".
• Dynamic User Context:
  • Use placeholders like #sess_username# to personalize results based on the logged-in user.
• Search Capability:
  • Add #SEARCH# to enable in-field search functionality:
    SQL

    WHERE column_name LIKE '%#SEARCH#%'
    

• Limit Results:
  • Optimize performance for large datasets using LIMIT:
    SQL

    LIMIT 100
    

Step 3: Grouping and Advanced SQL Features

1. Grouping Options:

   • Use the optgrp field to group related options:
   SQL

   SELECT 'Technology' AS optgrp, id AS value, name AS label FROM departments
   GROUP BY optgrp;
   

   
   

   SQL Statement	Description
   optgrp	Represents the group under which related options are categorized. It acts as the header or organizer for grouping items together.
   value	Represents the actual data that will be stored in the database when the user makes a selection. This is the value sent to your table.
   label	Represents the user-friendly name or description of the option that will be displayed in the interface. This helps the user understand what they are selecting.

   
   

2. Global Variables:

   • Use dk_global_vars for additional filtering in SQL:
     SQL

     WHERE column_name = '#dk_var_[name]#'
     

Step 4: Choose a Selector Plugin

The Selector Plugin determines how the options are displayed. Options include:

To set the plugin:

1. In the Selector Plugin dropdown, select one of the following:

   • Selectize (For small data sets ~50 rows)
   
   
   • Modal Selector

   

   
   
   • Normal Select Tag (For Offline Forms)

   

   
   

Step 5: Save and Test

1. Once all configurations are complete:
   • Click EDIT FIELD or SAVE.
2. Test the form to ensure:
   • The SQL query executes correctly.
   • Data displays as expected.
   • UI behavior matches the selected plugin.

Troubleshooting

Practical Example

Example: Company Selection

SQL Query:

SELECT
    CONCAT(company_name, ' | ', manager_name) AS label,
    company_id AS value
FROM companies
WHERE manager_email = '#sess_username#';

Expected Behavior:

• The field will display a dropdown of company names managed by the current user.
• The dropdown options will show as "Company Name | Manager Name".

Selector Plugin:

• Use Selectize for small datasets or Modal Selector for larger datasets.

Key Best Practices

1. Test Queries Thoroughly:
   • Validate your SQL query to ensure correctness and performance.
2. Use Intuitive Labels:
   • Ensure field labels and dropdown options are user-friendly.
3. Optimize for Performance:
   • Use LIMIT, efficient joins, and indexing to speed up queries.
4. Adapt Plugins to Data Size:
   • Choose the plugin best suited for your dataset's size and complexity.

Summary

The 'Field Selection of an SQL' field type is a powerful feature for creating dynamic and personalized forms. By integrating SQL and selector plugins, you can enhance user experience and improve data interactivity.