Visforms 5
SQL fields with text similarity

SQL fields with text similarity

Introduction

Note: These features are part of the Visforms Subscription and are not included in the free Visforms version.

Sometimes you want to display text-like fields in your forms, whose contents are dynamically retrieved from the database during the form’s runtime. If the contents of the database change, the corresponding field will automatically have the changed contents the next time it is displayed. This process can also refer to the current user input and the change in user input in other fields in the form.

The Visforms Subscription offers you 7 text-like field types, whose contents are dynamically generated from the database using SQL select statements.

The following text-like field types have SQL support:

  • Text
  • Email
  • Date
  • URL
  • Number
  • Phone
  • Hidden

You have 2 options using 2 different SQL statements to determine the field values from the database:

Hinweis:

System requirements

The Visforms Subscription must be installed.

Formal requirement for the two SQL statements

From Visforms’ point of view, these two possible SQL statements only have to fulfill one formal condition with regard to the result:

Note: The SQL statement must return exactly one single value.

It does not matter under what name the value is returned.
In comparison to the SQL statements for SQL fields with option lists, no as value must be included in the SQL statement.

The following SQL statements are therefore sufficient and equal SQL statements:

Select ${user:name};

Select ${user:name} as username;

Select ${user:name} as test;

Fields with a specific data type or data format

If fields expect a specific data type or data format, the following must be observed:

  • The SQL statement must ensure that the return value also has the correct type and format.

Example of field types that expect a specific data format:

  • Email: The SQL statement must return a text that has a valid email format.
  • Date: The SQL statement must return a text that corresponds to a date in the configured format.
    The date format is selected in the field configuration.
    For the selected date format dd.mm.YYYY, the correct SQL statement returns the text 31.04.2024
  • URL: The SQL statement must return a text in a URL format: https://….
  • Number: The SQL statement must return a text that only contains numbers without spaces.

An example of a field of type Date and the configured date format dd.mm.YYYY:

select DATE_FORMAT(registerDate, '%d.%m.%Y') as date from #__users where id in ${input:user[]};

Possible validations of the field configuration must be fulfilled

It must be ensured that the value that the SQL provides also fulfills all other validations stored in the field configuration.
This includes the following:

  • a minimum or maximum number of characters,
  • a highest or lowest value,
  • a minimum date or a maximum date,
  • a user-defined validation.

Basics

The same basics apply to SQL statements and placeholder replacement as already described in the previous section for the SQL fields with option lists.

The following topics are particularly meant:

SQL statement for the one-time default value

Configuration

If you want to insert a default value in the empty form, you can specify which type of starting value is used for the field.
In the field configuration, Basic settings tab, select the option Value from individual SQL statement for the Fill field with parameter.
Write your SQL statement in the Default value SQL statement parameter.

SQL statement for the one-time default value

Special features

Note: Do not use input parameters of the Joomla input object.

In this case, you cannot use input parameters of the Joomla input object. Replacing the input parameters is not possible with the default value, as input parameters can lead to unexpected results.

User parameters from the Joomla user object can be used. The determined value is displayed as the default value in the form field and can be changed by the user at any time.

SQL statement for the updated field value

Configuration

In the field configuration, Basic settings tab, set the SQL field option to Yes.
You will then be shown 3 further options:

  • Reload when changed.
  • Field value SQL statement.
  • Execute SQL in Form Edit.
SQL statement for updated field values

Special features

Note: The field value is always determined automatically and cannot be changed manually by the user.

In this case, you can use input parameters and user parameters of the corresponding Joomla objects.

Configuring the SQL statement with input parameters

Some attention is required to ensure that the SQL statement and its repeated execution work when selected user inputs are changed. You insert these selected fields with their relevant user inputs into the SQL statement in the form of input parameters.

Changes to the user inputs in these fields, the so-called trigger fields, must trigger a re-execution of the SQL statement. The list of reload trigger fields in the configuration of the SQL field is used for this purpose.

Note: It must be individually configured so that a re-execution of the SQL statement is triggered when the user inputs of selected fields, the so-called trigger fields, are changed.

To ensure that the SQL statement is executed again with the corresponding input parameter when a user changes input in these trigger fields, the following must be observed:

  • All trigger fields must be selected in the list of reload trigger fields of the SQL field.
  • All form fields that are used as input parameters in the SQL statement must be included in the list of reload trigger fields.
  • Fields that are not available in the list of reload trigger fields must not be used as input parameters.

Note: Not all fields that are possible in principle can actually be used as trigger fields. They are then not available as trigger fields in the list of reload triggers.

In the following situations, fields are not available as possible trigger fields:

  • The following field types can never be selected because the field type is not technically suitable:
    • File Upload
    • Image Submit-Button
    • Submit
    • Reset
    • Field Separator
    • New Page
    • Location
    • Signature.
  • The following activated field options may not be compatible with use in the SQL statement:
    • Option Edit-only field.
      For all field types that have an Edit-only option.
    • Options Display as data list in field type Select-SQL
    • Option HTML Editor in field type Textarea
  • If the field is used in a condition, and this would result in an endless logical loop.
    Field A triggers the reload of field B and field B triggers the reload of field A.

It is absolutely necessary to ensure that the input placeholders used in the SQL statement are also included as fields in the Reload trigger list.
You configure the Reload trigger list in the field configuration, Basic settings tab with the form parameter Reload when changed.

Note: Otherwise, using the field can lead to unexpected results.

Note: If the reload trigger list is incomplete, it may even happen that a form cannot be submitted at all.

If configured correctly, the field value of an SQL field is automatically adjusted immediately with each user input if the change takes place in a reload trigger field.

In principle, the user can overwrite the value, but the input would be changed again immediately by a later reload and is also not valid. If the form option SQL field is activated, Visforms validates on the server side after the form has been submitted that the value transmitted with the form matches the value resulting from the SQL statement. If the values do not match as a result of a manual change in the SQL field, the form is returned as incorrect and displayed again.

The field option “Execute SQL in Form Edit”

Option set to “No”

If you set this option to No, the field behaves like a normal non-SQL field when editing the form. The value stored in the database is used. This can be manually changed by the editor when editing the form. It is then no longer validated that the user input matches the value resulting from the SQL statement.

This setting can be useful if the SQL statement contains user parameters. The user who originally submits the form does not have to be the same user who later edits the data. Or if you have implemented a form of auto-increment that should not continue to increment every time you edit.

Note: Use the field permissions to control who can change the value and who cannot.

Option set to “Yes”

If you set this option to Yes, the field behaves like an SQL field in the form view.

In this case, it is best to set the field to Read-only. This makes it clear that this is data that the user should not change.

Note: Visforms' data validation ensures that the data is actually not changed.

What's on this Page