DEV Community: Busra Sengul

Deleting Umbraco Forms(13) Entries via SQL

Busra Sengul — Tue, 27 May 2025 13:24:58 +0000

When working with Umbraco Forms, it's common to accumulate a large number of form submissions over time — especially on high-traffic sites. While Umbraco provides a UI to manage entries, bulk deletion (especially based on date) isn't available out of the box.

What About Scheduled Deletion?

Umbraco Forms does include a scheduled record deletion feature, which allows you to configure automatic cleanup of form entries after a specified number of days. This is a great option for routine maintenance, especially when you want to apply a blanket policy across all forms.

However, there are scenarios where more granular control is needed — for example, deleting entries from specific forms, targeting a custom date range, or integrating cleanup into a broader data management workflow. In such cases, a custom SQL-based approach offers the flexibility and precision required.

In this post, we’ll walk through how to safely delete form entries for Umbraco Forms 13, before a specific date using SQL, while maintaining referential integrity across the related tables.

Understanding the Umbraco Forms Data Structure

Umbraco Forms stores form submissions across several tables:

UFRecords: Each form submission (entry) is stored here.
UFRecordFields: Links each field in a form to a specific submission.
UFRecordAudit: This table logs changes made to form entries via the Umbraco backoffice. It helps track who edited what and when — useful for auditing and compliance.
UFRecordDataXXX: Stores the actual field values, split by data type:

UFRecordDataString
UFRecordDataLongString
UFRecordDataInteger
UFRecordDataBit
UFRecordDataDateTime

Each UFRecordDataXXX table uses a Key column (a GUID) that maps to the Id of a record in UFRecordFields.

The Challenge

To delete a form entry, you must:

Remove all related field values from the UFRecordDataXXX tables.
Remove the corresponding rows from UFRecordFields.
Finally, delete the entry from UFRecords.
Failing to follow this order can lead to orphaned data or foreign key constraint errors.

The Solution: SQL Script for Safe Deletion

Here’s the SQL script we used to delete all entries created before a specific date (e.g., 2024-01-01):

-- Set the cutoff date
DECLARE @CutoffDate DATETIME = '2024-01-01';

-- Step 1: Delete from UFRecordDataXXX tables using JOINs
DELETE ds
FROM UFRecordDataString ds
JOIN UFRecordFields f ON ds.[Key] = f.[Key]
JOIN UFRecords r ON f.Record = r.Id
WHERE r.Created < @CutoffDate;

DELETE ds
FROM UFRecordDataLongString ds
JOIN UFRecordFields f ON ds.[Key] = f.[Key]
JOIN UFRecords r ON f.Record = r.Id
WHERE r.Created < @CutoffDate;

DELETE ds
FROM UFRecordDataInteger ds
JOIN UFRecordFields f ON ds.[Key] = f.[Key]
JOIN UFRecords r ON f.Record = r.Id
WHERE r.Created < @CutoffDate;

DELETE ds
FROM UFRecordDataBit ds
JOIN UFRecordFields f ON ds.[Key] = f.[Key]
JOIN UFRecords r ON f.Record = r.Id
WHERE r.Created < @CutoffDate;

DELETE ds
FROM UFRecordDataDateTime ds
JOIN UFRecordFields f ON ds.[Key] = f.[Key]
JOIN UFRecords r ON f.Record = r.Id
WHERE r.Created < @CutoffDate;


-- Step 2: Delete from UFRecordAudit
DELETE a
FROM UFRecordAudit a
JOIN UFRecords r ON a.Record = r.Id
WHERE r.Created < @CutoffDate;

-- Step 3: Delete from UFRecordFields
DELETE f
FROM UFRecordFields f
JOIN UFRecords r ON f.Record = r.Id
WHERE r.Created < @CutoffDate;

-- Step 4: Delete from UFRecords
DELETE FROM UFRecords
WHERE Created < @CutoffDate;

Targeting a Specific Form

If you only want to delete entries for a specific form, you’ll need to filter by the form’s unique identifier (GUID), which is stored in the Form column of the UFRecords table.

