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 it to an integer 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.

Tutorial 1 – Data Management

There are several ways to manage collected data and the best approach depends on the problem that you are trying to solve.

  1. Using the Modify page.  You may be an administrator and you want to fix errors in the data such as misspelt locations, other text used to answer a select question when a choice should have been selected and so on.  This is sometimes called “data cleaning” and it can be done quickly and easily in the modify page.
  2. Oversight forms.  You may want to add notes to a data record or update a field such as a priority and this may be done as part of an ongoing process.  In this case you can add an oversight form to the data collection form and if if it has questions with the same name as the data collection form then the answers to these questions can be changed.
  3. Tasks.  You may want to send somebody out to update a previously submitted form and you want them to change existing data and add new data. You can assign a task to do this.
  4. Webforms.  You may want to completely go over a single submission and change many answers.  In this case you can edit the data using a webform.
  5. Export / Import.  Alternatively if you want to completely revamp all your data in a spreadsheet you can export it and then re-import the updated spreadsheet.

This tutorial will just cover the first of these which is the “Modify Page” approach.

New Feature added 18th April 2018.  A checkbox has been added to the main screen which is labelled “Select data to change using a primary key”.  This allows you to select a specific record in submission to change.  Its use is covered in a new section after “updating other question types”.

Getting Started

Modify is part of Analysis so after you logon select the Modules menu, then Analysis and finally the Modify menu.

Updating Text

Basic Text Questions

In my sample form I collected some data that included a text question asking for the country.  On the modify page I selected this “Sample Form” survey and then the country question.

Each distinct answer is shown along with a count of how many times in the survey that answer was given.  So you can see in the above table that “Uganda” was entered 3 times.  “France” was entered only once however there is also an entry for a country called “Franc”.  I’m going to assume that’s a typo and so I click on the “Update” button to the right of that text value.

A dialog box is shown.  At the top is written the action that is going to be performed: “Modify Franc To”. Then there is a text box labelled “Reason”.   I have entered “Spelling Mistake” in here.  This reason will be shown in the audit trail of changes.  Next there is a select box called “Existing Choice” which is disabled because there are no select choices associated with this question. Next is a select box called “Existing Text”.  This allows you to select any of the other answers that have been submitted for this question.  In this case I selected the answer “France”.  Finally there is another text field where you could have entered any new value for the text that you wished.

After you press the Save button you will see the updated results.

Now France is entered twice.  Exported results and graphs will use this updated data.

Replacing an “other” text question with a select choice

This time I selected the question “transport_other”

You can see there are 3 distinct values.  The first one is blank and it occured 4 times.  This is the number of times that the “other” text question was not relevant because a choice was selected and hence no answer was entered.  The values of “Scooter” and “Truck” were each entered once.  I am going to replace the answer “Scooter” with one of the choices in the select question and so I click on the “Update” button next to the “Scooter” text.

For the reason I have entered “Categorise Scooter as Motorbike”.  The “Existing Choice” select box is now enabled and I selected one of the choices from the select question which was “Motorbike”.  The system knows that the select question is related to the other question because there is a relevance on the other question that references the select question.  Rather than selecting an existing choice I could also have just changed the text in other to say “Moped”.

After clicking Save you will see that “Scooter” has gone and there are now 4 blank answers for other.

However if you export the data you will see that the transport select question now has an additional response for “Motorbike”.

Adding new select choices to replace other text

This is done exactly the same way as replacing text with an existing choice.  However you first need to add your additional choice to the select question, either using the online editor, or in XLSForm and then replace the existing form with the updated one.  You will then be able to select the new choice.

Updating Other Question Types

The previous sections covered updating text answers using only the existing answers to choose what will be updated.   This is great for changing all occurrences of a misspelt name however sometimes only some values need to be updated.  For example if you need to fix a wrongly entered age for a person then you don’t want to change all occurrences of that age.

Modify allows you to update text, select one, integer and decimal answers that have a specific value and also a text question with a specific value.  Note this might not always be discriminatory enough to get just the record you want to update in which case you will need to use a different method such as Webforms or an Oversight form.

I’m going to change an “age” question.  First I select the name question.  This has to be a text question. Then I check the checkbox “Update a different question” and then I selected the “age” question.

