Hint: Changing the question after you have started collected data

The replace button on the form management page lets you replace one survey with another at any stage of the data collection process.  There are no restrictions on what you can change and this can cause some issues if you change a question type.

For example a customer had a question in their form “How far away from home is your work?”.  They originally specified this question as “decimal” and had collected data where it was recorded as 3.0, 4.5 etc.  However they later changed the type of the question to “text” and the submitted results contained values like “5 minutes”.  Unfortunately these results were not being applied to the database so they could not see the later results.

Cause of the Problem

Once you start submitting data for a form, a table is created to store that data.  The answers to different types of questions are stored differently:

  • integer question answers are stored as Integer
  • decimal are stored as double precision
  • text, select_one, select_multiple, barcode, image, video, audio are stored as text
  • geopoint are stored as geometries

So if you then change a text question to a select there is no problem but if you change an integer to a text then the data will not load.

Detecting the Problem

The monitor page will show the the errors and list the results that were not loaded.  To see these:

  1. Select the “Admin” Module
  2. Select “Monitoring”
  3. Set the source to “Submitted”
  4. Select “Show Instances”
  5. Select your project and survey if there are a lot of submissions from other surveys shown

The errors indicating a problem with the question type will look like:

ERROR: invalid input syntax for type double precision: “5minutes” Position: 127

So a bit cryptic but a problem with question type.  Its trying to write some text into a decimal question’s database column.

Fixing the Problem

Usually the problem can be fixed simply from the Analysis module.  Open a table view of your survey.  This will show the results that have been loaded.  Click on the icon “<” at top right to open the aside.

There are two useful buttons here: “Delete Data” and “Restore Data”.  However note the “warnings” section below before using them.

Case 1: You are still testing the form

If you are still testing the form select “Delete Data”.  As well as deleting the test data this will also delete the tables that store the data so next time you submit some results it will recreate these tables but using the latest question types.

Case 2: You have some valuable data loaded (or submitted)

In this case select “Restore Data”.  This will also delete the tables but it will then reload all of the data from the original submissions.

Warnings

Using “Restore Data” will

  1. delete any modifications you may have made to data after it was collected.  For example fixing spelling mistakes using the modify page.
  2. resend any notifications attached to your survey.  For example you may be sending an email with a PDF containing details on each uploaded record.  If you don’t want these emails to be resent then you can disable notifications while you do the restore.  However any records that had failed to load due to the question type change will then also not trigger the notification. So tricky.

Taking note of these warnings it is a good idea to change the data types of questions with care.

Leave a Reply

Your email address will not be published. Required fields are marked *