Tables - rev 208

The documentation on this page corresponds to rev 208 (October 2016) of the tools suite. See Previous Distribution or Older Versions for documentation on earlier releases of this suite.

Release Designation

ODK Tables rev 208 (October 2016) is designated Alpha software.

See Release Designations for the meaning of this designation.

To determine the ODK Tables release, click on the About menu item. The release version will be displayed above the license information.

 

Overview

Note: ODK Tables only works on Android 4.1 and newer devices.

Tables is a program that allows you to visualize and update existing data. Using Tables as your entry-point to data collection, you will be able to gather data using ODK Survey, sync it to a server using ODK Services, and have other users download and edit this same data on their own devices.

We are including a sample application built on top of Tables along with a handful of data tables that showcase some features. Following the steps below will get Tables installed on your device and load those sample files onto your phone.

 

Installation

To install ODK Tables, you must enable downloading from unknown sources by checking the checkbox beside Unknown sources on the Security screen of your Android device's Settings application.

Then, open your Android device's browser and download rev 208 of the ODK Tables APK from here. Click on the ODK Tables rev 208 APK link on that page and download the actual ODK Tables rev 208 APK.

You should then be able to install that APK onto your device by choosing the Android Notification of the completion of the download. On some devices, you may need to open a file manager and double-tap on the downloaded file in the Downloads directory to trigger the install. On others, you may need to rename the file in the Downloads directory to end with .apk before double-tapping it to trigger the install.

 

Install Supporting Applications

ODK Tables requires:

  • ODK Survey
  • ODK Services

Download and install these from the same downloads page (see above). You must install the compatible versions of these tools (e.g., rev 208)

Install the following app from Google's Play Store (this is required):

  • OI File Manager

 

Install the Sample Application

There is one sample application for this release.

  • 5 demo apps

Unlike ODK Collect, the ODK 2.0 tools are application-focused. An application is identified by the name of the directory under the /sdcard/opendatakit/ folder. The sample application is named default, as are the sample applications described on the Survey and Services pages. This means that you can only deploy one application at a time onto a device. Later, we will explain how to rename one of these so that two or more applications can co-exist and not interfere with each other on this same device (this will require setting up an ODK Aggregate for that renamed application; each ODK Aggregate can host only one application at a time and must be configured with the application name that it will host).

5 demo apps

This sample application consists of 5 separate demo apps:

  1. Tea Houses - a fictional Benin Teahouse directory.
  2. Hope Study - a simplified subset of a perinatal follow-up application that was piloted on ODK Tables and ODK Collect (now converte to use ODK Survey).
  3. Plot - a fictional agricultural field pest- and yield- assessment application.
  4. Geotagger - a simple geotagging application.
  5. JGI - an app used to track the daily behavior of chimpanzees.

We will use the ODK 2.0 synchronization mechanism to install this app. It is about 26 MB in size and takes a few minutes to download from the web.

NOTE: Using the sync mechanism will delete all the configuration files for any data tables not present on the server. If you have experimented with creating forms and pushing them to your device, when you sync to the server, the synchronization process will delete these extraneous forms from your device and leave only the forms and files defined on the server.

To access this sample application and its 5 demo apps,

  1. Launch ODK Tables
  2. Click on the sync icon (2 curved arrows) to launch ODK Services directly into the sync activity
  3. From the menu, choose the Settings
  4. Choose Server Settings
  5. Choose Server URL and specify https://opendatakit-tablesdemo.appspot.com as the server URL (note the https).
  6. Because this server allows anonymous access, the Server Sign-on Credential can be set to anything (by default, this will be set to None (anonymous access), which is fine). Other options are Username and Google Account.
  7. Exit out of the Server Settings page, and then the Settings page, by using the back button.
  8. By default, the sync interaction will synchronize all row-level data and file attachments with the server (Fully Sync Attachments). Other options are to only upload attachments from the device to the server (Upload Attachments Only), only download attachments from the server to the device (Download Attachments Only), or to not sync any attachments (Do Not Sync Attachments). For the demo, please ensure that Fully Sync Attachments is selected.
  9. Click on Sync Now

The sync process will now begin. If you have selected to use a Google Account, you may be challenged to authorize access to your Google account information. Otherwise, the sync will begin and a progress dialog will appear. As stated above, this synchronization mechanism forces the configuration of the device to exactly match that of the server. Any local configuration files for data tables or forms that are not present on the server will be removed from your device (i.e., everything under the /sdcard/opendatakit/default/config directory will be revised to exactly match the content on the server).