You can see in the results that there were 2 people called Richard one of whom has an age of 34 and the other 55.  I’m going to change the age of 55 to 57 by overtyping it and then pressing the update button at the bottom of the list of data.  That is all there is to it.

Updating Other Question Types For a Single Record

You may want to just change a value that is in error on a specific submission.  You can now do this by selecting the checkbox “Select data to change using a primary key”.

You will see that the drop down fields for selecting language and question will be replaced by a drop down to select the Form.  The checkbox “Update a different question” will also be automatically selected and disabled so you cannot change it.  In other words you are not allowed to change the primary key value only the value of another question.

You will also see the the “Text” value for each record on the left hand side of the form is a number which will correspond to the unique key of the record that you want to replace.

For the top level form discovering the primary key for the record that you want to change is easy.  This key is included in all exports of data.  If you want to update a specific value in a subform then you will need to use the table view in the analysis dashboard to find the unique key in the subform.

Reviewing and Reversing Changes

All changes to the data are audited and can be reversed.  To see the audit log select the “Review” Menu.

Above you can see the audit trail for the sample form where I recategorised scooter as a motorbike and changed the name of the country to France.  Each change includes the reason, a description of what happened which is created by the system and the name of the person who made the change.  If you click on the details button it will give more information about the change including listing the primary keys of all the records that were changed.  To reverse the change you simply click on the reverse button and the change will be undone.

There is a caveat here, you can reverse changes in any order however if one change depended on another and you don’t reverse them from last change to first then you may be left with some altered data in the system. If that were to happen then an error should be shown and you can fix the problem some other way.

Accountability Product

Announcing another “Product” from Smap.  These products consist  of form templates for and instructions on how to set them up on a Smap server to address a specific objective.  Or at least start to address that objective as you will want to customise the forms to suit your needs.

Overview

The accountability product includes 2 forms the first of which is a feedback form that can be completed on a mobile phone or in a web browser. The second is an oversight form that is attached to the feedback form and that can then be used to record actions taken in response to the feedback.

Feedback Form

The feedback form is just a standard form that can be completed in the fieldTask application on a phone or tablet as well as in web-forms in a browser. If you were taking feedback in a call centre then you would presumably use web-forms. Alternatively if you were in a remote location without data connectivity then you would probably use fieldTask.

Oversight Form

The oversight form has the following data entry fields:

  1. Responsible person. A text field to enter who is responsible for processing the feedback.
  2. This has the same name as the priority question in the feedback form and hence it shows the same value. The priority can however be changed from the value initially set in the feedback form.
  3. Translation of Feedback. This is a text question that is linked to the feedback description set in the feedback form.
  4. Transcription of the issue statement. The manager can play the audio statement and type the text into the text field.
  5. Image labels. The labels shown were generated automatically by Amazon Rekognition. However they can then be manually modified by the user.
  6. Date of any action taken

Getting Started

You can download a zip file containing this product and a PDF with instructions from the Smap Products web page.

 

Version 18.04

Improved Performance

Refreshing fieldTask will now be faster.  Note This requires fieldTask 5.33.  The changes are:

  1. If you change an image or other media file that is used in a question or choice then that new media file will be downloaded automatically to the mobile device using push notifications.
  2. A media file, including a CSV file, will now only be downloaded to a phone if it has changed.
  3. Media and CSV files are only downloaded once if they are used by more than one form on the device.  In other words as long as the media file is coming from “Shared Resources” it is also shared between forms on the device.
  4. If the media changes but the form has not changed then the form is not re-downloaded only the changed media files are.

You can expect substantial reductions in download time under the following circumstances:

  • You have a form that uses many images in questions and choices but you are making changes to that form by adding new questions or changing the question text.  Now after each form change only the new form will be downloaded and not all the images.
  • You have a csv file that is constantly being changed.  Now only the changed csv file will be downloaded and not the form and any other csv files or media.

However performance may be slightly worse if you have many forms that use media and only one is updated.  The reason being that during refresh the manifest will now be checked for all the forms that use media to see if the media file has been changed.  Previously the change in a media file would not have been picked up and the form would not have been updated on the device.  This is a quick operation and will only be noticed if the number of forms is quite large.

Other Features

Filtering on table view in dashboard

You can now use “advanced filtering” in these table views.  For example adding a filter such as

${district} = ‘capital’ and ${gender} = ‘female’

