Custom Order of Columns in the Sitecore Forms Export

I am working on a Sitecore 10 solution, where we use the Sitecore Forms, and a strange issue occurred when generating the Forms report. Depending on the data submitted by the users, the columns in Excel appeared in a different order. This was very disturbing for the customer, who expected the same report format every time they did the export.

Upon investigating, I have discovered that, for non-mandatory fields left empty by the user, no record was saved in the FieldData Sql table. Therefore, when an export was performed, if the first submitted record had empty fields, the corresponding columns were added last in the Excel file. I have reported this to Sitecore and they said it is a known bug, reference number 435640. They also provided me with guidance into creating my own custom CsvExportProvider class.

So, I have created a new class, CustomCsvExportProvider, that inherits from CsvExportProvider. Then, by using as reference the code in the original class, I have added the GenerateFileContent() method, that I have adjusted, and the EscapeCsvDelimiters() method because it is a private one. Also, I have created a CustomComparer class that I used to sort the columns in the way I wanted. The initial solution was to use the Sort order values of each field for sorting.

Let’s look at the code below:

public class CustomCsvExportProvider : CsvExportProvider
{
    private readonly IFormDataProvider _formDataProvider;

    public CustomCsvExportProvider(IFormDataProvider formDataProvider) : base(formDataProvider)
    {
        Assert.ArgumentNotNull(formDataProvider, nameof(formDataProvider));
        _formDataProvider = formDataProvider;
    }
        
    protected override string GenerateFileContent(IEnumerable<FormEntry> formEntries)
    {
        Assert.ArgumentNotNull(formEntries, nameof(formEntries));
        var formData = formEntries.OrderByDescending(item => item.Created);
        var fieldColumnsList = new List<FieldData>();
        var stringBuilder = new StringBuilder();

        foreach (var formEntry in formData)
        {
            fieldColumnsList.AddRange(formEntry.Fields.Where(x => fieldColumnsList.All(c => c.FieldItemId != x.FieldItemId)));
        }
                
        if (fieldColumnsList.Count == 0)
        {
            return string.Empty;
        }

        fieldColumnsList.Sort(new CustomComparer());

        [...]
    }

    private static string EscapeCsvDelimiters(string fieldValue)
    {
           [...]       
    }

    public class CustomComparer : IComparer<FieldData>
    {
        public int Compare(FieldData x, FieldData y)
        {
            var xItem = Sitecore.Context.Database.GetItem(new Sitecore.Data.ID(x.FieldItemId));
            var yItem = Sitecore.Context.Database.GetItem(new Sitecore.Data.ID(y.FieldItemId));

            var xSort = 0;
            if (xItem != null)
            {
                int.TryParse(xItem.Fields["__Sortorder"].Value, out xSort);
            }

            var ySort = 0;
            if (yItem != null)
            {
                int.TryParse(yItem.Fields["__Sortorder"].Value, out ySort);
            }

            return xSort.CompareTo(ySort);                
        }
    }
}

In order to get the Export to call the new class, it needs to be registered in a config file:

<services>
    <register patch:instead="*[@serviceType='Sitecore.ExperienceForms.Data.IExportDataProvider, Sitecore.ExperienceForms']"               serviceType="Sitecore.ExperienceForms.Data.IExportDataProvider, Sitecore.ExperienceForms"    
implementationType="MyFormsProject.Providers.CustomCsvExportProvider, MyFormsProject" />
</services>

So, after setting all the Sort order values properly, the export reports were always displaying the columns in the same order. However, after a while, I have discovered another issue, that looks like another Sitecore bug. When saving a form in the Forms Dashboard, all the Sort order values from its fields are reset to 0. Until I get an answer from Sitecore Support, the quickest solution would be to add another field on the Form Field template, set the same values as I set on the Sort order and adjust the CustomComparer class to use it in sorting.

The only change we need to do is in the CustomComparer class:

public class CustomComparer : IComparer<FieldData>
{
    public int Compare(FieldData x, FieldData y)
    {
        var xItem = Sitecore.Context.Database.GetItem(new Sitecore.Data.ID(x.FieldItemId));
        var yItem = Sitecore.Context.Database.GetItem(new Sitecore.Data.ID(y.FieldItemId));

        if (xItem.Fields["Export Order"] == null || yItem.Fields["Export Order"] == null)
        {
            return 0;
        }

        var xSort = 0;
        if (xItem != null)
        {
            int.TryParse(xItem.Fields["Export Order"].Value, out xSort);
        }

        var ySort = 0;
        if (yItem != null)
        {
            int.TryParse(yItem.Fields["Export Order"].Value, out ySort);
        }

        return xSort.CompareTo(ySort);                
    }
}

Last but not least, don’t forget to publish the new Export Order field, as the Sitecore.Context.Database seems to be set to web instead of master.

Sitecore Forms and Attaching the Uploaded Files when Sending Emails

Recently, while exploring more and more the Sitecore Forms on a Sitecore 10 solution, I got to use the File Upload field type and I wanted to send the uploaded file as an attachment. It was a bit tricky to get the file in the code, so I am going to share in this article how I did it.

