Remove entries from Umbraco Forms

This blog post will explain how this works in Umbraco Forms 8.4.4. Database tables will most likely change between versions so keep this in mind - one might have to adjust the SQL-scripts depending on the Umbraco Forms-version.

Some basics

Before diving into details we need to be on the same page when it comes to the different building blocks in Umbraco Forms.

Forms

A Form is an actual Form, with all its fields, configured in the backoffice. Umbraco Forms stores the actual Forms as json-files on disk (\App_Data\UmbracoForms\Data\forms).

Field

A Form is built up by adding Fields to the form, giving them a name, alias and choosing a Form Field Type.

Entry

An entry is what gets stored when a visitor/user posts a Form. One can browse these Entries by expanding a form and clicking on "Entries". Each entry is stored together with all the values for the different Fields in the Form.

Database tables

Here is a brief overview of the data tables used by Umbraco Forms.

Entries and Fields

UFRecords

A UFRecord in the database is the same as one Entry in the UI. Each posted form will generate one UFRecord. This table contains information like when the Entry was created, on which page and from what IP.

UFRecordFields

This table "connects" each Field in the Form with the Entry. If the form has 5 fields, there should be 5 rows in this table for each Entry/Record. 

Database tables

All these tables are used to store data based on the data-type, they have a id and key-column and a column for the value (base on the data type).

UFRecordDataString

UFRecordDataLongString

UFRecordDataInteger

UFRecordDataBit

UFRecordDataDateTime

Security

UFUserSecurity

Stores security settings related to specific Backoffice Users

UFUserFormSecurity

Stores security settings related to specific Forms

Delete data with SQL-script

To be able to delete an Entry we need to remove all related data in the UFRecordDataXXX-tables, the UFRecordFields and the UFRecord it self.

Here is a SQL-statement to remove Entries from Umbraco Forms, set the @formKey to only remove entries for specific Form, or use an empty string as @formKey to remove all Entries.

-- Use this to delete entries for a single form
DECLARE @formKey nvarchar(MAX) = 'f8274271-239c-4564-9f5e-0629bb02ca01';

-- Use this to delete entries for all forms
--DECLARE @formKey nvarchar(MAX) = '';

DECLARE @formRecordIds TABLE
(
   recordId int
);

DECLARE @fieldKeys TABLE
(
   fieldKey uniqueidentifier
);


IF @formKey = '' 
	BEGIN
		INSERT INTO @formRecordIds SELECT [id] FROM [UFRecords] 
	END
ELSE 
	BEGIN
		INSERT INTO @formRecordIds SELECT [id] FROM [UFRecords] WHERE [Form] = @formKey
	END

-- Populate temp-table with a record-ids for the given form. Should match the number if the Umbraco Forms-dashboard

SELECT * FROM @formRecordIds

-- Populate the fieldKeys with all Field keys for any of the Records for the Form
INSERT INTO @fieldKeys SELECT [key] FROM [UFRecordFields] WHERE [Record] IN (SELECT recordId FROM @formRecordIds)
SELECT * FROM @fieldKeys



-- Delete all UFRecordDataXXX rows for any of the Fields in any of the Records
DELETE FROM UFRecordDataBit WHERE [key] IN (SELECT fieldKey FROM @fieldKeys)
DELETE FROM UFRecordDataDateTime WHERE [key] IN (SELECT fieldKey FROM @fieldKeys)
DELETE FROM UFRecordDataInteger WHERE [key] IN (SELECT fieldKey FROM @fieldKeys)
DELETE FROM UFRecordDataLongString WHERE [key] IN (SELECT fieldKey FROM @fieldKeys)
DELETE FROM UFRecordDataString WHERE [key] IN (SELECT fieldKey FROM @fieldKeys)

-- Delete the Fields
DELETE FROM UFRecordFields WHERE [key] IN (SELECT fieldKey FROM @fieldKeys)

-- Delete the Records
DELETE FROM UFRecords WHERE id IN (SELECT recordId FROM @formRecordIds)