The number of records that match the filter as well as the total number of records will be shown just above the table of data.

Issues Fixed

  • Issue 63.  Labels are not shown in PDF exports if the label is for a select question that uses an external CSV file to get its choices
  • Issue 153.  When you download data and upload again in Analysis there is a warning message about “question name missing”
  • Issue. The Legacy XLS export does not allow select multiples to be split into separate columns.
  • Issue.  Replacing a survey that used uncompressed select multiples results in the select multiple questions being converted to compressed but the old data is not converted making it invisible.

Medical Clinics Product

Overview

This product has 3 sample forms that can be used to complete a process. The process steps are:

  1. A person is registered as a new patient or as a returning patient in which case their details can be reviewed and updated.
  2. The doctor sees the patient, reviews their history and provides a consultation during which they may prescribe some medications.
  3. A nurse dispenses the medications prescribed by the doctor.

Process

Each of the process participants has their own device on which notifications will appear.  Hence the doctor will get a notification when a patient has been registered.  This notification will appear on their phone or tablet as a list entry with the patient’s name on it.  When the doctor is ready to see the patient, then they can select the patient’s name to see their details and record the new consultation.

View of doctors tablet

The registration person is the only one who needs to select the empty form which has a yellow icon like a clipboard. The nurse and the doctor will get forms pre-populated with patient data. These will show as a white clipboard icon.

Getting Started

This product consists of a downloadable zip file containing 3 forms, CSV file and a PDF with instructions on how to set it up.

  • register.xlsx.  A form to register new patients and update the details of existing patients.
  • doctor.xlsx.  A form for the doctor to record details of their session with the patient.
  • dispense.xlsx.  A form for recording medications dispensed.
  • medication.csv.  A CSV file with a list of medications that is used by the doctor and dispense forms.
  • medicalClinicsProduct.pdf.  A PDF with instructions on how to set up the product.

Head to the Smap Products web page to download.

Customisation

The forms, and indeed the process, will need to be modified to suit your needs.  They are just the starting point and the doctor form in particular will require many more questions to be added.  The forms can be edited with the XLSForm editor from odk.  Documentation on this is available here.  However there are a number of extensions to this editor that have been added by Smap in order to support using forms in a process.  These are documented in the instructions that are included with the downloadable ZIP file.  You will also of course need to set up appropriate security if recording sensitive information.

Version 18.03

Version 18.03 is here with lots of bug fixes and the following two new features.

Using SVG images to Select Choices

Selecting Choices by clicking on an SVG image in fieldTask

You can use this feature in both webforms and in fieldTask.   The image needs to be a Scaler Vector Graphic (SVG) which is drawn using mathematical functions rather than being composed of pixels like in a photograph.  This means that the image can be enlarged to any size without becoming fuzzy.  To use an SVG image to select choices you need to:

  • Create your SVG file or get an existing one.
  • When you click on a region in your SVG editor it should show the “id” and allow you to edit that.  Set the ids to the name of the choices in your select list.
  • Attach the SVG file as an image on the select question
  • Set the appearance of the select question to “image-map”.

Example

To try this out you can download the sample survey from here.  Also download the sample SVG file (which I sourced from Wikipedia) and load both of them onto your server.

Reports

Yes Finally a new reporting page.  This can be accessed from the Analysis module by clicking on the menu item “reports”.  On this new reports page you will find a link to the old page (in case you are still using that).

How to use

Add reports by clicking the “Add Report” menu item.  You can specify a name for the report as well as parameters include filters, date range, split select multiples and languages.  This will create an entry for the report in the table containing:

  • The name of the survey
  • A name that you have given the report
  • An anonymous link.  You can send this link to anyone so that they can access the report.  No logon or account or password would be required.   This link does allow the recipient to change any aspect of the report.  Hence if you had set a data filter or a specific date range that cannot be changed.
  • An button that has available 3 actions:
    • Generate.  Runs the report to download the data.
    • Edit.  Allows you to change any aspect of the report.  Someone who has received an anonymous link for the report would see these changes the next time they accessed that link.
    • Delete. Will delete the report.

Limitations

  1. Only the new XLSX format report is supported.  Other types such as images, shape files will be added progressively and as requested.
  2. Support for security roles in the anonymous link have not been added yet

Contact me if you need any of this missing functionality urgently.

Version 18.02