In the Execute method of the SendEmailAction class, where the FormSubmitContext is a method argument, we can access the File Upload fields, assuming there can be more than one, get the stream of data and add it to the attachments list. Please see the code snippet below:

var attachments = new List<Attachment>();
var uploadFieldList = formSubmitContext.Fields
      .Where(f => f.GetType() == typeof(FileUploadViewModel));
if (uploadFieldList != null && uploadFieldList.Count() > 0)
{
  foreach (var uploadField in uploadFieldList)
  {
    if (uploadField != null)
    {
      var uploadFieldVM = (FileUploadViewModel)uploadField;
      if (uploadFieldVM.Files != null && uploadFieldVM.Files.Count > 0)
      {
        for (var i = 0; i < uploadFieldVM.Files.Count; i++)
        {
          var uploadFile = uploadFieldVM.Files[i];
          uploadFile.InputStream.Position = 0;
          attachments.Add(new Attachment(
              uploadFile.InputStream, 
              uploadFile.FileName, 
              uploadFile.ContentType));
        }
      }
    }
  }
}

Very important, do not miss from the snippet above the line that repositions the stream at the beginning, otherwise the file will not be properly read: 

uploadFile.InputStream.Position = 0;

Then, the only thing left to do is to add the attachment list to the email message object.

Sitecore Forms and Multilingual Emails without EXM

I finally got the chance to work with Sitecore Forms on a Sitecore 10 solution. I think this is a great module that we’ve all been waiting for, but I got a bit disappointed when I saw that, by default, the Send Email functionality works only with EXM. Our client doesn’t have the EXM module, so I had to find other solutions for this. 

There are very good implementations for sending emails out there, and one that I tried was this: https://github.com/bcalisto-oshyn/sc-forms-emailsend . It works very well, including the fact that it accepts tokens. However, as you can see below, it uses the Parameters field from the Submit Action Definition, and this one is shared across all languages, therefore I cannot use it to pass the different language versions of the form fields.

I had to take a different approach. That’s when I decided to look closely into the default Send Email functionality and try to emulate it even without having the EXM module.

First of all, I created a folder and an Email template that I want to use to store the data to be sent. The template can be something very easy, like in the screenshot below, on the left, where you can decide which fields you want to make shareable. Then, in the Sitecore structure, we create the “Form Emails” folder, whose ID we will need later on.

Then we go in the core database to duplicate and adjust the default Send Email action, which is “Send email campaign message”, selected in the below screenshot: 

The new action is “Send Email”, and at first it will contain stuff that we don’t need. I am interested in keeping just the below, both in the tree and on the Presentation Details:

The “Text” renderings are for the HeaderTitle, HeaderSubtitle and for the ValidTokens, a functionality that I kept from the previous implementation (sc-forms-emailsend), but is not vital to our goal.

In the PageCode, there is a reference to the javascript file used by the “Send email campaign message”, which I have replaced with an adjusted copy of that code, placed here: /sitecore/shell/client/Applications/FormsBuilder/Layouts/Actions/SendEmailMessage.js . Mainly, I had to remove all the references to the items that are no longer used in my implementation. 
There are a few more important settings to be done here. First, the ServiceUrl for the “MessagesDataSource” needs to be changed from the Sitecore API call that retrieves the EXM messages to the Sitecore API call that retrieves the Email items from the “Form Emails” folder by using the ID of this folder as parameter.

Then, the ItemID and the ItemName need to be specified on the “Message” item, as below:

Make sure to adjust these in the SendEmailMessage.js code: replace the Id and Name properties with ItemID and ItemName in the setDynamicData function.

For the BindingConfiguration, we keep the messageId key, which we will use to pass the selected email ID. This should also be kept as is in the SendEmailMessage.js code.

We move back to the master database, where we need to define a new Submit Action. We will mention as the Model Type the name of the class where the code will be implemented, and we will select for the Editor the Send Email item that we created in the core database.

In the Forms dashboard, on the Submit button, we will select the Send Email action, which will prompt the screen to select from the existing Email items. The only downside is that the Email items need to be already created, they cannot be created from this screen. But then again, so is the case with the emails that come from EXM.

You can see below that what is saved is the messageId, by which the corresponding Email item will be retrieved in the code:

In the project, we need a model for the Email object and a SendEmailAction class:

public class EmailModel
{
    public virtual Guid? MessageId { get; set; }        
}

public class SendEmailAction : SubmitActionBase<EmailModel>
{
    public SendEmailAction(ISubmitActionData submitActionData) : base(submitActionData)
    {    }

    protected override bool Execute(EmailModel data, FormSubmitContext formSubmitContext)
    {
        [...] // checks for null or empty Guid
        var emailItem = Sitecore.Context.Database.GetItem(new ID((Guid)data.MessageId));
        var from = emailItem?.Fields[“From”].Value;
        [...] // get all the other item field values; replace tokens; send email      
    }
}

The emailItem will always be retrieved in the current language, thus enabling sending out the email in the right language version. Hope this comes in handy at some point!