Dynamic Data

Purpose

The Dynamic Data functionality allows for dynamic pre-filling and dependency-based data retrieval in forms. It is primarily used to populate dropdown fields, prefill single values, or create dynamic dependencies between form fields. DynamicData is a powerful tool that enables querying and processing data from various tables (even across plugins) and mapping it back into form fields in real time.

Key Features

Dynamic Dropdowns

Populate dropdown fields dynamically based on data from the database or external sources.

Dependency Support

Enable fields to load their values based on the selected value of another field (dependent fields).

Advanced Querying

Use custom SQL queries to fetch data from any database table, including plugin-specific tables like WooCommerce products.

Prefilling Single Values

Automatically calculate and prefill fields (e.g., next available sort order) based on dynamic data queries.

Caching for Performance

Supports caching of dropdown options to reduce query execution time for fields that do not depend on other fields.

Parameters

Form Field

The field where DynamicData functionality is applied.

Dynamic Data Query

The SQL query used to fetch dynamic data.

Dependent Field Value:

The value of another field that the current field depends on.

Query Placeholders

Placeholders in DynamicData queries are dynamic tokens that are replaced with actual values when the query is executed. These placeholders allow the query to be context-sensitive and adaptable to the current form state, field dependencies, or user input.

##dependent_field_value##: This placeholder is replaced with the value of a dependent field in the form. See Examples below.

##current_field_value##: This placeholder is replaced with the current value of the field where the query is applied. See Examples below.

Visual Example

Dynamic Data Query

In this example, we see the usage of DynamicData within a form. The form contains a dropdown field called Project, where the content —namely, the list of projects— is dynamically loaded using DynamicData. The form field Project is configured with a DynamicData query that retrieves and provides the data for the dropdown.

For this functionality to work, the query must be formatted to return at least two columns: value and text (the display text shown in the dropdown).

SELECT 
  wp_wr_project.id as `value`,
  concat( title, ' (', id, ')') as `text`
FROM 
  wp_wr_project
 

Resulting in a DropDown:

Dynamic Data Query with Dependent Field

In the following case, we want to create a dropdown box based on the chosen project. This ensures that you can only select entities contained within the selected project. To achieve this, the dynamic data query is configured with a dependent field. When the dependent field (in this case, project) is selected, DynamicData will query the relevant data and prefill the dropdown.

In this scenario, the placeholder ##dependent_field_value## will be replaced by the value of the project field.

SELECT 
  wp_wr_entity.id as `value`,
  concat(title, ' (', id, ')') as `text`
FROM 
  wp_wr_entity
WHERE project = ##dependent_field_value##
ORDER BY `text` ASC

Other Use Cases

Prefilling Single Value

Calculate the next available field for a new entry dynamically:

SELECT 
    IFNULL(MAX(field) + 1, 0) AS value  
FROM 
    table_name
WHERE 
    form_id = ##dependent_field_value##;

Prefilling Current Value

You can reference the field’s current value in queries using the placeholder ##current_field_value##. This is particularly useful for displaying all available options while ensuring that the currently selected option is retained in the list.

SELECT 
	* 
FROM 
    table_name 
WHERE 
    id NOT IN (SELECT related_id FROM another_table) 
    OR id = '##current_field_value##';

Custom Selected Text

If you want to set a pre-selected value, you can use the dynamic data query where the field selected specifies the value to be pre-selected.

select 1 as value, '0%' as text, 2 as selected 
	union all
select 2 as value, '20%' as text, 2 as selected
	union all
select 3 as value, '5%' as text, 2 as selected

This example creates a dropdown values with 0%, 20%, and 5% as text , where the option 20% with key 2 is pre-selected.

Filters

DynamicData seamlessly integrates with WordPress filters, enabling advanced customization. For example, see the filter: wr_form_dynamic_data_load_options_by_filter

Select Static Data

Sometimes you need to create very simple dropdown options without relying on external tables or complex structures. In these cases, you can directly define the dropdown options using a simple query with UNION. This allows you to provide static options without the need to create or maintain additional database tables. Alternatively, you can achieve the same result by using a WordPress filter, as mentioned above, or by defining the options directly in the query.

In the following example, we define a dropdown with three static options: Option 0, Option 1, and Option 2. The query directly specifies the value (what is stored) and text (what is displayed) for each option.

select 0 value, 'Option 0' text 
union 
select 1, 'Option 1' 
union 
select 2, 'Option 2'

A common use case is representing binary choices, such as Yes or No, in a dropdown. The following query defines these options:

SELECT 
  0 as value, 'No' as text 
  union all 
SELECT
	1 as value, 'Yes' as text 

Benefits

Dynamic and Flexible Forms

Simplify complex forms by dynamically loading data based on user input.

Real-Time Data Access

Query data from any table, enabling seamless integration with external systems and plugins.

Efficient User Experience

Reduce clutter by displaying only relevant options based on dependencies.

Reusable Configuration

Dynamic queries and caching make forms adaptable and efficient across multiple projects.