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

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.

Reference field in data entry - list with one reference
Reference field in data entry - list with one reference

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

Reference field in data entry - list with multiple references
Reference field in data entry - list with multiple references

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.

Single select field in data entry
Single select field in data entry

Multiple select field in data entry
Multiple select field in data entry

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.

Input mask in data entry
Input mask in data entry

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.

Validation rule and description field in form design
Validation rule and description field in form design
Validation rule and description field in data entry
Validation rule and description field in data entry

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.

Relevance rule in form design
Relevance rule in form design

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.

Required property in data entry
Required property in data entry

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).

Key field notification in data entry
Key field notification in data entry

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.

Serial number with prefix in table view
Serial number with prefix in table view

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.

Date field in data entry
Date field in data entry

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.

Reviewer only fields in form design
Reviewer only fields in form design

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.

Filter by blank in table view
Filter by blank in table view

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.

History panel in table view
History panel in table view

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

Audit log in Database settings
Audit log in Database settings

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.

Locked form in data entry
Locked form in data entry

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.

Units in data entry
Units in data entry

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.

Calculated field with formula in form design
Calculated field with formula in form design

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.