You can find the form’s GUID in the Umbraco backoffice or by querying:

SELECT DISTINCT [Key], [Name] FROM UFForms;

Once you have the form’s GUID, you can modify the SQL script like this:

-- Example: Only delete entries for a specific form
DECLARE @CutoffDate DATETIME = '2024-01-01';
DECLARE @FormId UNIQUEIDENTIFIER = 'YOUR-FORM-GUID-HERE';

-- Step 1: Delete from UFRecordDataXXX tables
DELETE ds
FROM UFRecordDataString ds
JOIN UFRecordFields f ON ds.[Key] = f.[Key]
JOIN UFRecords r ON f.Record = r.Id
WHERE r.Created < @CutoffDate And (@FormId IS NULL or r.Form = @FormId);

-- Repeat for other UFRecordDataXXX tables...
DELETE ds
FROM UFRecordDataString ds
JOIN UFRecordFields f ON ds.[Key] = f.[Key]
JOIN UFRecords r ON f.Record = r.Id
WHERE r.Created < @CutoffDate And (@FormId IS NULL or r.Form = @FormId);

DELETE ds
FROM UFRecordDataLongString ds
JOIN UFRecordFields f ON ds.[Key] = f.[Key]
JOIN UFRecords r ON f.Record = r.Id
WHERE r.Created < @CutoffDate And (@FormId IS NULL or r.Form = @FormId);

DELETE ds
FROM UFRecordDataInteger ds
JOIN UFRecordFields f ON ds.[Key] = f.[Key]
JOIN UFRecords r ON f.Record = r.Id
WHERE r.Created < @CutoffDate And (@FormId IS NULL or r.Form = @FormId);

DELETE ds
FROM UFRecordDataBit ds
JOIN UFRecordFields f ON ds.[Key] = f.[Key]
JOIN UFRecords r ON f.Record = r.Id
WHERE r.Created < @CutoffDate And (@FormId IS NULL or r.Form = @FormId);

DELETE ds
FROM UFRecordDataDateTime ds
JOIN UFRecordFields f ON ds.[Key] = f.[Key]
JOIN UFRecords r ON f.Record = r.Id
WHERE r.Created < @CutoffDate And (@FormId IS NULL or r.Form = @FormId);


-- Step 2: Delete from UFRecordAudit
DELETE a
FROM UFRecordAudit a
JOIN UFRecords r ON a.Record = r.Id
WHERE r.Created < @CutoffDate And (@FormId IS NULL or r.Form = @FormId);

-- Step 3: Delete from UFRecordFields
DELETE f
FROM UFRecordFields f
JOIN UFRecords r ON f.Record = r.Id
WHERE r.Created < @CutoffDate And (@FormId IS NULL or r.Form = @FormId);

-- Step 4: Delete from UFRecords
DELETE FROM UFRecords
WHERE r.Created < @CutoffDate And (@FormId IS NULL or r.Form = @FormId);

This approach gives you precise control over which form’s data is deleted — ideal for scenarios where different forms have different retention policies.

Safety Tips

Always back up your database before running deletion scripts.
Test the script in a local/dev environment first.
Consider wrapping this logic in a stored procedure or SQL Agent job for scheduled cleanups.

Bonus: Make It Reusable

You can easily convert this into a stored procedure:

CREATE PROCEDURE DeleteOldUmbracoFormEntries
    @CutoffDate DATETIME,
    @FormId UNIQUEIDENTIFIER = NULL
AS
BEGIN
    -- [Insert the full script here]
END

Conclusion

Cleaning up old form entries in Umbraco Forms can help improve performance and reduce storage overhead. With a clear understanding of the data structure and a safe deletion strategy, you can confidently manage your form data at scale.

Hope this helps to someone :)

Managing Stale Carts in Umbraco Commerce

Busra Sengul — Wed, 30 Oct 2024 11:12:39 +0000

