Tips and cases of use for a successful WordPress website with calculated forms.
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.
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:
Note that the data-sources for taxonomies, posts, and users data are covered by the database data-source.
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.
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.
{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
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.
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
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.
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:
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:
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):
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.
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 '';
})()
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:
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:
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:
{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
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)
This data source is only available with the RecordSet DS fields.
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.
The DataTable DS fields allow visualizing the RecordSet DS records. The DataTable DS control uses the DataTables JS library.