Category Archives: Sitecore 7.5

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

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.

CSV button

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

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

No images after upgrading to Sitecore 7.5

With the release of Sitecore 7.2 encode name replacements now applies to media URL’s, prior to 7.2 it only applied to content items, and therefore media URL’s with spaces used to be like the following:

/-media/some%20item%20with%20space/test-xx-yy.jpg

But with Sitecore 7.2, they can be as follows:

/-media/some-item-with-space/test-xx-yy.jpg

This change in fact caused all the images on the site I was upgrading to disappear, but first a quick introduction to Encoding name replacement.

It provides the ability to specify text replacements to use when the media manager generates the friendly URL for a media item. The element /sitecore/encodeNameReplacements in the web.config; contains a number of replace elements and each replace element defines what to find and replace.

<encodeNameReplacements>
      <replace mode="on" find="&amp;" replaceWith=",-a-," />
      <replace mode="on" find="?" replaceWith=",-q-," />
      <replace mode="on" find="/" replaceWith=",-s-," />
      <replace mode="on" find="*" replaceWith=",-w-," />
      <replace mode="on" find="." replaceWith=",-d-," />
      <replace mode="on" find=":" replaceWith=",-c-," />
</encodeNameReplacements>

Strangely enough replacing a space with a dash is not defined by default, but I have added it to almost every solution as %20 in URL’s kills SEO. Below is the element you need to add to achieve that spaces are replaced by dashes.

<replace mode="on" find=" " replaceWith="-" />

Nemesis and side effects

I have reported to Sitecore support a number of times that I thought that encode replacements should apply to media URL’s. So with 7.2 I got my wish and this is where nemesis strikes me as after upgrading to Sitecore 7.2 all the product images disappeared from the site.
The solution has over 50000 images which are synchronized from their PIM (Product Information Management) and name of media item had a space and a dash.
If you want to have spaces replaced with dashes the one restriction is that you can no longer have dashes in the items name. Because when Sitecore tries to resolve the URL it applies the reverse of any encode replacements i.e. sitecore replace all dashes with a space to find the item path.
For example assume that your media item has a space and a dash in its name.

/media library/cars/myCar 26-10-2013

The URL will be

/~media/cars/myCar-26-10-2013.jpg

But when Sitecore tries to resolve the URL to find the image it replaces all the dashes with spaces (i.e. the reverse of the encode name replacement) and then it can’t find the item at the following path.

/media library/cars/myCar 26 10 2013

There are a number of solutions

  • Write script to replace all dashes with a space in the media library
  • Create a custom media provider, which doesn’t execute the encode replacements.

Whist I try to avoid modifying standard sitecore behavior in this case I choose to override the GetMediaUrl of the MediaProvider, as it is not possible to change the image names due to the business requirements of the site.

Anyway I hope this helps anybody if there images disappear after upgrading to 7.5.