The content you're reading is getting on in years
This post is on the older side and its content may be out of date.
Be sure to visit our blogs homepage for our latest news, updates and information.
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?
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. ]
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:
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:
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:
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:
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!
View all posts from The Progress Team on the Progress blog. Connect with us about all things application development and deployment, data integration and digital business.
Let our experts teach you how to use Sitefinity's best-in-class features to deliver compelling digital experiences.
Learn MoreSubscribe to get all the news, info and tutorials you need to build better business apps and sites
Progress collects the Personal Information set out in our Privacy Policy and the Supplemental Privacy notice for residents of California and other US States and uses it for the purposes stated in that policy.
You can also ask us not to share your Personal Information to third parties here: Do Not Sell or Share My Info
We see that you have already chosen to receive marketing materials from us. If you wish to change this at any time you may do so by clicking here.
Thank you for your continued interest in Progress. Based on either your previous activity on our websites or our ongoing relationship, we will keep you updated on our products, solutions, services, company news and events. If you decide that you want to be removed from our mailing lists at any time, you can change your contact preferences by clicking here.