2020-12-23 Bulk Upload Survey Error

Date	23 Dec 2020
Authors	Philip Wilsdon (Unlicensed)
Status	Resolved
Summary	The uploaded outcome value was not matched correctly to our reference values. The outcome being missing due to the mismatch caused an internal error which was not reported properly.
Impact	Bulk upload

Non-technical summary

The “Outcome” column of the Assessment bulk upload template was not being handled correctly and failed to match the existing reference values. This was made worse by the server failing to properly report the error as part of the upload verification process, instead causing an “internal server error” with no specific detail available.

Timeline

23 Dec 2020 09:40 am Issue raised in teams, team looking into it
23 Dec 2020 12:44	Initial investigations Adding UUIDs broken generic upload - already have a fix ready Trying to use a `null` outcome when no match found - fix ready The 10.2 outcome causing issues in bulk upload due to incorrect conversion between numerical and text values.
23 Dec 2020 14:04	Fix submitted for review
23 Dec 2020 14:40	Fix deployed/released
23 Dec 2020 15:57	User confimed fixed

Root Causes

The addition of a UUID column to the Outcome and Reason tables in the database was not reflected in the upload service’s Outcome and Reason objects, causing a failure to load any Outcomes or Reasons from our reference data.
When an Outcome was not found for the uploaded value the null value was still used, causing an error on the server. This caused immediate failure rather than including the correct error message in the validation report.
Outcome 10.2 was not correctly converted from numeric to text, resulting in a mismatched outcome of “10.199999999”. This field is meant to be a text field, but due to the absence of text values Excel had converted it to numerical.

Trigger

A user reported in the team's channel they had issues with bulk upload

Resolution

UUID added to the upload service’s Outcome and Reason objects.
A null check added for the Outcome before using it.
Implemented better handling of numeric to text conversion to avoid the float point issues.

Detection

A user reported in Teams Support Channel

Actions

Investigate a more robust way to specify or determine the XLS cell’s type (numeric, text, date etc.) instead of relying on Excel to set it correctly during authoring.

Lessons Learned (Good and Bad)

Casting numbers to strings don’t always behave as expected.