Designing long questionnaires with a spreadsheet

For very long questionnaires, you may to prefer designing your questionnaire in a spreadsheet rather than ActivityInfo’s form designer.

Step 1: Examine the questions and find their respective field type

To start, we have to take a look at our questionnaire and start examining every question. As soon as we know what kind of questions we have in front of us, we can translate them to fields in ActivityInfo.

So for example, if you have a question that is open-ended, you might need a text field or if you are asking about an indicator, you need a quantity field. If you respondents need to select from a list of predefined questions, then you need a selection field or a reference field. Respectively, when asking for geography related information you can use a geo point field. When asking for information related to time periods you might need to use a date, month, week or fortnight field. If you want users to provide additional documents or images you will need an attachment field. Take a look at all the available fields in our User Manual.

In our spreadsheet, we need to call each field - corresponding to a question - with a specific name. Refer to the spreadsheet format reference for a complete list.

Step 2: Add headers and fill in the necessary fields in the spreadsheet

Once you have a good understanding of all the questions included in your questionnaire, you can start working on the spreadsheet. Your spreadsheet should have the following headers in the first row:

Not all cells need to be filled in. Most of them are optional. If you omit the headers of optional fields you will still be able to upload the questions.

The required ones are the ones in the columns of:

• FieldType: defines what kind of field we will use and can be taken from the spreadsheet format reference.
• Choice: this is repeated for every option that can be used as an answer in selection fields; so it is only needed when using selection fields
• Section header indentation level: indicate 1, 2 or 3 depending on the level of the section; this is only needed when using Section header fields

Use our Questionnaire Import Template to easily generate a spreadsheet with all the required columns.

Step 3: Enrich the questions

While looking at your questionnaire you might decide that some questions should be required or need more context. You might also want to get unique replies based on one or more fields or define units for your indicators. For these, we will work with the columns: FieldDescription, Required, Key and Units.

In the FieldDescription column, you can just type the description you want for every question. If you want to make a field required or key you can add TRUE, if not you can add FALSE. Please keep in mind that if you use a serial number field, this is the key for your form and you cannot add more keys in the form. In the units column, simply type the unit you want to use but note that it should only be filled for quantity field types.

Step 4: Add formulas

By adding formulas in our spreadsheet we make the questionnaire more interactive. We can define when a question will appear to the user, or when it can be counted as valid and we can automate some calculations in our form. Once again, you need to take a good look at your questionnaire and decide if and when formulas are needed. You can learn more about formulas in the article "Data quality and data analysis tips with formulas in ActivityInfo". In our user manual, you can view all formulas.

You can ask the following questions to decide on what you need:

• Is the question relevant to all? If the question is not relevant to all, a relevance rule can be used to show the question only when a specific condition is fulfilled.
• Are there restrictions for accepted answers? If there are restrictions for accepted answers, you can add validation rules to define specific conditions for accepting an answer.
• Is there a need for quick analysis? If there is a need for quick analysis, you can add Calculated fields in your spreadsheet.

Please note that to make it easier to use each field in formulas, you must assign a code to the fields you want to use. The code can be as simple as a letter or more letters without spaces in between them.

In this table, we added codes to both fields and then we simply wrote the formula in the cell that corresponds to the question where we want the validation rule to apply. This validation rule determines that only dates after the Date of Arrival will be accepted in the Date of Intake field.

In this table, we added a relevance rule to the multiple select field. The multiple select question “Please select all symptoms” will only appear if the answer to the single select question "Was the the individual sick" is “Yes”. Once again, we added a code to the single select question to make it easy to use.

In this table, we added a formula for a calculated field. Note that the field type now is ‘calculated’ and the formula is added in the Formula column.

Tip: If you want to create your own formulas for validation and relevance rules based on other fields, but you are not sure how to write the formulas, you can design a draft form in ActivityInfo with some fields with relevance and validation rules using the form designer, where you don't need to write a formula on your own. Then, simply export the fields and view the formulas in the exported spreadsheet to take some inspiration for your own formulas.

Step 5: reference other forms

In ActivityInfo, you can link forms to one another. This allows data entry users to select an answer in a form from a list of options that appears in another form. So for example, you might have in Form A a list of locations. Then, in Form B you can refer to this list of locations so users can select one of these locations from a drop-down list. When you want to reference a form in your spreadsheet, you need to have first added that form in ActivityInfo. You can either design that form or import a questionnaire using the method we discuss in this article. In any case, the form must already exist in ActivityInfo.

To reference that form you need to obtain the id of the form. To do that simply navigate to the Table View of that Form and copy it from the URL.

In the image above, you can see where exactly you can find the form id. In this example, we want to reference this list of programmes in the questionnaire we are creating in our spreadsheet.

For this reason, we will copy the code of the form that corresponds to this list and we will paste it in the reference form column in our spreadsheet. Of course note that we added a FieldType 'reference' to create the reference field. Please note that this specific form id won't work in your questionnaire, as the form we reference doesn't exist in your database.

Step 6: Import the spreadsheet to ActivityInfo

When the spreadsheet is ready, copy all the fields and add a new form in ActivityInfo. Give a title to the form, click on the "+" icon to reveal the field pallete.

Importing the spreadsheet

Click on "Paste field list from spreadsheet". Paste there all the fields and click on "Add fields".

The fields from your spreadsheet will now appear in the form designer.

Click “Save” to save the form with these new fields.