Created: 25 Jun 2021, last update: 30 Jan 2022
Getting data programmatically out Sitecore Forms, create a poll
With Sitecore Forms a content editor can easily create a poll or survey and export the data and analyze the result. But in case of a poll out of the box Sitecore Form has no option to show the aggregated result to the user.
If you want a poll on your Sitecore website, you can easily build it yourself, but this required some infrastructure to store the submitted data. However, not all organizations having a solid infrastructure as code setup and also if it is just a small thing it still cost effort to keep it running and, also think of onboard new developers and set up their dev environment. Another option is just using an external SaaS poll. Should be nice if the fully poll functionality was out of the box in Sitecore XP. This blog is about getting data programmatically out Sitecore Forms with as use case the poll, But also useful for getting the data for machine learning, for example.
IFormDataProvider
To keep close to Sitecore and avoid upgrade or configuration issues when things changes, you can use the IFormDataProvider. But the IFormDataProvider has limited methods.
namespace Sitecore.ExperienceForms.Data
{
public interface IFormDataProvider
{
void CreateEntry(FormEntry entry);
int DeleteEntries(Guid formId, DateTime? startDate, DateTime? endDate);
IReadOnlyCollection GetEntries(Guid formId, DateTime? startDate, DateTime? endDate);
int GetEntriesCount(Guid formId, DateTime? startDate, DateTime? endDate);
IEnumerable GetUploadedFileIds(Guid contactId);
IEnumerable GetUploadedFileIds(Guid formId, DateTime? startDate, DateTime? endDate);
int RedactEntries(Guid contactId);
}
}
You can use the GetEntries to get all Form data this can work for a poll when you have only 1 (radio) field and limited amount of data. You get all data and need to do yourself the aggregation.
var _formDataProvider = ServiceLocator.ServiceProvider.GetService();
var formdata = _formDataProvider.GetEntries(FormItem.ID.Guid,null,null);
var aggregatedResult = GetResult(radioField, formdata);
private Dictionary<string, int> GetResult(Item radioField, IReadOnlyCollection formdata)
{
var result = new Dictionary<string, int>();
foreach (var entry in formdata)
{
foreach (var field in entry.Fields.Where(x => x.FieldItemId == radioField.ID.ToGuid()))
{
if (result.ContainsKey(field.Value))
{
result[field.Value]++;
}
else
{
result.Add(field.Value, 1);
}
}
}
return result;
}
Stored Procedures
The FormDataProvider use SQL server and in the Database it call a stored procedure. The SP used for GetUploadedFileIds is usable to get field data.
DECLARE @return_value int
EXEC @return_value = [sitecore_forms_storage].[FormData_RetrieveFieldValues]
@FormDefinitionId = '{F3E7BC40-B228-4EB9-AD24-2DA0E4F73CDB}',
@ValueType = N'System.Collections.Generic.List`1[System.String]',
@StartDate = NULL,
@EndDate = NULL
SELECT 'Return Value' = @return_value
GO
But okay it works for a radiofield when you call it this way but no option to just select 1 field. Or to aggregate the data. Therefore we need to use SQL.
This SQL query do what you need for the poll example just select one 1 field, and also aggregated the data so there is just the data we need and much less network traffic:
SELECT
[Value], Count(0)
FROM
[sitecore_forms_storage].[FieldData] fielddata,
[sitecore_forms_storage].[FormEntries] formentries
WHERE
formentries.FormDefinitionId = '{F3E7BC40-B228-4EB9-AD24-2DA0E4F73CDB}'
and formentries.Id = fielddata.FormEntryId
and fielddata.FieldDefinitionId = '778764CD-F580-49D0-BFF2-A7FE28EC8D38'
GROUP by Value
Be aware that the forms database has no index on FormDefinitionId. So creating an index could make it faster.
Also it is possible to make the query simpler by removing the form id check, but then no more guarantee that it really belongs to the form:
SELECT
[Value], Count(0)
FROM
[sitecore_forms_storage].[FieldData] fielddata
WHERE
fielddata.FieldDefinitionId = '778764CD-F580-49D0-BFF2-A7FE28EC8D38'
GROUP by Value
Example code to execute SQL query to Sitecore form:
using System;
using System.Collections.Generic;
using System.Configuration;
using System.Data;
using System.Data.SqlClient;
namespace RadioPollResult.Services
{
public class SqlPoll
{
public Dictionary<string, int> GetAggregatedPollValues(Guid FormDefinitionId, Guid FormFieldId)
{
var setting = new Sitecore.ExperienceForms.Configuration.FormsConfigurationSettings();
var connectionsting = ConfigurationManager.ConnectionStrings[setting.ConnectionStringName].ConnectionString;
Dictionary<string, int> result = null;
string commandText = "SELECT Value, Count(0) " +
"FROM [sitecore_forms_storage].[FieldData] fielddata, [sitecore_forms_storage].[FormEntries] formentries " +
"WHERE formentries.FormDefinitionId = @FormID " +
"AND formentries.Id = fielddata.FormEntryId " +
"AND fielddata.FieldDefinitionId = @FieldID " +
"GROUP by Value";
try
{
using (SqlConnection connection = new SqlConnection(connectionsting))
{
SqlCommand command = new SqlCommand(commandText, connection);
command.Parameters.Add("@FormID", SqlDbType.UniqueIdentifier);
command.Parameters["@FormID"].Value = FormDefinitionId;
command.Parameters.Add("@FieldID", SqlDbType.UniqueIdentifier);
command.Parameters["@FieldID"].Value = FormFieldId;
connection.Open();
using (SqlDataReader reader = command.ExecuteReader())
result = this.ParseResults(reader);
connection.Close();
}
}
catch (Exception ex)
{
Sitecore.Diagnostics.Log.Error("Forms database communication error.", ex, (object)this);
}
return result;
}
protected Dictionary<string, int> ParseResults(SqlDataReader reader)
{
var pollvalues = new Dictionary<string, int>();
while (reader.Read())
{
string str = reader.GetString(0);
int count = reader.GetInt32(1);
if (!string.IsNullOrEmpty(str))
pollvalues.Add(str,count);
}
return pollvalues;
}
}
}
note: it should not take longer than a few ms, to increase scalability you could apply caching.
