What happens to Form Builder data when forms are modified?

In my last blog post, I introduced some of the under-the-hood features that are powering Sitefinity 4.0’s new Form Builder.  Because Sitefinity is powered by Telerik’s OpenAccess ORM, we are able to generate (at runtime!) dedicated tables for these new custom forms. 

The benefits of this behavior were explained in my last blog post.  However, this behavior also raises some concerns.  Specifically, what happens to these tables when Sitefinity forms get modified?  Even more importantly, what happens to the data these tables contain?

Step 1 - Create a Form

To demonstrate this scenario, I’ll create the following quick form:

Then add a handful of form elements (name, job title, company, email address, message).

After clicking Publish, Sitefinity creates a new table dedicated to storing responses to this form.

[ Hint: I’m viewing this table in Visual Studio using the Server Explorer.  SQL Server Management Studio could also be used to view database table definitions and data. ]

Step 2 – Create some sample responses

The form is now created and can be dropped onto any Sitefinity page. 

Any responses to this form can then be viewed through Sitefinity’s UI:

But these responses can also be seen in the custom table:

Step 3 – Modify the form

Everything is working as expected.  However, what happens to this data if the form is modified? 

We can test this by removing the Company & Job Title fields and adding a Website field.

Publish and look at the resulting table modifications:

Because the default column names aren’t terribly descriptive (hint: you can control these column names) it’s hard to see what happened.  Sitefinity added 1 new column (form_text_box__c006), but kept the 2 columns for the removed text fields. 

Submitting a new response for this modified form results in the following data:

None of the old data has been lost.  However, new form submissions will result in a NULL value for these orphaned columns.  Furthermore, the old data is not visible within Sitefinity’s UI:

Step 4 – Rescuing orphaned data

As demonstrated, data is protected during form modifications.  However, by removing the form elements you are disconnecting Sitefinity from the data in these columns.  It will be impossible (as of this writing) to view this orphaned data inside Sitefinity.

To re-expose this lost data inside Sitefinity you need to re-add the form fields that were removed from the form.  This means re-adding form fields of the same type (text box, drop-down, multiple-choice, etc.) and the same ID.

In the example above, I can use the table column names to see the old ID’s of these missing form elements:

• form_text_box__c003 = Job Title
• form_text_box__c004 = Company

Armed with this information I can add 2 new text fields to my form:

However, I also need to explicitly set the ID’s for these Textboxes:

Now, Publish the form.  The orphaned data has now been rescued:

Some additional questions…

What happens if I change the underlying form element type?

In the examples above I added, removed and then re-added Textboxes.  What if I modified the Textbox to be a Paragraph?  This is possible and Sitefinity will modify the column type from nvarchar(255) to text.  In this scenario, no data will be lost.  However, if the field was then modified back to Textbox the column would get modified back to nvarchar(255).   Because this data type is limited to 255 characters this could result in data being truncated.

How do I permanently & completely remove a form and all its data?

Sitefinity tries very hard to protect your data.  However, this becomes a problem when you want to completely remove a form and start over.  Sitefinity will continue to account for old form fields in the database.  I tried to manually remove the database table, but even then Sitefinity would recreate old form fields.  As of this writing, I don’t have a clear answer to this question.   Creating a unique name for the form, results in a fresh start.

How do I view disconnected data without adding the form elements back to form?

I don’t have a good answer to this.  Sitefinity will not display the data if the form element is missing from the form.  Consequently, the form field MUST be available on the form to view results associated with the form field.  However, as demonstrated above, you can always use a database manager (SQL Server Management Studio, etc.) to view the raw data in the form.  It’s not very user friendly, but it will work in a pinch.

--

As you can tell, I’m exploring all of this along with all of you.  Feedback, suggestions, etc welcomed!