New Form Management Page

The visual changes are minor however now when you click the “Upload Form” button it will use a new XLS Form uploader (see next section).  We no longer use the ODK pyxform loader.  Just in case this causes problems there is a link, under the upload button, back to the original page that still uses pyxform.  One reason you may want to use the old page is if you have to upload in XML file which the new upload function does not currently support.

There are 3 new columns in the forms list.

Group

The new XLS Form Uploader allows you to put forms into groups.  These will then share the same data tables.  Hence if you have a form with questions; name and age plus a second form in the same group with questions; name and gender.  Then the name answer from both forms will be put into the same table column and when you export results you will see data from both surveys in that column

Groups become really useful when you use tasks to progressively complete a survey.  So form one fills in some of the data then a user is assigned an instance of form 2 to get the rest of the data.  This instance can include the data collected by the first form.

You can also set a key policy of “merge” on surveys in a group so that records are updated rather than replaced.  This could allow you to create a records management system.

Replace

Yes you can now replace a form with another uploaded from an XLSForm template.  You can do this even after you have started collecting data.  If the form you are uploading does not have questions that were in the original form then any data collected for those questions will be hidden but not deleted.

Click the replace button next to the form to be replaced, specify the new template and click upload.

The only thing you should avoid changing is the “type” of a question.  This is only a problem if you have already collected data for the old type in which case future submissions will fail.  So if you need to change type, say from integer to text, then create a new question with a different name and delete the old question.  Alternatively if you are in a position to delete already collected data, perhaps it was test data, then you can do that before collecting new data.

Get Link

Click on this button to get a link to complete a webform that does not require a password.  You can copy this link and email it to someone who does not have an account on Smap and they can then fill in the web form.

If a form has a public link then the link button will be a darker shade of blue.  When you no longer want people without accounts to complete webforms then you should delete the links.

XLS Form Uploader

Choice Lists are Preserved

Previously if you had referred to a single choice list from multiple questions then after you uploaded that data this sharing of choices would all be lost.  If you then made changes to the form in the online editor and downloaded the resultant XLSForm you would have a mass of duplicate choices.  This no longer happens and you will get your original choice lists back.

Question Parameters

These have been added to odkCollect and fieldTask but previously could only be added to a survey using the online editor. Add them in the parameters column of the survey sheet.  Separate multiple parameters using a space. Examples include:

  • rows=5                    Set the number of rows in a text question
  • max-pixels=500     Set the maximum number of pixels on the longest edge of a photo to 500. The photo will be scaled accordingly.  This can be used to reduce the size of images.

And many others.  Check the Survey Edit Reference on the google help drive for a complete list.

Specify Security Roles

Roles you specify for a form will be created if they do not already exist.  If you add a role but do not specify columns or row filters then a person must have that role in order to be able to see the new form.

To add roles with column filters add a column  role::”role name” in the survey work sheet.  Specify any questions that should be visible to that role by putting “yes” in that role column for that question.

To add a role with a row filter add a column role::”role name” in the settings work sheet.  Specify the row filter underneath the role.

Other Uploader Changes

  1. Simpler clearer error messages
  2. Error messages are in multiple languages (well spanish at any rate, contributions for other languages are welcome)
  3. Support for questions of type “chart”

Bug Fixes and other Changes

  1. The api call that retrieved data in json format has been substantially optimised to reduce memory usage
  2. Issue 188. Times in PDF exports are now set to local time.
  3. The latest version of fieldTask is now included in the release so it can be downloaded from the web server rather than google play store.  The website will attempt to check if your Android version is greater than 4.0 and if so it will get the new fieldTask.  Otherwise it will get the old one.
  4. Added a user security group of “view_data”.  People with this role will be able to view aggregated results like an analyst but will not be able to delete data or create new forms.

Version 18.01

I’m still playing catch up on documenting the new features however its time to announce version 18.01.  Included is a summary report that helps with analysis of your collected data.

Summary Report

This is an excel file containing multiple worksheets including a worksheet for each question.  The data in these question worksheets will be formatted so that they summarise the responses to that question and can be be used directly to create charts. For Example the following chart was created directly from the data generated by the summary report.

How to find it

The Summary report is located on the browse results page which is in the web forms module.

  • Login
  • Select the menu modules then Webforms
  • Select the menu Browse Results

