Description

The following symptoms may occur when a contact submits a particular web form created using the WFFM module two or more times.

• An error appears in log files when the data aggregation is being performed:

  Message: The conversion of a datetime data type to a smalldatetime data type resulted in an out-of-range value.

• Data in the WFFM report may be missing or incomplete.
• Exported data from the WFFM report may differ from the data displayed in the report grid table.

Solution

The following solution runs an aggregation process for all data from the collection (MongoDB) database. It allows to restore missing data, and to fix the issue for future runs of the aggregation processes.

To implement this solution, rebuild the reporting database:

• For Sitecore 7.5 and its updates - as described in section 5.2 Rebuilding the Reporting Database in the xDB™ Upgrade and Conversion Guide document except for the last step - clicking the Start button.
• For Sitecore 8.0 - 8.1 and its updates - as described in the "Walkthrough: Rebuilding the reporting database" article except for the last step - clicking the Start button.

1. Execute the "WFFM_Analytics.sql" script from the /Data folder on the reporting.secondary database; 
2. If you have the EXM module installed, execute the "Sitecore.EmailExperienceManager" script from the /Data folder on the reporting.secondary database; 
3. Execute the "WFFM.StoredProcesdure.AddFormStatisticsByContact" script on the reporting.secondary database; 
4. Add the following lines in bold to the /App_Config/Include/Sitecore.Analytics.Processing.Aggregation.config file: 

   <!-- Facts -->            
   <SqlMappingEntity type="Sitecore.Analytics.Aggregation.SqlMappingEntity, Sitecore.Analytics.Sql">
     <Table>Fact_FormStatisticsByContact</Table>
     <Routine>Add_FormStatisticsByContact</Routine>
   </SqlMappingEntity>         
   <SqlMappingEntity type="Sitecore.Analytics.Aggregation.SqlMappingEntity, Sitecore.Analytics.Sql">
     <Table>Fact_Conversions</Table>
     <Routine>Add_Conversions</Routine>
   </SqlMappingEntity>

5. Perform the last step in the guide - click the Start button.