Sitecore Form Submission Report

Published Jul 15, 2023

Sitecore Forms has an “Export Data” option in the Forms Builder that provides a basic form submission report. The “Export Data” option is in the dropdown menu that is available in the toolbar area at the top of the center pane. The dropdown menu is opened by clicking the button between the “Select All” button and the “Toggle Tools Panel” button. A dialog is then shown with radio button options for “Complete list” or “Date range”, and if “Date range” is selected, then textboxes can be used to enter “Date From” and “Date To”. When the “Ok” button is clicked, a “csv” file is generated, with a row for each submission of the selected form, with each row showing all of the field values for that submission. A user needs to have the “Forms Data Administrator” role to be able to use the “Export Data” option.

A more advanced form submission report is shown in the screen cap below. Its advantages over the “Export Data” option are that it allows for submissions from multiple forms to be included in the same report, it allows for the report data to be shown in an interactive grid that can be sorted and searched, and it allows the report data to be copied to the clipboard, saved as a CSV, or saved as a PDF.

The dual select box at the top of the above screen cap is based on code in the github repo at https://github.com/Geodan/DualListBox. The left list box is populated with all of the forms that appear in the “Forms” folder in the Sitecore Content Editor. The textbox above the left list box allows filtering of the list of forms. Multiple forms can be selected in the left list box and then the “Add” button can be clicked to move all selected forms to the right list box.

(Note – the screen cap above shows a customized version of the Dual List Box that I created, to fix a couple issues due to it being 9 years old, including replacing glyhpicons on the buttons with labels “Add All”, “Add”, “Remove”, and “Remove All”, and adding a double-click handler to add or remove items, and fixing a bug with lowercase form names appearing down at the bottom of Available Forms, and updating it to not clear the filter box or scroll to the top when moving items from one list box to the other, and changing the buttons to always be enabled.)

The gijgo date pickers allow setting the date range for the form submissions to be shown for the selected form(s), and the “Apply” button runs the report and populates a datatables grid with the form submissions desired. The “Search” box above the grid can be used to further filter the report output. The report output can then be copied to the clipboard with the “Copy” button, or downloaded with the “CSV” or “PDF” buttons.

The creation of the custom form submission report is described in the steps below:

1. Create a Controller Rendering

A Controller Rendering item named “FormSubmissionReport” was added in the Content Editor’s “Layouts” folder, in the “Renderings/Component/Report” subfolder. Its “Controller” property was set to “Web.MVC.Renderings.Component.Report. FormSubmissionReport.FormSubmissionReportController, Web.MVC”, and its “Controller Action” property to “ControllerRendering”.

2. Create a View

The “FormSubmissionReportView.cshtml” file was added to the “Renderings\Component\Report\FormSubmissionReport” folder in the Visual Studio solution.

The markup for the View is below. The “select” element contains a list of the available forms. The “input” elements with labels of “Date From” and “Date To” allow selecting the date range using the gijgo date picker. The “table” element at the bottom will be populated with a datatables grid once the form submission data is retrieved from the database.

<h3>Form Submission Report</h3>

<div>

<select id="selFormSubmissionReportFormItems" class="form-select"

multiple data-json="false" data-title="Forms">

@foreach (Item formItem in @controller.formItems)

{

<option value="@formItem.ID">@formItem.Name</option>

}

</select>

</div>

<label for="txtFormSubmissionReportDateFrom" class="col-form-label">Date

From:</label>

<input id="txtFormSubmissionReportDateFrom" value="@controller.DateFrom"

class="datepicker form-control" />

</div>

<label for="txtFormSubmissionReportDateTo" class="col-form-label">Date

To:</label>

<input id="txtFormSubmissionReportDateTo" value="@controller.DateTo"

class="datepicker form-control" />

</div>

<input id="btnFormSubmissionReportApply" type="button" value="Apply"

class="btn btn-primary form-control" />

</div>

<div id="divFormSubmissionReportSpinner" class="loading spinnercontainer"

style="display: none;">

</div>

<table id="displayTable" class="display table table-striped table-condensed

table-hover g_tblSort cell-border report-grid" cellspacing="0"

width="100%">

</table>

</div>

3. Create a Controller

The “FormSubmissionReportController.cs” file was added to the “Renderings\Component\Report\FormSubmissionReport” folder in the Visual Studio solution.