You can then select the menu “Summary Report”.

Options

The following dialog allowing you to customise your report.

  • Text Function.  This can be set to count or percent.  So if its “count” then the occurences of each different piece of text in an answer will be counted.  If you select “percent” and “Group By” then the percentage is calculated against the total count for each group value.  This was the setting used in the example chart shown above where the totals for male and female have been adjusted to each add up to 100.
  • Numeric Function.  This can be set to Average, Min, Max or Sum.
  • Group By.  A second question with which to group the results.  This will result in a two dimensional table as in the example above.

How to use it

The summary report is not intended to create your final report directly.  For example you may want the answers to some questions grouped by gender and the answers to other questions grouped by region.  Some results you may want as percentage, some as counts and some as both. Hence you would generate multiple summary reports using the above options and then copy and paste the tables, and charts you create from those tables, into your final report.

Filtering Data

Because the summary report is on the browse results page any filters you apply  will also be applied to the report.  Hence you may select that only data from a specific region where at least one text answer includes the word “drought” be included. Then generate the report.  The above filters will be shown on the “settings” tab of the summary report along with the numeric function, text function and group by that were selected.

Restrictions

Currently only data in the top level form is included.  If you have data in sub forms it won’t be included in the summary report.  This is because browse results only currently shows the top level form.  This restriction will be removed in a future release.

 

Version 17.12

There are many more features in version 17.12 than are documented here.   Other release notes will follow.

Charts in FieldTask

Yes!  you can show charts inside the form on your tablet or phone.   You will need fieldTask version 5.23 as well as Smap Server 17.12 in order to use these.  This work was sponsored by InformEd International.

Here is an example form with basic charts.  If you load the form in the link then you should see the following charts among others:

 

Showing time series data from other forms

Using the pulldata() function you can get data from previously submitted forms.  We have now added a new pulldata() type that will retrieve data as a time series.   Hence if you are collecting data over time and you want to see a chart of that time series you would use this new function.

  pulldata(‘chart_xxxxx’, ‘data_column’, ‘key column’, key value)

The ‘chart_’  in the file name field tells the server to return data as a time series suitable for charting.

A detailed example form with comments can be found here.

Bugs Fixed

  • Issue #156. Security roles page for a survey not being displayed
  • Issue #157. Crash on export with language other than english
  • Issue #158. Guidance not being added for tasks
  • Issue #159. SMS URL deactivated
  • Issue #160. Added a warning message on upload when a form references missing columns in a csv file

Version 17.11

Time for November’s release!

The changes are

  • Filtering of notifications and Exported Results
  • Can now set default value for questions in the online editor.
  • Can now specify the number of rows used by text questions in fieldTask
  • Improvements to form monitoring (See below)
  • Added option to do a full survey export without including its media.  Some surveys can have many gigabytes of images and other media submitted.  Just on the off change that you want to export the data and import it into a different survey but don’t need the images then you now have that option.
  • Set a new notification to “enabled” by default.
  • Show most recent data received first in table views in the analysis module
  • More spanish translations

Filtering of Notifications and Exported Results

When creating a notification you can now specify a filter so that this notification is only sent when data is received that matched the filter.  You can also use the same sorts of filter when exporting data from the analysis module.

Examples:

  • ${age} < 10      – Where the answer to the age question was less than 10
  • now() – ${_upload_time} > {2_days}     – Export data uploaded more than 2 days ago
  • ${name} not like ‘%om%’    – Any name that does not have the letters “om” somewhere within it

More information on specifying a filter can be found here: https://sg.smap.com.au/sqlFilterHelp.html

Improvements to Form Monitoring

Actually the main improvement is that this now works again!  The old approach relied on fieldTask notifying the server of the forms that the user had after they had done a “refresh”.  However this did not reflect changes when the user had downloaded a form using the ODK menus and without doing a fieldTask refresh.   Anyway recent releases of fieldTask no longer sent this notification.

The updated form download monitoring now records all form downloads whether as a refresh or when the form is selected explicitly.  One limitation of this new approach is that the device that did the download won’t be identified unless you are using version 5.14 of fieldTask.

Bug Fixes

  • Error shown when a user registered a new organisation has been fixed.  This message did not stop the registration proceeding but would have been pretty disconcerting to new users
  • Allow data to be imported when the question type is “range”