SQL Provider (SaveToDatabase Action) for Web Forms For Marketers (WFFM 2.5 – Sitecore 7.5)

24 Replies

With the release for WFFM 2.5 for Sitecore 7.5; SQL server is no longer supported (see release notes) as each time a form is submitted it automatically saves the form data in the xDB (MongoDB) and associates it with the current contact (used to be called visitor).

I believe MongoDB is a much better choice than SQL for storing non-structured data that is generated by WFFM and in addition it can also be used to tailor the experience to the current contact (visitor) i.e. don’t show them the form if they have already filled it out, etc.

Problem

Unfortunately I have a number of customers that want to upgrade to Sitecore 7.5, but due to a number of business and operational reasons are not ready to migrate MongoDB. Therefore they wish to continue storing their form data in the existing SQL database.

Solution

I decided to create a simple SQL provider for WFFM 2.5, that would allow customers to continue saving/retrieving data frm the existing SQL database.

The shared source module WEB FORMS FOR MARKETERS 2.5 – SQL PROVIDER (SAVETODATABASE) is available at Sitecore’s marketplace and the source code is available at GitHub.

This initial version is very simple and provides the following functionality:

  • Save data to a WFFM SQL database (SaveToDatabase Action).
  • Retrieve all submitted data for a given form.
  • Download the data as a CSV file (see separate blog post)
  • Specify a date range to be exported (see article)
  • Define the CSV Delimiter (see article)

It was my intention to port the forms report viewer from a previous version, but the architecture has changed considerably to accommodate xDB and it would have required me to re-code and override a lot of classes. In addition there were a number of UI control libraries that the forms report viewer relies upon which are no longer used/bundled with Sitecore 7.5.

Therefore I intended to implement the ability to down load the data for a given form as an CSV file. So the data can be analysed and sorted using Excel, which in my experience is how the majority of customers used WFFM data.

Installation

Install the WFFM.SQLServer.SaveToDatabase-1.4.zip package, note you prompted that the Save to database item already exists, select overwrite and aply, see below

overwrite

Ensure the WFM.ConnectionString settingin /app_config/include/Sitecore.Forms.config is set to the name of the connection string for the WFFM SQL database i.e.

<setting name="WFM.ConnectionString" value="wfm" />

where the “wfm” connection is defined as follows:

<add name="wfm" connectionString="user id=[USER ID];password=[password];Data Source=[Server];Database=[WFM DATABASE];Connect Timeout=30" />

How To Save Data

Like previous version of WFFM you use the “Save To Database” action to save the submitted form data to the SQL database. The SaveToDatabase action is still /sitecore/system/Modules/Web Forms for Marketers/Settings/Actions/Save Actions/Save to Database. In fact it is the same item from previous versions, so all existing forms that use the SaveToDatabase action will continue to work without making any changes.

save to database item

The only change is that class is WFFM.SQLServer.SaveToDatabase.Infrastructure.Actions.SaveToDatabase, from the WFFM.SQLServer.SaveToDatabase assembly.

Retrieve data for a given Form

To Insert new form data and retrieve data there is a FormReposiotry class which has a Get function to retrieve a list of form data for a given form id, seee below

public IEnumerable<IForm> Get(ID formId)

I have added a button to download data as CSV see this article.

And it is now possible to specify a date range to export and the delimiter used to separate the columns in the CSV file (see article)

Links

marketplace.sitecore.net/Modules/Web_Forms_for_Marketers_SQL_SaveToDatabase.aspx

github.com/TakeitEasyAlan/WFFM-SQL-Server-SaveToDatabase

24 thoughts on “SQL Provider (SaveToDatabase Action) for Web Forms For Marketers (WFFM 2.5 – Sitecore 7.5)”

  1. Pingback: Sitecore 7.5 SQL Provider (WFFM 2.5) – Added button to download data as CSV | Alan Coates – Sitecore/.NET blog

  2. Ian Graham

    Hi Alan,
    Great solution! I’m currently battling with the default setup of WFFM 2.5 and may have to go down this route!

    With the default setup, do you know if there’s a way to view submitted form data in the Form Reports as you would in previous versions of WFFM? I can’t find this option in 2.5 – has this been removed?

    Cheers

    Ian

    Reply ↓
    1. alancoates Post author

      Hi,

      I am not sure but i believe if you use MongoDB (but i have not tried this yet), you can use the forms viewer, as there are some extra tables added to the reports database by WFFM.

      Reply ↓

  4. Pingback: Sitecore 7.5 SQL Provider (WFFM 2.5) – Download for specific data range | Alan Coates – Sitecore/.NET blog

  5. Stephanie

    Hi,
    is this module supposed to show the existing data from the SQL server DB when user view Reporting? After I installed this module, the reporting does not show the existing data from the older version of Webforms. The export to CSV works fine and does have the existing WF data.

    Thanks,
    Stephanie

    Reply ↓
    1. alancoates Post author

      Hi,

      No it is not the intention that the reporting will show the SQL data, it was only meant as a temporary way to export the existing SQL data and continue to use the SQL database if you do not use MongoDB (I have a number of customers where operations for a number of reasons will not setup MongoDB)
      Hope this helps, if not let me know 🙂

      Reply ↓
    1. alancoates Post author

      Hi,

      Unfortunately the package can not be un-installed, but you can extract the zip file and see what items are installed, and remove them 🙂

      Reply ↓
      1. Adam Seabridge

        Thanks Alan, Could you confirm we can still use the DMS conversion plugin (link above) to export any data captured using the SQL Provider to Mongo?

      2. alancoates Post author

        The data is stored in exactly the same format, so I would assume so. But I have not tried it, and I doubt I will have time this week. 😦

  7. Milan Thakkar (@milanjt)

    Hi Alan.

    We are planning to use WFFM 2.5. We are not using Sitecore Analytics (xDB) – it is disabled in our instance. Can we still use WFFM 2.5 with your module? We are on Sitecore 7.5 and I read in the documentation of WFFM 2.5 that Enabled experience analytics is a prerequisite for WFFM 2.5.

    Since you mentioned lot of your customer are not ready for MongoDB, I’m guessing they have analytics disabled and are using the WFFM 2.5 with your module. Just want to confirm if people are using WFFM 2.5 in prod with experience analytics disabled.

    Thanks.

    Reply ↓
  10. alancoates Post author

    hi,

    Not yet as the majority of our customers that have moved to 8, have also moved to xDB, and therefore use MongoDB to store their fomrs data.

    Reply ↓
  12. Rahul

    Hi Alan

    Though the Export To CSV functionality works great, however the current library does not support customization as all the classes involved are marked either internal or private and cannot be inherited. In our case our CMS server is shared for production and preview environment and we need to switch our connection strings while exporting data. For that purpose we required a bit of tweaking.

    Thanks
    Rahul

    Reply ↓
  13. Preeti G

    Hi Alan,

    We are upgrading from Sitecore 7.2 to Sitecore 8.2 and currently in process of upgrading to WFFM 8.2. We are not using Sitecore Analytics (xDB) – it is disabled in our instance. We were using WFFM 2.3 with Sitecore 7.2 instance and everything was working as expected with SQL Server database.

    We would like to use WFFM 8.2 with Sitecore 8.2. Can we use this module in this implementation?

    Thanks.

    Reply ↓

Leave a reply to Stephanie Cancel reply

This site uses Akismet to reduce spam. Learn how your comment data is processed.