Recently a Smap customer, who are conducting a hospital audit, encountered problems loading a survey that had just under 2,000 questions. The problem was caused by a limit in the Postgres database. There can be no more than 1,600 columns per table. In a survey you can reach this limit either by specifying:
- 1,600 questions
- or 160 multiple choice questions with 10 options in each, since a separate column is used for each option in a multiple choice question
- or a combination of the above
The solution to the problem is to enclose some questions within a repeat group with a “repeat_count” of “1”. These questions will be written to a separate table in the database.
While the data collector is filling in the survey they will not be aware of this repeat group as it is set to automatically repeat just once.
When you export the survey results, if you select the “pivot” option, then the records from this “begin repeat” subform will be combined with the records from the main form. However you will also have the option of exporting the main form and its sub form(s) separately. I’ve seen exports of surveys to spreadsheets with over 3,000 columns and I’m not aware of any constraints on this.
I was able to load the large hospital audit survey after adding this begin repeat, end repeat solution. A future release of Smap will check that the maximum number of questions per form is not exceeded. If it is an error message will be shown.