Category Archives: Web Forms For Marketers

Sitecore 7.5 SQL Provider (WFFM 2.5) – Download for specific data range

Version 1.4 is now released and is available either as a sitecore package at sitecore marketplace or you can get the source from Github (for more details see the original post for the module)

Firstly I have to say thanks to Paul Martin (@sitecorepm) as he implemented these new features.

It is now possible to specify a date range for exporting the SQL entries (see image).

The following setting defines the delimiter to be used to separate the columns in the CSV file.

The following setting defines the delimiter to be used to separate the columns in the CSV file.

How to add a button to the sitecore content editor to download data as a CSV file

5 Replies

In this blog I will explain how to add a button to the content editor and download data as an CSV file. For this example I will assume that we want to download the data for a given web form for marketers form as a CSV file.

Adding the button

Contextual Ribbons

You may not have noticed but this is a contextual ribbon that is it is only shown when the item is based on the forms template, as it gives no meaning to have the download data as CSV added to all items.

First you need to swap to the core database, and navigate to the Contextual Ribbons item for the content editor i.e. /sitecore/content/Applications/Content Editor/Ribbons/Contextual Ribbons.

This is where all the ribbons that are only shown for specific item types are placed; within this folder there is a forms toolbar, which contains a form strip, which contains a forms chunk, which in turn contains the “Export Data as CSV” button item.

The most important part of the button item is the click field, which defines the command name that will be raised when the button is clicked (I will explain how to hook this up in more detail later).

But how is a contextual ribbon associated with a given template – well that part is easy on the template item under the appearance section there is a ribbon field which can define a contextual ribbon or toolbar to be shown.

How to hook the button click up to the code that is to executed?

We have to add a command item to the /app_config/commands.config file to bind the command name to the class that is going to provide the functionality.

<command name="forms:exportformdata" type="WFFM.SQLServer.SaveToDatabase.Infrastructure.Commands.ExportFormDataCommand, WFFM.SQLServer.SaveToDatabase" />

The class must inherit from the Sitecore Command class, and override the Execute function.

    internal class ExportFormDataCommand : Command
    {
        public override void Execute(CommandContext context)
        {
            Assert.ArgumentNotNull(context, "context");
            Assert.IsNotNull(context.Items, "context items are null");
            Assert.IsTrue(context.Items.Length > 0, "context items length is 0");

            Item contextItem = context.Items[0];
            Assert.IsNotNull(contextItem, "First context item is null");
            OpenNewWindow(contextItem.ID, contextItem.Name);
        }

        private void OpenNewWindow(ID id, string name)
        {
            Assert.ArgumentNotNull(id, "id");
            UrlString url = new UrlString(Constants.Url.ExportFromDataPage);
            url.Append(Constants.QueryString.Name.ItemId, HttpContext.Current.Server.UrlEncode(id.ToString()));
            url.Append(Constants.QueryString.Name.ItemName, HttpContext.Current.Server.UrlEncode(name));
            SheerResponse.Eval(string.Format("window.open('{0}');", url));
        }
    }

Its not very elegant, but the code generates a URL which points to a aspx page that will stream the data. The Eval method of the sheer response, allows me to execute JavaScript on the client which in this case opens a tab/window.

The aspx page doesn’t do that much it just sets the ContentType to “text/csv” and streams the data. If you want to see the complete implementation, you can download it from github

Hope this was helpful, Alan

Sitecore 7.5 SQL Provider (WFFM 2.5) – Added a button to download data as CSV

1 Reply

As promised I have added the ability to download the form data as an Excel spreadsheet (CSV) .

I have created an include file /app_config/WFFM.SQLServer.SaveToDatabase.config to bind the command to the class that exports the CSV.

<command name="forms:exportformdata" type="WFFM.SQLServer.SaveToDatabase.Infrastructure.Commands.ExportFormDataCommand, WFFM.SQLServer.SaveToDatabase" />

Open the content editor navigate to any form, select the “Forms” tab, and click on the “Export Data as CSV” (see below).

For more information about the module please refer to original blog post, or get the sitecore package from sitecore marketplace or the source code from GitHub for the source.

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

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.

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

Alan Coates – Sitecore/.NET blog

Pushing Sitecore to the limits and beyond