NOTE: As a safeguard to prevent data loss, data tables that are only defined on the device will not be deleted. However, because their associated configuration files will have been removed, they are generally inaccessible until you restore their configuration files and their forms onto the device.

Once the configuration of the device is an exact match to that of the server, the data within the data tables are synchronized. And, finally, the file attachments associated with those data are synchronized. If you have a slow connection, it may take two or three tries before the sync is successful; the system stops at the first timeout and does not attempt any retries.

When complete, click 'OK' on the Sync Outcome dialog and back out of the ODK Services application, returning to ODK Tables.

ODK Tables should now present a custom homescreen with 5 tabs, one for each of the demos. If it does not, back out of ODK Tables and re-launch it.

Select a tab (demo application), then click the 'Launch Demo' button to enter that sample application.

Layout of Application Files

The layout of this release of ODK 2.0 tools is as follows:

  • /sdcard/opendatakit -- directory containing all ODK 2.0 applications. Each application is a sub-directory within this directory.
  • /sdcard/opendatakit/default -- default application name (directory) for the ODK 2.0 tools

Within the application folder (e.g., /sdcard/opendatakit/default), the following directories are present:

  • config -- contains read-only configuration files that define the user's application (e.g., the 5-demos example application you just sync'd from https://opendatakit-tablesdemo.appspot.com). Within this folder are:
    • assets -- contains files that initialize your data tables (in the csv sub-folder) and define the custom home screen and provides css files for overall appearance of your app, and js libraries and files for common behaviors in your app.
    • tables -- contains directories that are named with table ids. Within these sub-directories, the ODK Survey forms and table-specific html, js, and css files are found. For example, the html file describing the list view for the tea houses table is found in config/tables/Tea_houses/html/Tea_houses_list.html.
  • data -- contains the database and row-level attachments (files).
  • output -- contains files that are generated (e.g., detailed logging files) or exported (e.g., CSV files) by the ODK tools on the device.
  • system -- an area maintained by the tools themselves (e.g., ODK Survey, ODK Tables, ODK Scan, etc.). These files are extracted and placed here by the APKs. You should not modify files in this folder; when first started, the ODK tools sweep this directory to verify that these files match their internal copy. Any deviant file is replaced with a fresh internal copy.

The automatic configuring and loading of data into ODK Tables is governed by the config/assets/tables.init file. It provides a list of table ids and the csv files (located in the config/assets/csv folder) that should be imported to populate them. This is discussed in more detail here.

NOTE 1: This file is the only configuration file that is not sync'd to the server. This is to optimize start-up of your application on other devices; once this initial data has been loaded into your data tables and sync'd to the server, the other devices will obtain the data through an ordinary sync action.

NOTE 2: This file is scanned once. If the import(s) fail, it could leave some tables partially initialized. The file will be re-processed and data rows re-loaded by clicking on Reset Configuration on the Settings screen then exiting the ODK Tools and re-launching them. Upon being re-launched, the file will be scanned and processed.

Most of the app-level settings that are configured through the Settings page are stored in the config/assets/app.properties file. Excluded from this file are the Server Sign-On Credential type, and the values for that credential (e.g., username and password). This allows the application designer to specify and enforce most of the app-level settings (e.g., the server used when syncing) via the sync mechanism.

 

A Guided Tour

ODK Tables allows you to customize the app home screen. If you supply a custom home screen (config/assets/index.html), you will have the option of using this as the home screen of the app. This is what is displayed after downloading the demo application.

If there is no custom home screen configured, ODK Tables will display the Table Manager view, with the options to add new tables or data via CSVs (the '+' icon), export data to a CSV (the '->' icon), launch ODK Services (the two curved arrows icon), change app-level settings, or view the application version and license information.

From the custom home screen, you can get to the Table Manager view by clicking on the icon with three lines at the top right of the menu bar.

To enable or disable the use of the custom home screen, go to the Table Manager and click on the Settings icon. This will open a screen through-which you can access application-level settings. Click on Tables-specific Settings and then check or unchech the Use custom home screen checkbox. This checkbox is only enabled if the config/assets/index.html file exists. After making the selection, you will need to fully back out of the ODK Tools and re-launch ODK Tables to have it pick up the change and render the home screen.

