Calculated Fields Form Blog

Tips and cases of use for a successful WordPress website with calculated forms.

Blog / Using the information stored in a database or csv file in the form

Using the information stored in a database or csv file in the form

The use of DS fields for reading the information stored in a database or CSV file, and its use in the equations.


The Developer and Platinum versions of the plugin are distributed with a special set of fields, they can be identified by the "DS" text in their names (Line Text DS, Email DS, Text Area DS, Checkbox DS, Radio Btns DS, Phone DS, Dropdown DS, Hidden DS, Number DS, Currency DS, Date Time DS, and Recordset DS) whose values are read from an external data-source, like a database or CSV file.

Settings of DS fields

DS Settings

The DS fields include a delimited area in their settings, enclosed into a dashed frame for selecting the data-source to use and configure them. The available data-sources are:

  • A database
  • A CSV file
  • A recordset field (it is another DS field that would be described below)
  • A particular case of the database data-source for reading the information of the post types
  • Another particular case of the database data-source for reading the information of the users
  • Similar to the two previous cases, a data-source for reading the information of the taxonomies
  • Finally, the forms submissions data-source to read other forms' submissions

Note that the data-sources for taxonomies, posts, and users data are covered by the database data-source.

Populating the fields values from a database

To describe the integration with the databases I'll use a DropDown DS field, because this control includes all possible attributes. In my case, I'll use this field for reading the titles and their corresponding ids, of the latest 5 published pages on the website.

Database Integration

To use the database as data-source, must be selected the database option through the "Define Datasource" list.

The settings of database includes two sections: the database connection settings, and the query definition section.

The database connection section

The database connection section allows to enter the hostname, the database's engine, the database's name, and the username and password for connecting to the database. Furthermore, for databases that require a more specific connection's structure, it is possible to select the "DNS" option through the "Database connection" attribute, and enter the "DNS" definition.

The database connection section includes a button for checking if the connection information is correct. Pressing this button, the plugin tests the database connection.

Pay attention: for using the same website's database, as in my example, leave these attributes in blank.

The query definition section

The plugin allows to define the query by its components, or enter the query directly.

In my example, as I want to use a DropDown DS field for reading the titles and ids of the latest 5 published pages on the website, so, I'll describe this section entering the values required to achieve my goal.

The "Column for values" attribute allows to enter the name of the column to read the values of the choices in the DropDown field, in my case the "ID" column of the wp_posts table.

The "Column for texts" attribute allows to enter the name of the column for reading the texts of the choices in the DropDown field (in my case the "post_title" column of the wp_posts table)

The "Table name" attribute is used for entering the name of the database's table, in my example it should be wp_posts, but pay attention to the screenshot above, I'm using the constant: {wpdb.posts}, why?

WordPress allows to define the prefix used for naming the tables in the database ("wp" is the prefix by default). So, the table wpposts, can be renamed as abposts, xyposts or any other preferred prefix, and using the constants for the names of the tables, it is not needed to know the prefix of the database.

Constants:

{wpdb.prefix} the prefix used by WordPress in the name of database's tables, for example, the text "wp_" into the table's name "wp_posts"

{wpdb.comments} the name of Comments table

{wpdb.commentmeta} the name of Comment Metadata table

{wpdb.links} the name of Links table

{wpdb.options} the name of Options table

{wpdb.postmeta} the name of Post Metadata table

{wpdb.posts} the name of Posts table

{wpdb.terms} the name of Terms table

{wpdb.term_relationships} the name of Term Relationships table

{wpdb.term_taxonomy} the name of Term Taxonomy table

{wpdb.termmeta} the name of Term Meta table

{wpdb.usermeta} name of User Metadata table

{wpdb.users} the name of Users table

{wpdb.blogs} the name of Multisite Blogs table

The Condition attribute allows filtering the rows of database. As I want to load the list of published pages, I'm filtering by: post_type="page" AND post_status="publish"

The Order by attribute allows ordering the rows. In my example, the latest published pages, so, I'm ordering the rows by the post_date column in descending order: post_date DESC

The Limit attribute limits the number rows returned by the query. I want to populate the field with only 5 elements.

Implementing the same query, but selecting the "Custom Query" option, and entering the query directly:

SELECT ID as value, post_title as text FROM {wbdb.posts} WHERE post_type="page" AND post_status="publish" ORDER BY post_date DESC LIMIT 5

Inserting the same posts list, but this time selecting the "Post Type" as datasource

CSV Integration

As you can see in the previous screenshot the solution is simpler. I just selected the "Post Type" option from the "Define Datasource" attribute, the "Page" option through the "Post Type" list, the "ID" option for the "Attribute for values" list, the "post_title" option for the "Attribute for texts" list, and entered the number 5 into the "Display the last" attribute.

Populating the fields values from a CSV File

I'll describe the integration with a CSV file with a practical example: the insertion of a list of countries in the form. For my example, I'll use the CSV file located in:

https://raw.githubusercontent.com/icyrockcom/country-capitals/master/data/country-list.csv

where the first row is a headline with the columns: country, capital, type

CSV Integration

The first step would be select the CSV option through the "Define Datasource" list.

I'm using an online CSV file, so, I've ticked the "from URL" choice, and entered the URL to the CSV file into the input box.

As the first row in the CSV file is a headline, I've ticked the "Use headline" checkbox, and pressed the "Import CSV" button. This action process the headline row and populate the lists "Select column for texts" and "Select column for values", for selecting the columns to populate the values of the texts and values of the choices in the DropDown field. In my example, I'm selecting the "country" column in both lists.

Using a RecordSet DS field as datasource and its advantages

I'll describe the advantages of using a RecordSet DS field for reading the information from the data-source, and use it as the data-source of others DS fields.

