2020-12-23 Bulk Upload Survey Error - Outcomes

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

Dec 23, 2020 09:40 am Issue raised in teams, team looking into it	Open
Dec 23, 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.
Dec 23, 2020 14:04	Fix submitted for review
Dec 23, 2020 14:40	Fix deployed/released
Dec 23, 2020 15:57	User confimed fixed Open

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.