Within ODK Tables, each data set can be viewed in three different ways:

  • List -- as a list of items, rendered using your own customized html and javascript. Each item can be selected to display details of that item.
  • Spreadsheet -- as a tabular view reminiscent of a Microsoft Excel Spreadsheet.
  • Map -- as a map displaying data points which can be selected to display details of data associated with that data point.

The custom home screen has 5 tabs. Select the 'Tea' tab and click 'Launch Demo'. This will display another screen with 3 buttons. Note that all of these screens are served directly off of the device -- there is no network access, and these are fully able to function in Airplane mode -- without a Wi-Fi or internet connection. When you design your applications, you can either have them operate without any network access, or you can write them to access data on the internet. This becomes your design choice, not ODK's.

Click on the 'View Tea Houses' button.

 

List View

You are currently looking at a List view of the Tea Houses table. This view is designed entirely in Javascript and HTML, and we’ve customized it for the Tea Houses table. Click on the lined paper icon at the top of the screen. Here you’ll see all the possible view types. Select “Spreadsheet”.

 

Spreadsheet View

This takes you to a familiar view as if you were looking at it on a spreadsheet. Each row here represents a tea house, and each was a row in the list view. The thin column on the left is called the “status column”; if you downloaded the data from the server and have not modified the row, this will show as white (clear). If you have modified the row, it will show as yellow, if you have added an entirely-new row, it will display as green, and if you have deleted the row it will display as black until you sync with the server and publish those changes. Select the lined paper icon again, and select “Map”.

 

Map View

All the fictional tea houses in Benin appear on the map. Pinch and squeeze or widen to zoom out and in, respectively. The tea house location is plotted based on what appeared in the “Location_latitude" and "Location_longitude” columns in the Spreadsheet view. When you click on a map marker, the list view will redraw with that marker's information at the top of the list view. Click on an entry and you will be taken to a Detail view.

 

Detail View

The detail view is a way to visualize a single row in a table--in this case a single tea house. You can scroll down to see the information we decided to display. Like the list view, we programmed this using very rudimentary HTML and Javascript, but it could be customized to look fancier or display additional information.

Scroll to the bottom and you’ll see a link as a number of teas. This is using the information in the table called “Tea Inventory” to tell you how many teas this tea house offers, and has also been defined in the Javascript. Click on the link. You’re now in a List view for the Tea Inventory table, displaying only those teas offered at this tea house. If you select one of these teas, you’ll see specific information about the tea, including whether it is available hot, iced, in bags, or loose leaf. Note that the tea type is being pulled from the Tea Types table, but the Javascript is getting the information from that table to construct our view.

Hit the device’s back button twice to get back to the Detail view for the tea house.

At the top of the screen you will see a pencil icon. If an ODK Survey form were defined for this table, you can click on this to open ODK Survey to edit the row using that form. Try this now with Tea Houses.