The “Sitecore.ExperienceForms.Data.SqlServer.dll” was then copied into the solution from the “bin” subfolder in the website’s “inetpub\wwwroot” folder, and a reference was added to the dll in the project containing the controller.

The Controller code has a “RenderView” method shown below, which defaults the DateFrom and DateTo fields, and gets the Form items from the “sitecore/Forms” folder in the Sitecore database:

The Controller code also has a “GetData” method shown below, which returns data to the “formsubmissionreport.ts” typescript file (described below in section 3) when the report user clicks the “Apply” button. It receives input params of “DateFrom” and “DateTo” containing the user-specified date range, and “FormItems” containing the one or multiple forms selected by the user from the list of available forms.

[RoutePrefix("FormSubmissionReport")]

public class FormSubmissionReportController : BaseController<FormSubmissionReportModel>

{

override protected ActionResult RenderView()

{

DateTime startDate = new DateTime(DateTime.Now.Year, DateTime.Now.Month, 1);

startDate = startDate.AddMonths(-3);

DateFrom = startDate.ToShortDateString();

DateTo = DateTime.Now.ToShortDateString();

Item formsItem = Sitecore.Context.Database.GetItem("/sitecore/Forms");

formItems = formsItem.DescendantsOfType(FormModel.TemplateID, deep: true).OrderBy(f => f.Name).ToList();

return Render("~/Renderings/Component/Report/ FormSubmissionReport/FormSubmissionReportView.cshtml", this);

}

public string GetData(FormSubmissionReportParam param)

{

var listFormSubmissionColumns = new List<FormSubmissionColumn>();

var listFormSubmissions = new List<Dictionary<string, object>>();

DateTime dateFrom;

DateTime dateTo;

if (DateTime.TryParse(param.DateFrom, out dateFrom) == true &&

DateTime.TryParse(param.DateTo, out dateTo) == true &&

param.FormItems.Count() > 0)

{

var formsConfigurationSettings = new FormsConfigurationSettings();

var connectionSettings = new

SqlConnectionSettings(formsConfigurationSettings);

var logger = new Logger();

var formDataParser = new FormDataParser();

var formFieldDataConverter = new FormFieldDataConverter();

var formDataProvider = new SqlFormDataProvider(connectionSettings,

logger, formDataParser, formFieldDataConverter);

var formEntries = new List<FormEntry>();

//First, get all distinct columns for the selected form(s)

foreach (var formItem in param.FormItems)

{

//Get FormEntries for current Form

formEntries = formDataProvider.GetEntries(new Guid(formItem),

dateFrom, dateTo.AddDays(1).AddTicks(-1)).ToList();

if (formEntries.Count() == 0)

continue;

List<FieldData> listFields = GetFormInputFields(new Guid(formItem),

formEntries).ToList();

if (listFields.Count == 0)

continue;

//Add Columns for current Form to list of Columns for DataTables, if

they don't already exist in the list

foreach (var fieldData in listFields)

{

var formSubmissionColumn =

listFormSubmissionColumns.FirstOrDefault(p =>

p.data.ToLower() == fieldData.FieldName.ToLower());

if (formSubmissionColumn == null)

{

var fieldItemIds = new List<Guid>();

fieldItemIds.Add(fieldData.FieldItemId);

listFormSubmissionColumns.Add(new FormSubmissionColumn { data =

fieldData.FieldName, fieldItemIds = fieldItemIds });

}

else

{

//a column with the same name already exists, so just add the

FieldItemId for the field on the current form to that

existing columns list of fieldItemIds

formSubmissionColumn.fieldItemIds.Add(fieldData.FieldItemId);

}

//Then get all of the records for the selected form(s)

foreach (var formItem in param.FormItems)

{

formEntries = formDataProvider.GetEntries(new Guid(formItem),

dateFrom, dateTo.AddDays(1).AddTicks(-1)).ToList();

if (formEntries.Count() == 0)

continue;

//Add row for each Form submission

foreach (FormEntry formEntry in formEntries)

{

var formSubmission = new Dictionary<string, object>();

formSubmission.Add("Created", new { display =

$"{formEntry.Created.ToLocalTime():yyyy-MM-dd h:mm tt}", timestamp = $"{formEntry.Created.ToLocalTime():yyyy-MM-dd HH:mm:ss}" });

if (param.FormItems.Count() > 1)

{

var form = Sitecore.Context.Database.GetItem(new

Guid(formItem));

formSubmission.Add("Form Name", $"{form.Name}");

}

for (int j = 0; j < listFormSubmissionColumns.Count; j++)

{

FormSubmissionColumn formSubmissionColumns =

listFormSubmissionColumns[j];

FieldData fieldData =

formEntry.Fields.FirstOrDefault((FieldData f)

=> formSubmissionColumns.fieldItemIds.Contains(f.FieldItemId));

formSubmission.Add(formSubmissionColumns.data, fieldData !=

null ? fieldData.Value : string.Empty);

}

listFormSubmissions.Add(formSubmission);

}

if (listFormSubmissionColumns.Count == 0 || listFormSubmissions.Count == 0)

{

listFormSubmissionColumns.Clear();

listFormSubmissionColumns.Add(new FormSubmissionColumn { data = "Results" });

var formSubmission = new Dictionary<string, object>();

formSubmission.Add("Results", new { display = "No form submissions found

for selected form(s).", timestamp = "" });

listFormSubmissions.Clear();

listFormSubmissions.Add(formSubmission);

}

else

{

listFormSubmissionColumns.Insert(0, new FormSubmissionColumn

{ data = "Created" });

if (param.FormItems.Count() > 1)

listFormSubmissionColumns.Insert(1, new FormSubmissionColumn

{ data = "Form Name" });

}

var json =

$"{{\"columns\":{JsonConvert.SerializeObject(listFormSubmissionColumns)},

\"formsubmissions\":{JsonConvert.SerializeObject(listFormSubmissions)}}}";

return json;

}

public class FormSubmissionColumn

{

//Property name used to store field name has to be called "data" for datatables columns

public string data { get; set; }

public List<Guid> fieldItemIds { get; set; }

public FormSubmissionColumn()

{

}

public class FormSubmissionReportParam

{

public string Operation { get; set; }

public string DateFrom { get; set; }

public string DateTo { get; set; }

public string[] FormItems { get; set; }

public string Search { get; set; }

public string ModelId { get; set; }

public int DisplayLength { get; set; }

public int Start { get; set; }

public int SortCol { get; set; }

public string SortDir { get; set; }

}

The first “for” loop above steps through the list of selected “FormItems”, and for each form item, it calls Sitecore’s “SqlFormDataProvider.GetEntries”, to get the “formEntries” list of form submissions for the form item. The “formEntries” list is then passed to the “GetFormInputFields” method shown below (along with its supporting methods and classes), which is based on the method of the same name in Sitecore.ExperienceForms.Client.Data.CsvExportProvider. The method calls “GetFieldItems” to get a list of all fields defined on the form item, and then gets a distinct list of all of those fields that actually exist in a “FormEntry” form submission record for the form, and unions that list with the fields that don’t exist on a “FormEntry” record, but with the suffix “ [Deprecated]” added for those unused fields. Note that the “GetFieldItems” method calls the “IsValueField” method to filter the returned fields to those that implement “IValueField”. “IsValueField” uses the same approach as Sitecore’s Export Data feature, which is to get the Fields[“Model Type”] of the field type, cast it as IViewModel, and then check if it “is IValueField”. If you have a field type that you don’t want to include on the report, e.g. a reCAPTCHA field, make sure it inherits from FieldViewModel instead of StringInputViewModel, since FieldViewModel does not implement IsValueField.

There is then a subloop through the “listFields” that was returned by “GetFormInputFields” and generates the list of columns for the report, named “listFormSubmissionColumns”. The data for each column is stored in a simple custom class named “FormSubmissionColumn” that is designed to allow fields from multiple forms to be shown in the same column on the report, as long as they have the same field name. The “FormSubmissionColumn” has a “string” property named “data” that stores the field name, and a “List<Guid>” property named “fieldItemIds” that stores one-to-many guids. There is one instance of “FormSubmissionColumn” for each distinct field name, which again is stored in the “data” property, and the “fieldItemIds” list property then stores one FieldItemId guid for each field, on one of the forms selected for the report, that has the field name that’s stored in the “data” property.

private static IEnumerable<FieldData> GetFormInputFields(Guid formId,

IEnumerable<FormEntry> formData)

{

GetFormFieldsEventArgs getFormFieldsEventArgs = new GetFormFieldsEventArgs(formId,

inputFieldsOnly: true);

GetFormItem(getFormFieldsEventArgs);

getFormFieldsEventArgs.Fields = GetFieldItems(getFormFieldsEventArgs.Item,

getFormFieldsEventArgs.InputFieldsOnly);

if (getFormFieldsEventArgs.Aborted ||

!string.IsNullOrEmpty(getFormFieldsEventArgs.Message))

{

return Enumerable.Empty<FieldData>();

}

FieldData[] allFieldsUnordered = formData.SelectMany((FormEntry entry) =>

entry.Fields).DistinctBy((FieldData field) => field.FieldItemId).ToArray();

FieldData[] array = getFormFieldsEventArgs.Fields.Select((Item fi) =>

allFieldsUnordered.FirstOrDefault((FieldData field) => field.FieldItemId ==

fi.ID.Guid) ?? new FieldData

{

FieldName = fi.Name

}).ToArray();

IEnumerable<FieldData> second = allFieldsUnordered.Except(array).Select(

delegate (FieldData f)

{

f.FieldName += " [Deprecated]";

return f;

}

);

return array.Union(second);

}

public class GetFormFieldsEventArgs : FormBuilderEventArgs

{

public Database Database { get; }

public virtual IEnumerable<Item> Fields { get; set; }

public Guid FormId { get; }

public bool InputFieldsOnly { get; }

public Item Item { get; set; }

public GetFormFieldsEventArgs(Guid formId, bool inputFieldsOnly = false)

: this(Factory.GetDatabase("master"), formId, inputFieldsOnly)

{

}

public GetFormFieldsEventArgs(Database database, Guid formId, bool inputFieldsOnly =

false)

{

Database = database;

FormId = formId;

InputFieldsOnly = inputFieldsOnly;

}

private static void GetFormItem(GetFormFieldsEventArgs args)

{

if (args.FormId == Guid.Empty)

{

return;

}

Item item = args.Database.GetItem(new ID(args.FormId),

args.FormBuilderContext.Language);

if (item != null && string.Equals(item.TemplateID.ToShortID().ToString(),

Settings.GetSetting("SitecoreFormsToSalesforce.FormTemplateId"),

StringComparison.CurrentCultureIgnoreCase))

{

args.Item = item;

}

private static IEnumerable<Item> GetFieldItems(Item item, bool inputFieldsOnly = false)

{

foreach (Item childItem in item.Children.InnerChildren)

{

if (childItem.HasChildren && !IsValueField(childItem))

{

if (!inputFieldsOnly)

{

yield return childItem;

}

foreach (Item fieldItem in GetFieldItems(childItem, inputFieldsOnly))

{

yield return fieldItem;

}

else if (inputFieldsOnly)

{

if (IsValueField(childItem))

{

yield return childItem;

}

else

{

yield return childItem;

}

private static bool IsValueField(Item item)

{

if (item.Fields["Field Type"] != null &&

(InstanceHelper.CreateInstance(Sitecore.Context.Database.GetItem(item.Fields[

"Field Type"].Value).Fields["Model Type"].Value) as IViewModel) is IValueField)

return true;

else

return false;

}

internal static class InstanceHelper

{

private static readonly ConcurrentDictionary<string, Type> ModelTypes = new

ConcurrentDictionary<string, Type>();

internal static object CreateInstance(string modelType, params object[]

parameters)

{

if (string.IsNullOrEmpty(modelType))

{

return null;

}

if (!ModelTypes.TryGetValue(modelType, out var value))

{

Type typeInfo = ReflectionUtil.GetTypeInfo(modelType);

if (typeInfo == null)

{

return null;

}

ModelTypes.TryAdd(modelType, typeInfo);

value = typeInfo;

}

return ReflectionUtil.CreateObject(value, parameters);

}

Returning to the “GetData” method, the second “for” loop again steps through the list of selected “FormItems”, and also again loops thru the list of “formEntries” for each form item. For each “formEntry” it adds an item to the list of rows for the report, named “listFormSubmissions”. Each row stored in the “listFormSubmissions” is a dictionary that contains a key-value pair for each form field for that form submission, with the key set to the field name and the value set to the field value. Each row also has a “Created” column added with the created datetime, and also a “Form Name” column if multiple forms have been selected for the report.

The “listFormSubmissionColumns” and “listFormSubmissions” are then serialized to json with a key of “columns” for the “listFormSubmissionColumns” items, and a key of “formsubmissions” for the “listFormSubmissions”, and then returned to the ajax call in the typescript file.

4. Create a “Form Submission Report” typescript file

class FormSubmissionReport {

$form: JQuery;

dataTable!: DataTables.Api;

ajaxUrl: string = '/api/sitecore/FormSubmissionReport/GetData';

constructor($formsubmissionreport: JQuery) {

this.$form = $formsubmissionreport;

this.$form.find('#txtFormSubmissionReportDateFrom').datepicker({

uiLibrary: 'bootstrap4'

});

this.$form.find('#txtFormSubmissionReportDateTo').datepicker({

uiLibrary: 'bootstrap4'

});

this.$form.find('#selFormSubmissionReportFormItems').DualListBox();

let classObj = this;

$('#btnFormSubmissionReportApply').on('click', function (e) {

e.preventDefault();

classObj.GridLoad();

});

}

GridLoad() {

this.$form.find('#divFormSubmissionReportSpinner').show();

let queryParams = new FormSubmissionReportParam(this.$form);

let classObj = this;

$.ajax({

url: this.ajaxUrl,

type: "POST",

dataType: 'json',

contentType: 'application/json',

data: JSON.stringify(queryParams),

async: false

}).done((data) => {

if (data) {

let tableHeaders: string = "";

$.each(data.columns, function (index, value) {

tableHeaders += "<th>" + value.data + "</th>";

});

$("#divFormSubmissionReportGrid").empty();

$("#divFormSubmissionReportGrid").append('<table

id="displayTable" class="display table table-striped

table-condensed table-hover g_tblSort cell-border

report-grid" cellspacing="0" width="100%"><thead><tr>'

+ tableHeaders + '</tr></thead></table>');

let $table = this.$form.find('#divFormSubmissionReportGrid').

find('#displayTable');

classObj.dataTable = $table.DataTable({

dom: 'Bfrtip',

order: [[0, 'desc']],

pageLength: 10,

scrollX: true,

buttons: [

{

extend: 'copy',

text: 'Copy'

{

extend: 'csv',

text: 'CSV'

{

extend: 'pdf',

text: 'PDF',

orientation: 'landscape',

pageSize: 'LEGAL'

}

data: data.formsubmissions,

columns: data.columns,

columnDefs: [

{

targets: [0],

render: {

_: 'display',

sort: 'timestamp'

}

]

});

this.$form.find('#divFormSubmissionReportSpinner').hide();

}

});

}

class FormSubmissionReportParam {

Operation: string = 'GetData';

DateFrom: string;

DateTo: string;

FormItems: string;

ModelId: string = 'none';

Search: string = '';

DisplayLength: number = 10;

Start: number = 0;

SortCol: number = 0;

SortDir: string = '';

constructor($el: JQuery) {

// Extract Values

this.DateFrom = <string>$el.find('#txtFormSubmissionReportDateFrom').val();

this.DateTo = <string>$el.find('#txtFormSubmissionReportDateTo').val();

var arr: any = [];

$(".selected.form-control > option").each(function (index, value) {

arr.push($(value).val());

});

this.FormItems = arr;

}

$(() => {

$('._formsubmissionreport').each((index, value) => {

new FormSubmissionReport($(value));

});

The constructor first sets up the date pickers and dual list box, then a “click” handler for the “Apply” button, which calls the “GridLoad” function to generate the report in a datatables grid. (Note: typings files need to be added for the dual list box, with the code “interface JQuery { DualListBox(): any;}”, and for the date picker, with the code “interface JQuery { datepicker(arg0: { uiLibrary: string; }): any;}”, and the typings files for datatables will need to be added, e.g. using npm)

When the “Apply” button is clicked, the “GridLoad” function first instantiates the “FormSubmissionReportParam” class, in which the constructor gets the “Date From” and “Date To” values from the page, and then gets the selected forms from the dual list box. The “GridLoad” function then makes an ajax call to the “GetData” method on the controller, described in section 2 above.

After the data is returned from “GetData”, the “GridLoad” function then loops thru the data.columns and adds a “<th>” for each item, with the “data” key used to get the field name value from the column items for the “<th>” inner text. It then adds the “<table>” to the parent “<div>” (named “divFormSubmissionReportGrid”), and then wraps a “<thead><tr>” around the “<th>” elements created earlier.

Finally the “GridLoad” function instantiates the “datatables” grid, setting the “columns” property to “data.columns” and the “data” property to “data.formsubmissions”, where “data.columns” and “data.formsubmissions” were the json objects created by the “GetData” method in the Controller in section 2 above.

Add a comment if you have any questions about using the Form Submission Report, and I will do my best to answer them.

Sitecore Form Submission Report

Comments

Post a comment