The ultimate list of tips for ensuring data quality in ActivityInfo

In monitoring and evaluation, to be able to track progress and capture accurately a situation you need to work with quality data. ActivityInfo offers various features to help you design a data collection system that ensures that the data collected is consistent, accurate, and complete.

In this list, you can find various tips for form and database design in ActivityInfo which can help you design a robust system.

We will discuss the following tips for ensuring data quality:

• Ensure consistency with a list of predefined answers
• Define what is accepted as an answer and provide guidance
• Always collect required data
• Avoid duplicates
• Avoid duplicates for sensitive data
• Capture consistent dates
• Review and approve or reject data
• Ensure completeness even when you don’t use required fields
• Trace changes, revert deletions
• Prevent users from reporting for specific time periods
• Calculate automatically key numbers to avoid mathematical errors

1. Ensure consistency with a list of predefined answers

Both Reference and Selection fields allow you to create lists of options. However, as these fields serve different purposes, it is important to choose the appropriate type when designing a Form.

A Selection field allows you to create a list of options inside the Form whereas a Reference field references another Form (a 'Reference Form') where each option is another Record. In the second case, as the option is an actual Record, it is possible to include more information about that option (i.e. meta-data or other kind of information) in the referenced Form. You can use as many fields as needed to capture that information and by making them Key fields you can also bring that information into the initial Form (creating the 'cascading effect').

We strongly suggest the following best practices:

• Use a Single Selection field to capture a basic list of options that don't require further meta-data or additional information. Examples: age categories, list of options for biological sex, etc.
• Use a Reference field to link to a Reference Form where you will collect additional information for each option (Record). You expect changes to occur on that data (data is described as dynamic rather than static) and you need to link to these options in many Forms in your Database in a consistent way. Examples: partner organizations, list of institutions/entities, etc.

Use reference forms or reference fields to provide users with a selection of predefined answers and ensure their answers don't have spelling mistakes, typos or irrelevant responses.

Use the cascading effect with multiple keys to provide more possible answers.

For shorter lists of options, use single or multiple selection fields instead of a text field. This can be done when there is an available list of possible answers and the question is not open-ended.

2. Define what is accepted as an answer and provide guidance

If you use text fields and you need to collect answers that must follow a specific format, use input masks to define that format. You might for example need to collect text including a social number that must follow a specific combination of letters and numbers.

Alternatively use validation rules to define the criteria under which an answer will be accepted. Use RegEx formulas, other formulas or any other rule you need to achieve even more advanced control on the input. Provide some guidelines in the Description to guide users.

During data entry users will get feedback if they don’t follow the input mask format or the validation rules.

Set relevant rules to gather only the relevant data from the relevant people. Use formulas to achieve more advanced control on when a question appears.

3. Always collect required data

Make a field required by checking the box in the card editor to ensure that a user fills in the field before saving the form.

4. Avoid duplicates

To ensure that you collect unique data you can use one field or a combination of fields as key fields and ensure that you have unique records corresponding to your questions. All data related to a key field (e.g. name of a school) are collected in one place (i.e. a record).

You can add more key fields to create a more advanced identifier for your record (e.g. name of school, province and headmaster name).

5. Avoid duplicates for sensitive data

Instead of key fields you can use Serial numbers to distinctly identify each record without revealing other information about it. Add a prefix formula to the Serial number based on a text field, a single selection field or a field of another form using a reference field.

6. Capture consistent dates

All date fields (such as Month, Week, Fortnight, Date etc.) follow the international date format and thus you know that your data related to dates will always be formatted in the same way. Also, when adding such a field, users can use a calendar to select the date of their preference instead of typing in the date.

Never ask users to provide a date using a Text field as you won’t be able to use that field to lock a database for a specific period of time.

7. Review and approve or reject data

You can add a Reviewer’s field or section to your form and make it available only to specific users responsible for reviewing data. This way, they can quickly mark records as accepted or rejected and arrange follow up actions.

8. Ensure completeness even when you don’t use required fields

After you have collected some data you can sort and filter all records to search for missing data in the table view. You can filter columns by blank to reveal all records missing information.

9. Trace changes, revert deletions

Trace any edited or deleted data using the History panel in the table view. View which user added, edited or deleted a Record and the exact changes that took place.

Use the Audit log to find more details about the whole database and to revert deletions.

10. Prevent users from reporting for specific time periods

Lock databases to prevent users from adding or editing data that fall after, before or within a specific period of time. Make the date field a key field to be able to apply a lock based on specific dates.

11. Specify units for indicators

When you use quantity fields to collect quantitative data make use of the unit property to specify what the number collected refers to. This can help avoid confusion and is especially useful when collecting numbers related to a metric system.

12. Prevent users from accessing and editing data not related to them

You can use the multi-partner reporting database template to ensure that a user will only see data related to their partner organization and not data reported by other organizations or teams. You can do that by adding all Partner organizations to the reference form of the database template and then assigning the Reporting Partner role to the invited users. All forms should include a reference field referencing the Partner Form of the database.

13. Calculate automatically key numbers to avoid mathematical errors

When you use multiple quantity fields in your form and you want to summarize some numbers, find the average or do other mathematical calculations instead of asking users to type in the result of the calculation, you can do that automatically in your form. Use calculated fields and formulas for simple or more complex calculations and prevent erroneous data.

How many of these tips did you already know?

Try applying all of them in your current forms or adjust the forms of our available database templates to practice along.