A more complex example of this same flow is in the Home demo. Back out of the Tea Houses demo, back to the custom home screen and select the Hope demo and launch it. Choose Screen Female Client. This launches an ODK Survey form for entering a new client into the Hope study. Either complete an entry or back out of that form and choose to Ignore Changes to leave ODK Survey without adding a new client. Choose Follow Up with Existing Client to see a list view of clients who have already been entered into the study. Choosing one of these displays a detail view that allows you to access client or partner forms for that individual. You can also click on the pencil icon at the top of that detail view screen to launch an ODK Survey form within-which you can view or change any of the information for that client (clicking on the top right button with the form name opens a drop-down menu from which you can choose Contents to see a summary of all the form's fields and their values).

 

Graph View

List views can use Javascript packages like d3 to render data graphically. Back out of the Hope demo and launch the Plot demo, choose View Plots, and choose Puerto Madero to see a bar graph of corn crop heights across different visits to this farm. That graph was rendered using d3. That library can render scatter plots, line graphs, graphs with error bars and many other visualizations.

 

Non-form-based data entry

Finally, back out of the Plot demo and choose JGI.

The JGI (Jane Goodall Institute) demo app is a portion of a chimpanzee interaction tracking app that field researchers can use to record the activities of a designated ("followed") chimp and its interactions with nearby chimpanzees at 15-minute intervals.

Choose New Follow. Fill in the form fields, and click Begin. And begin recording interactions of this chimp with other known chimps in that group. These fields are all filled-in using hand-written HTML -- not ODK Survey. ODK Survey would be too scripted and confining for this type of dynamic interaction record.

 

Another Map example: Geo Tagger

Another example demo app, Geotagger, is also included in the sample application. It contains information and pictures from various places around Seattle. The HTML and Javascript files associated with this table are slightly more sophisticated, and will give you an idea of the customization you can achieve using Tables.

From the custom home screen, click on the 'Geo' tab, and click on the 'Launch Demo' button.

This directly opens the Geotagger dataset in the Map view. The data represent several places around Seattle. Click on “Phinney Ridge”, and the item will expand to give you more information. This more sophisticated behavior is all performed in the Javascript and HTML file, which you can find in config/tables/geotagger/html/geo_list.html as well as config/tables/geotagger/js/geo_list.js.

Click on the picture and you’ll be taken to a Detail view of the Phinney Ridge entry. This Detail view is also fancier than those in the Tea Time in Benin example. This file is located in config/tables/geotagger/html/geo_detail.html.

Press the device’s back button to go back to the map view. We’re going to add an entry for your current location. Press the plus icon at the top of the screen and you’ll be taken to ODK Survey.

Fill out the form, and at the last screen of the form and press “Finalize”. You’ll now see your new entry in the list. Navigate to the Detail view and you’ll see it works there as well. If you go back to List view and change to Spreadsheet view, you’ll see it there as well.

 

Adding Your Own Tables

The creation of data tables is handled within the App Designer. ODK Tables can display and present data, but no longer has the ability to create tables on the fly. This enables the ODK Services application to enforce that the configuration of the device (its tables, html files, etc.) are identical to those on the server. If we were to allow new tables to be created on the device, the synchronization logic would get much more complicated.

 

Initialize from ODK Application Designer

See the Application Designer documentation for how to create a form to describe a new data table.

 

Import Data into a Table from a CSV

This section assumes you have already created the table into which you are importing data.

Once you have created the table, you can load data from a CSV into it by choosing the plus ('+') icon on the Table Manager screen.

A CSV is a comma-separated values file. It is a common way to transport tabular data between different programs. Microsoft Excel can save and open CSV files, as can Open Office and a variety of other programs. Tables expects a certain format of the data in order to import the data correctly: the first line must be the comma-separated list of column names. The remaining lines must be the data for each of the corresponding columns.

For example, assume you wanted to load data into table of people's names, with column (field) names of 'Name' and 'Age'. In addition to those columns, your CSV file must also specify the unique row id (instance id) for each data row (the _id column); you can also specify the creator of the row, the time of creation, and other information. But, at a minimum, the file should look like:

_id,Name,Age
myUniqueIdforSam,Sam,27

This can be achieved by creating a spreadsheet in a spreadsheet editor and saving it as a CSV, or by copying the above text into a text editor and saving it with a .csv extension.

The upload process is as follows:

  1. Place the CSV file onto the device and place it in the config/assets/csv/ directory with a filename of tableid.csv. For example, /sdcard/opendatakit/default/config/assets/csv/people.csv would be the CSV file for the 'people' table.
  2. Launch ODK Tables and navigate to the Table Manager screen
  3. Press the plus ('+') button at the top of the Table Manager screen,
  4. Press “Select CSV File to Import” (note that you must have installed OI File Manager from the Play Store in order for this to work).
  5. Find your file, select it, and press “Pick file”.
  6. Press "Append to an Existing Table"

The data will be read from the file and appended to your data table.

IMPORTANT NOTE: Prior to any deployment, you should sync your device to your server and export the data table and copy the exported CSV file back on top of the simple csv file that you created above. This ensures that the additional fields required by the ODK tools are properly populated and that a server-managed revision number is added to the data rows so that all devices will have the same internal ids for all of your data rows. This eliminates the possibility of the tables.init mechanism introducing duplicate records and speeds the sync process and minimizes the occurrence of conflicts across the devices when these devices first sync to the server.

Specifying the values for the _id column is important. Otherwise, each device, when it loads the CSV file, would assign different unique ids for each of the rows, causing much duplication and confusion (there would be a many Sam data records, one for each device).

 

Exporting Tables to CSV

You can export any of your tables to a CSV file and associated supporting files. These files will be written to the output/csv directory.

A Tables-exported CSV includes all the metadata needed to allow the table to be imported with exactly the same status settings, file associations and metadata settings on another device. Exporting produces 3 files:

  • tableid.definition.csv -- this defines the data table's structure. It specifies the columns and their column types and is a copy of the file found under config/tables/tableid/
  • tableid.properties.csv -- this defines the column heading names, translations, and the html files associated with list views, detail views, map views, etc. and is a copy of the file found under config/tables/tableid/
  • tableid.csv -- this holds the data file that you can import to recreate the contents of your data table
  • tableid -- this holds an instances folder that holds folders named after each row id (the row id is cleaned up to remove any invalid filename characters such as slashes and colons). Each of those folders contains the row-level attachments for that row id.

To export a table:

  1. Launch ODK Tables and navigate to the Table Manager screen
  2. Press the arrow ('->') icon at the top of the Table Manager screen.
  3. Select the table you want to export.
  4. Optionally specify a qualifier that will be inserted into the filenames of the emitted files before the .csv extension.
  5. Press “Export”

For example, if you were to export the geotagger table and specified 'demo' as a qualifier, the following files would be written:

  • output/csv/geotagger.demo.definition.csv
  • output/csv/geotagger.demo.properties.csv
  • output/csv/geotagger.demo.csv/geotagger.demo.csv
  • output/csv/geotagger/instances/1f9e.../137...jpg
  • output/csv/geotagger/instances/...

 

Configuring an App at Startup

If you are installing Tables on a new device and don’t have a server set up from which to pull the data (see the advanced section about syncing), you can alternatively configure Tables to import data at startup. This is useful during forms development, as you can simply push the form definitions, HTML and Javascript for your application data down to the phone from your computer and launch ODK Tables, and it will load data from csv files into your data tables.

The configuration file must be titled tables.init and placed in the /sdcard/odk/tables/config/assets directory. Below is the complete contents of the tables.init file distributed with the sample application:

table_keys=teaHouses, teaTypes, teaInventory, teaHousesEditable, geotagger, plot, plotVisits, femaleClients, maleClients, geopoints, follow
teaHouses.filename=config/assets/csv/Tea_houses.updated.csv
teaTypes.filename=config/assets/csv/Tea_types.updated.csv
teaInventory.filename=config/assets/csv/Tea_inventory.updated.csv
teaHousesEditable.filename=config/assets/csv/Tea_houses_editable.updated.csv
geotagger.filename=config/assets/csv/geotagger.updated.csv
plotVisits.filename=config/assets/csv/visit.example.csv
plot.filename=config/assets/csv/plot.example.csv
femaleClients.filename=config/assets/csv/femaleClients.allfields.csv
maleClients.filename=config/assets/csv/maleClients.allfields.csv
geopoints.filename=config/assets/csv/geopoints.allfields.csv
follow.filename=config/assets/csv/follow.updated.csv

The table_keys key contains a comma and space separated list of table keys. Each table key can then have a .filename that indicate, unsurprisingly, the filename of the csv data that should be imported; this file should be under the config/assets/csv directory and the name should begin with the tableId, followed by an optional qualifier (e.g., allfields), and end with .csv. If there are row-level file attachments for the table, they would be placed in a tableid file within the csv directory. Each row-level file attachment filename is relative to the folder for that row's id. If the rows _id column was myUniqueIdForSam, then the filenames in the data table for row-level attachments for that row would be relative to /sdcard/opendatakit/default/config/assets/csv/tableId/instances/myUniqueIdForSam/.

Any table ids appearing in this file must already have their table definitions and metadata values defined in the definition.csv and properties.csv files within their corresponding config/tables/tableId directory.

As mentioned earlier, only one attempt is made to read and import data at start-up. If that attempt fails, some or all tables may not be initialized or may be partially initialized. You can trigger a re-processing of this file by going to Settings and clicking Reset configuration then exiting the ODK tool and re-opening it.

Additionally, as mentioned earlier, this file is never uploaded to the server. After you have created your user application and loaded data onto your device using this mechanism, resetting the app server will push all the configuration files and all of data (i.e., the data rows loaded by the tables.init script) up to the server (except for this tables.init file). Other devices that synchronize with the server will retrieve all of those data rows during the data-row synchronization phase. There is no need for the devices that synchronize with the server to have a copy of the tables.init file and independently perform these actions.

 

Syncing--Advanced

The final thing you might like to try is synchronizing data to the cloud. See the instructions here.

 

References

ODK Tables Paper

ODK 2.0 Paper