If you have an e-commerce site using Umbraco Commerce that handles a lot of traffic, you might notice an accumulation of carts that never convert into orders. This was the case for my site, which has been running for about two years and had thousands of stale carts!

Adding to the complexity, I migrated this site from Vendr to Umbraco Commerce. If you’re considering a similar migration, you can refer to the official documentation.

Post-migration, a background job was set up to export all orders to a CRM. However, the stale carts were causing issues unrelated to the migration but due to other development factors. Our definition of product changed quite a bit, causing lots of errors being thrown due to 'cart' no longer being able to identify 'product' anymore. It would have also meant customers would have 'old' products in their cart that they could no longer order, therefore, clearing them all out was the best approach.

Some people suggested deleting them manually through the CMS, selecting 30 at a time, which would have been very time-consuming.

After extensive research, I found this issue and decided to swap Vendr tables with Umbraco Commerce tables. Here’s the SQL query I used to delete the stale carts efficiently:

BEGIN TRANSACTION;

-- Select all carts before date
SELECT * INTO #tempOrders FROM umbracoCommerceOrder 
WHERE createDate < 'yyyy/mm/dd' AND (finalizedDate IS NULL)

-- Delete data referenced to orders above in related tables
DELETE FROM umbracoCommerceFrozenPrice 
WHERE umbracoCommerceFrozenPrice.OrderId IN (SELECT Id FROM #tempOrders);

DELETE FROM umbracoCommerceOrderPriceAdjustment 
WHERE umbracoCommerceOrderPriceAdjustment.OrderId IN (SELECT Id FROM #tempOrders);

DELETE FROM umbracoCommerceOrderAmountAdjustment 
WHERE umbracoCommerceOrderAmountAdjustment.OrderId IN (SELECT Id FROM #tempOrders);

DELETE FROM umbracoCommerceOrderAppliedDiscountCode 
WHERE umbracoCommerceOrderAppliedDiscountCode.OrderId IN (SELECT Id FROM #tempOrders);

DELETE FROM umbracoCommerceOrderAppliedGiftCard 
WHERE umbracoCommerceOrderAppliedGiftCard.OrderId IN (SELECT Id FROM #tempOrders);

DELETE FROM umbracoCommerceOrderLineAttribute 
WHERE umbracoCommerceOrderLineAttribute.OrderLineId IN (SELECT Id FROM umbracoCommerceOrderLine WHERE OrderId IN (SELECT Id FROM #tempOrders));

DELETE FROM umbracoCommerceOrderLineProperty 
WHERE umbracoCommerceOrderLineProperty.OrderLineId IN (SELECT Id FROM umbracoCommerceOrderLine WHERE OrderId IN (SELECT Id FROM #tempOrders));

DELETE FROM umbracoCommerceOrderProperty 
WHERE umbracoCommerceOrderProperty.OrderId IN (SELECT Id FROM #tempOrders);

DELETE FROM umbracoCommerceOrderLine 
WHERE umbracoCommerceOrderLine.OrderId IN (SELECT Id FROM #tempOrders) AND umbracoCommerceOrderLine.parentOrderLineId IS NOT NULL;

DELETE FROM umbracoCommerceOrderLine 
WHERE umbracoCommerceOrderLine.OrderId IN (SELECT Id FROM #tempOrders);

DELETE FROM umbracoCommerceOrder 
WHERE umbracoCommerceOrder.Id IN (SELECT Id FROM #tempOrders);

-- Drop the temporary table
DROP TABLE #tempOrders;

-- Use ROLLBACK for testing
-- ROLLBACK;

-- Use COMMIT for deleting
COMMIT;

This approach allowed me to clean up the stale carts quickly and efficiently.

I hope this helps!
B

Umbraco Forms 12+, Custom Field Type

Busra Sengul — Thu, 17 Oct 2024 16:46:22 +0000

This is an addition to my previous post about Umbraco Forms.

This time let's create a custom field!

Sometimes we need fields that are not visually rendered in the FE, but the FE still needs to pass those values on a form submission. I'm sure you can instantly think of a few use cases for this.

In my case, I needed to have a text string field for the address, and then FE was responsible for having a postcode look up and sending back 5 different fields to the BE and then to a custom workflow.

Let's dive right into how with steps;

1-Install Umbraco Forms for your Umbraco 12+ project
dotnet add package Umbraco.Forms

You'll need to buy your license for your domain when you go live!

2-Create a custom field type;

public class AddressLookupField : FieldType
{
    public AddressLookupField()
    {
        Id = new Guid("2e26a579-2859-44fa-8778-fcdab81675d2");

        Alias = "addressLookup";

        Name = "Address Lookup";

        Description = "Address Lookup";

        Icon = "icon-terminal";

        DataType = FieldDataType.String;

        FieldTypeViewName = "FieldType.AddressLookupField.cshtml";

        SortOrder = 10;

        SupportsRegex = true;
    }

    public override IEnumerable<string> ValidateField(Form form, Field field, IEnumerable<object> postedValues, HttpContext context, IPlaceholderParsingService placeholderParsingService, IFieldTypeStorage fieldTypeStorage)
    {
        var returnStrings = new List<string>();

        return base.ValidateField(form, field, postedValues, context, placeholderParsingService, fieldTypeStorage, returnStrings);
    }
}

This class is for backoffice to understand there's a new field type to be added to the forms field types.
Here you can see it's now available as an answer type

3-We also need a .html file to display this field in the back office;
Add the following code to the path;
App_Plugins/UmbracoForms/backoffice/Common/FieldTypes/addresslookupfield.html

<input type="text" tabindex="-1"
       class="input-block-level"
       style="max-width: 300px" />

Now we can see the field too

4-Create a .cshtml file to
Views/Partials/Forms/themes/customProjectThemeFolder/FieldTypes/FieldType.AddressLookupField.cshtml

Here's the .cshtml will look like;

@model Umbraco.Forms.Web.Models.FieldViewModel
@using Umbraco.Forms.Web

<input type="text" name="addressLookup" id="addressLookup" class="text" value="@Model.ValueAsHtmlString" maxlength="500" />

<input type="hidden" name="addressLine1" id="addressLine1" class="text" value="@Model.ValueAsHtmlString" maxlength="500" />

<input type="hidden" name="addressLine2" id="addressLine2" class="text" value="@Model.ValueAsHtmlString" maxlength="500" />

<input type="hidden" name="city" id="city" class="text" value="@Model.ValueAsHtmlString" maxlength="500" />

<input type="hidden" name="postcode" id="postcode" class="text" value="@Model.ValueAsHtmlString" maxlength="500" />

<input type="hidden" name="country" id="country" class="text" value="@Model.ValueAsHtmlString" maxlength="500" />

As you can see, we only display the actual addressLookup field and there are 5 more additional fields that are hidden.

This is how you create a custom field!

Let's use this on a workflow;

When a form is submitted and you get your record fields by;
records.GetRecordFieldByAlias("addressLookup")?.Values; you get an array of the fields you had hidden!

Here's what my payload looks like

And Umbraco forms are smart enough to pick it up in the submissions!

Hope this helps someone!
B

Microsoft Clarity in Azure AD B2C

Busra Sengul — Fri, 01 Mar 2024 15:33:54 +0000

Have you heard about Microsoft Clarity before?

Microsoft's official definition is

Clarity is a free user behavior analytics tool that helps you understand how users are interacting with your website through session replays and heatmaps.

What about Azure AD B2C?

Azure AD B2C is a customer identity access management (CIAM) solution capable of supporting millions of users and billions of authentications per day. It takes care of the scaling and safety of the authentication platform, monitoring, and automatically handling threats like denial-of-service, password spray, or brute force attacks.

I've been growing quite fond of Clarity recently! It's an amazing tool and will always be free! Super simple to get started as well;

Add your site to https://clarity.microsoft.com/
Get the tracking script of your choice
Put the script into the head element of your view page.

So far so good.

I also wanted to add this script to a user flow we had in place including Azure B2C as the login process.

Here are some of the contexts you need to be aware of before we start how to achieved this.

The project uses Sign In user flow, as well as one of the default page layouts that Azure B2C offers, with nothing special within either. Configuration is detailed here, and pick a template after creating your user flow. Check the documentation on how to do it.

While we want to keep the template, we also want to inject the Clarity script into these pages that have been generated for us.

Here are the steps to achieve this;

1- Enable JavaScript enforcing
Go to the properties of your user flow, enable the JavaScript enforcing

2- Download the default page layout template you want to keep using
Here is the GitHub repo for the default page layout templates

3- Edit the relevant HTML page by adding your script to the head element of your template, if you do not need to, you do not have to touch the rest of the page!

4- Now host these wanted HTML files somewhere. Can be in blob storage, can be within your code, or wherever you want them to be.
For the sake of this article, I'm going to share the approach Microsoft Docs suggested, hosting with an Azure Blob Storage
If you're choosing to host the HTML files in the blob storage do not forget to configure CORS!

5- Finally update your user flow page layouts with the blob storage hosted HTML file URLs!

There it is! It might take a little while for tracking to kick in, but you now can see your B2C steps in the Clarity!

Happy hacking!

Umbraco Forms 12, Custom Workflow with using RestClient

Busra Sengul — Mon, 04 Dec 2023 11:52:03 +0000

I recently needed a custom workflow for an Umbraco Cloud project. My Custom Workflow needed to send some form details using a third-party API. I achieved this using the rest client on submission.
Here are the 3 steps to make this possible on an Umbraco 12+ project!

1- Install Umbraco Forms for your Umbraco 12+ project
dotnet add package Umbraco.Forms

You'll need to buy your license for your domain when you go live!

2- Add a new class for your custom workflow and inherit WorkflowType

namespace MySite.FormExtensions
{
    public class CustomWorkflow : WorkflowType
    {
        //We need to read our API keys from secrets/appconfig.json
        private readonly IConfiguration _configuration;

        public CustomWorkflow (IConfiguration configuration)
        {
            _configuration = configuration;
            Id = new Guid("D6A2C01-CF50-11DE-B075-55B055D75689");
            Name = "Custom workflow name";
            Description = "Custom workflow description";
            Icon = "icon-terminal";
            Group = "Services";
        }

        public override WorkflowExecutionStatus Execute(WorkflowExecutionContext context)
        {
            //Get your form information from here
            foreach (RecordField rf in context.Record.RecordFields.Values)
            {
                // and we can then do something with the collection of values on each field
                List<object> vals = rf.Values;

                // or get it as a string
                rf.ValuesAsString(false);
            }

            //or get them one by one
            var fieldName = context.Record.GetRecordFieldByAlias("fieldAlias")?.Values;

            var options = new RestClientOptions(_configuration["Rest:Api:BaseUrl"]);

            options.Authenticator = new HttpBasicAuthenticator(_configuration["Rest:Api:Username"], _configuration["Rest:Api:Password"]);

            var client = new RestClient(options);

            var request = new RestRequest("", Method.Post);

            request.AddHeader("accept", "application/json");

            request.AddHeader("content-type", "application/json");

            request.AddHeader("authorization", "Basic " + options.Authenticator);

            request.AddJsonBody(vals.ToJson(), false);

            var response = client.PostAsync(request);

            context.Record.State = FormState.Approved;

            return WorkflowExecutionStatus.Completed;
        }

        public override List<Exception> ValidateSettings()
        {
            return new List<Exception>();
        }
    }
}

3- Register your custom workflow class

public class CustomWorkflowComposer : IComposer
{
    public void Compose(IUmbracoBuilder builder)
    {
        builder.AddFormsWorkflow<CustomWorkflow>();
    }
}

Add your custom workflow to your form's workflow now, either on the submission step or on approval.

The only thing to be aware of here is how your third party wants your data. Mine asked for JSON format hence the line
request.AddJsonBody(vals.ToJson(), false);

That's it :)
Hope this will be useful for you!
B