CFDB Plugin

See: http://moometric.com/integrations/sf/wordpress-form-submissions-salesforce-custom-object/

This post relates to the Community Yard Sale Plugin

The Problem

Google has changed Google Maps so that all websites with domains registered after June 2016 require an API key to work.

If you are using the Community Yard Sale plugin, and you see an error such as the following, then you need an API Key.

The Solution

You must obtain a Browser API Key from Google. This requires that you have a Google account.

Go to the Google Maps API page, and click the GET A KEY button.

Ensure Create a project is selected and click Continue.

Pick any Name you like and set the URL of your website as shown and click Create.

Copy the key that is created.

Go to your WordPress dashboard, Plugins -> Yard Sale -> Options tab, enter the API key and click Save Changes.

Occasionally people find that they cannot save form submissions with upload files greater than a certain size, often 1MB.

CFDB does not limit the size of file uploads, but other things do.

Check The Form Definition

First, check your form definition in whatever front-end form plugin you are using. There may be a setting to set the file size limit (such as with Contact Form 7).

Check The WordPress Memory Setting

Your server should much more memory than the max size of your upload. Check your WordPress memory settings.

Check MySQL max_allowed_packet

The file binary gets stored in the database. Often it is the MySQL DB that is rejecting the insert of a large Binary Large Object (BLOB). This is fixed by changing the max_allowed_packet setting on the MySQL server. You may need to contact your hosting provider to do this for you.

Users which WordFence firewall plugin activated can experience an error in which they see a “403 Forbidden” error when they try to save a code entry in Add Shortcodes Actions and Filters (ASAF).

The solution is to whitelist ASAF in the WordFence plugin.

From the WordPress Dashboard, go to WordFence -> Firewall.

In The Whitelisted URLs section

• URL: /wp-admin/admin-ajax.php
• Param: POST Body
• Param Name: code
• Click Add

Update: as of version 2.10.13, HYPERLINK functions should work in downloaded Excel .xlxs files automatically.

If you have a form that includes uploaded files, then .xlsx and .ods export files will have HYPERLINK functions for each file entry. This is intended to enable you to download the files.

But the problem is that the export code doesn’t support creating functions. So the literal text of the function appears in the cell, but Excel, OpenOffice & LibreOffice don’t know that it is a formula.

The fix for this is:

1. Open the export file
2. Select the cells with formula text
3. Do a “Find/Replace All” operation: find “=” and replace with “=”

That doesn’t change the formula but tricks Excel, OpenOffice & LibreOffice into re-evaluating the cells at which point it converts them to an actual formula.

In Excel selecting a single cell and pressing F2-Enter will convert the cell to a formula. But this only works on one cell, not a range of selected cells.

(Since version 2.5.1)

RSS URLs do required you to login to access them by default. You can create RSS URLs to add to any RSS feed reader to show some of all of the contents of a form. But to make it work with a feed reader that doesn’t allow you to login, you have to make such URLs publicly accessible.

How to Generate an RSS URL

Go to the WordPress administration page,

• Contact Form DB -> Short Code
• Choose a form
• Choose “Export File” = “RSS“
• Choose a form fields for the “Item Title” (title for each item in RSS list)

This will display a URL that you can use as an RSS feed. But by default you must log into WordPress for the URL to work. Some RSS readers will not be able to use this URL because they cannot login. The URL is secure

How to Turn off Security

This creates a security hole whereby a savvy person could see (but not change) all the saved form data in your database. If someone knows or can guess one of your form names, they can easily work out the RSS URL needed to show the form’s data.

If you want to make RSS URLs like this publicly accessible, then go to the WordPress administration page,

• Contact Form DB -> Options,
• set “AllowRSS URLs” = “true“

This option makes all RSS URLs that you could possibly generate publicly accessible. All form data can be viewed, it is only a matter of creating the right URL.

Forms that have many thousands of entries can be slow to return query results or may cause you PHP page to run out of memory. A quick addition to your short code can make the query run by the short code faster and require less memory. This addition is unbuffered=”true”.

Example: change [cfdb-table form="form1"] with
[cfdb-table form="form1" unbuffered="true"]

However: this cannot be used if you are nesting a short code inside another short code that is also doing a query. It doesn’t work if two queries are running at the same time. An example might be nesting a cfdb short code inside a [cfdb-html] short code.

Technical: This option makes use of the PHP mysql_unbuffered_query function instead of mysql_query.

For Contact Form 7 Users: a recent update of the Contact Form 7 plugin seems to have added several new meta fields to form submissions. These automatically get saved by the CFDB plugin. If you look on the Database page in your administration panel,  you will see extra fields associated with new submissions:

_wpcf7,_wpcf7_version,_wpcf7_unit_tag,_wpnonce,_wpcf7_is_ajax_call

You may view these fields as unwanted clutter.

To avoid saving these fields, go to the Database Options page,  and paste the list of fields in the following:

Update

Since regular expressions can now be used in this field, you can achieve the same effect but putting in this:

/.*wpcf7.*/,_wpnonce

This is an example of a user-defined short code that I helped someone create and I thought it would be a good one to share.

My new friend Gillian created a web site to track Polio survivors currently living in Australia (http://www.polioaustralia.org.au). Visitors to the site can register and provide information about themselves. Gillian is capturing these submissions into her database using this plugin. She then wanted to add some graphs showing statistics on the data.

Specifically, she wanted to compute some percentages. Example: of those people who contracted Polio in Australia, how many were contracted in New South Wales?

The first thought would be to use the cfdb-value short code with function=”percent”. But cfdb-value does not give her what she wants. The issue: The calculation needs to exclude those people who contracted Polio in a different country (Those people made form submission because they are now living in Australia).

So we turned to creating our own short code. In this short code we want to first select only the subset of form submissions that indicate a case of Polio contracted in Australia, then determine the percentage of those that were from a particular state (e.g. New South Wales).

But we can do better; we created a general “percentage-of” short code where we can select those entries that constitute the total for the denominator, then count those that match the sub-criterion for the numerator (state=New South Wales in this case).

The approach to getting the total (denominator) is to use plugin’s “filter” capability to select the relevant entries (In this case, Polio cases contracted in Australia). Then in the code of our user-created short code, we loop through those results and count those that meet our sub-criterion (state=New South Wales), compute and output the percentage.

The short code placed on the page looks like this:

[percent-of form="Polio Register" filter="Overseas=N" field="PolioStateTerr" matching="New South Wales"]

Here, we used the built-in  form and filter short code attributes to select the entries of interest for the denominator (those from the register that are not overseas i.e. Polio cases contracted in Australia). Then we add two new short code attributes that our user-created short code PHP will need to handle. These are field and matching for the numerator.

The PHP for the short code is the following. You may wish to use this code since it can be applied generally.

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15

require_once(ABSPATH .
'wp-content/plugins/contact-form-7-to-database-extension/CFDBFormIterator.php');
$exp = new CFDBFormIterator();
$exp->export($atts['form'], $atts);
$total = 0;
$count = 0;
while ($row = $exp->nextRow()) {
    $total = $total + 1;
    if ($row[$atts['field']] == $atts['matching']) {
        $count = $count + 1;
    }
}
$percentNum = 100.0 * $count / $total;
$percentDisplay = round($percentNum) . '%';
echo $percentDisplay;

The key element is in lines 9-10 which resolves to checking if a form entry’s PolioStateTerr field’s value is equal to the value “New South Wales”. If so, we increment $count. At the end we compute the percentage using $count/$total.

As of version 2.2, you can filter by relative time to capture things like “since last week”

[cfdb-table form="Contact form" filter="submit_time>last week"]

Read more