Reading the information from an external data-source always takes some time, because the information is stored in the server side or a different domain, and if there are multiple DS fields in the form the performance can be affected because require to open multiple connections. Using a RecordSet DS field improves the form performance drastically, because only one field will read the external information, and then, the other fields simply use the information stored in this field locally.

There is another important advantage that I'll try to describe with an example. Assuming there is a CSV file with four columns. For summing the four columns of the record, would be required to insert a DS field per column, and sum these fields in the equation associated to the calculated field, but with a RecordSet field, would be needed only identify the record, and sum the columns directly in the equation (and won't be required additional fields). I'll return to this solution below.

To describe the use of the recordset fields, I'll return to the case of use with the CSV file for countries and capitals.

I've inserted first the RecordSet DS field in the form (in my example it is the fieldname18), for reading the CSV file, and configured it as follows:

Recordset

  • I've selected the CSV option from the datasource list
  • Ticked the "from URL" choice
  • I've entered the URL to the CSV file: https://raw.githubusercontent.com/icyrockcom/country-capitals/master/data/country-list.csv
  • And after pressed the "Import CSV" button
  • I've selected all the columns loaded into the "Select columns" list

Once the recordset field is inserted and configured, I've inserted a DropDown DS (the fieldname16) for reading the countries list from the RecordSet DS field, and I've configured this field as follows:

DropDown - Recordset

  • I've selected the "Recordset" option from the data-source list
  • and selected the recordset field to use as data-source from the "Recordset" list (in my example the fieldname18)
  • Finally, I've entered the column's name: country into the attributes "Property for values" and "Property for texts"

To complete the project I've inserted a "Line Text DS" field, to display the capital of the country selected in the DropDown DS field (fieldname16):

Line Text DS - Recordset

  • I've selected the "Recordset" option from the datasource list
  • and selected the recordset field from the "Recordset" list (in my example the fieldname18)
  • I've entered the name of the column to populate the field (In this case: capital)
  • And pay attention to the condition, this attribute is very important, because I want to display the "capital" column in the record corresponding to the country selected through the fieldname16 field: record['country'] == '<%fieldname16%>'

Note as I'm referring to the value of other field in the form with its tag: <%fieldname16%>, and as its value is a text, the tag should be enclosed between single or double quotes.

Using a RecordSet DS field in the equations

Assuming there is a recordset field in the form (fieldname1) where every record includes four columns: id, columna, columnb, and columnc, and I want calculate the sum of columns: columna, columnb, and columnc, whose id match with the value entered by the user through the fieldname2 field.

The equation associated to the calculated field would be:

(function(){
        var records = fieldnam1;
        for(var i in records)
        {
        if(records[i]['id'] == fieldname2)
            return records[i]['column_a']+records[i]['column_b']+records[i]['column_c'];
        }
        return '';
})()
Reading the information of logged user

There are many situations where you want to pre-fill the fields in the form with the information of the logged user to avoid input errors, and make your form more users-friendly. The User Data data-source come at rescue in this cases. I'll describe its use inserting a "Email DS" field to load the email of the logged user:

User Data

  • I've selected the "User Data" option for the "Define Datasource" list
  • I've selected the "user_email" option through the "Attribute for values" list
  • And very important, I've ticked the checkbox: "Display data of logged user"

As I said at beginning of the article, "User Data" data-source is a particular case of the "Database" data-source

So, describing the same case, but with the Database as the data-source:

User Data - Database datasource

As you can see in the screenshot, the settings are very similar to the field used in the section that describes the use of "Database" as data-source, so, I'll centering only in the differences, that are very important.

The field's settings does not includes the attribute: "Column for texts" because it is an "Email DS" field, that has no options with values and texts.

The second point to consider is the table name: in this case I'm using the constant {wpdb.users} referring to the wp_users table in the WordPress database (or the equivalent with the prefix of your database)

But the most significant difference in the field's settings is the use of a new constant {user.id} in the "Condition" attribute. This new constant belongs to the set of constants with the information of the logged user:

Logged user constants:

{user.id} the id of the current user

{user.login} the username of the current user

{user.nicename} the URL-friendly name for the current user

{user.email} the email address of the current user

{user.url} the URL associated to the current user

{user.display_name} the user's name that is shown on the site for the current user

{user.first_name} the first name of current user

{user.last_name} the last name of current user

Loading the list of categories (or any other taxonomy like the tags)

Taxonomy Datasource

After selecting the "Taxonomy" option from the Define Datasource list, the plugin loads the list of available taxonomies in the "Taxonomy" attribute, and in my example, I've selected the "Categories" option, and then, I've selected the attribute to use as the values of the choices in the DropDown DS field (term_id), and the attribute to use as the texts of the choices (name)

Loading forms submissions

This data source is only available with the RecordSet DS fields.

Forms Submissions Datasource

The data source allows loading forms submissions, filtering them by the forms' ids separated by commas and/or submissions ids separated by commas. Furthermore, it is possible to restrict the submissions belonging to the registered users. The data source includes the attribute for entering the names of the fields to read separated by commas (if no field name is entered, the plugin reads all submitted fields).

A practical example. Assuming the form 123 was created for a carpet cleaning service to collect rooms details. The fieldname1 field in the form is a number field to collect the room's area.

To create a new form that calculates the property area of the customer, insert a RecordSet DS field in the new form for reading the user's submissions (configured as the screenshot above), fieldname3. Finally, Insert a calculated field with the following equation:

(function(){
    var records = fieldnam3, area = 0;
    for(var i in records) area += records[i][1];
    return area;
})()

Note that the piece of code records[i][1] returns to the fieldname1 in the i record.

DataTable DS

The DataTable DS fields allow visualizing the RecordSet DS records. The DataTable DS control uses the DataTables JS library.

DataTable DS Settings

DataTable DS Settings