Tables - rev 126

The documentation on this page corresponds to rev 126 (February 2015) of the tools suite. See Older Versions for documentation on earlier releases of this suite.

This is an older version of our software.

The websites referenced in this documentation will generally have been updated to work with the newer software, and therefore may not function with this older release.

Where practical, we have provided zip files of the ApplicationDesigner environment used in creating those sites. See Survey and Tables Aux Files

Release Designation

ODK Tables rev 126 (February 2015) 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 edit data, revisiting existing data and syncing it with an ODK Aggregate instance in the cloud. Using Tables as your entry-point to data collection, you will be able to gather data using ODK Survey or ODK Collect, sync it to a server, and have other users download and edit the data on their own devices.

We are including two different sample applications 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 some 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 126 of the ODK Tables APK from here. Click on the ODK Tables rev 126 APK link on that page and download the actual ODK Tables rev 126 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

The ODK Tables sample application requires

  • ODK Survey and
  • ODK Sync

Download and install these from the same downloads page (see above).

Install the following apps from Google's Play Store (these are not required, but are used by our demos):

  • ODK Collect
  • OI File Manager
  • Google Play Services (for maps)

IMPORTANT: Open ODK Collect and close it. This ensures that the correct directories exist on the phone. If you fail to do this, then you will get strange errors reported from ODK Tables when you try to edit a data row (by default, ODK Tables uses ODK Collect for ad hoc edits of a data row; it can use either ODK Survey or ODK Collect when a predefined form is specified).

USAGE NOTE: The first time ODK Tables launches ODK Survey, ODK Survey will initialize itself by unzipping the javascript libraries it uses to render forms. After this initialization is complete, ODK Survey will display a blank white screen until you either turn the device to force an orientation change or back out of ODK Survey and re-launch it from ODK Tables.

 

Install the Sample Applications

There are two sample applications for this release. The first consists of a collection of 4 demo apps, and the second consists of just the Geotagger application, with a walk-through of how to modify that application to add an additional data field.

The first sample application consists of 4 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.
  3. Plot - a fictional agricultural field pest- and yield- assessment application.
  4. Geotagger - a simple geotagging application.

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 ODK Sync mechanism will delete 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 the first sample application and its 4 demo apps,

  1. Launch ODK Survey
  2. Click on the cloud icon to launch ODK Sync from within ODK Survey
  3. Specify https://opendatakit-tablesdemo.appspot.com as the server URL (note the https).
  4. Select any user account associated with the device (if you have not configured one, open your device's Settings app, scroll down to the Accounts heading, and add an account (this is different from the accounts you can set up under Users).
  5. Click on 'Save Settings'
  6. Click on 'Authorize Account'
  7. Authorize access to the account's e-mail address (this is an acceptance screen presented by the Android operating system).
  8. Click on 'Sync Now'

The sync process will now begin. As stated above, this synchronization mechanism forces the configuration of the device to exactly match that of the server. Any local data tables or forms that are not present on the server will be removed from your device.

Once the configuration of the device is an exact match to that of the server, the data within the data tables are synchronized.

When complete, back out of the ODK Sync application, returning to ODK Tables. If the authentication failed, re-try the sync (if you have a slow connection, it may take two or three tries before all the data is synchronized).

Click on the configuration settings menu item, and choose the Use Custom Home Screen checkbox.

Finally, to see that custom home screen, back out of ODK Tables and relaunch it. You should now see the custom home screen of this sample application:

This homescreen presents 4 tabs, one for each of the 4 demos. Select a tab, then click the 'Launch Demo' button to enter that sample application.

Layout of Application Files

NOTE: The directory structure will change in a future release. We will be isolating the application directories into config, data and system sub-directories so that it is clear what files are static and forced to be identical to those on the server (the config files), what files are supplied by the ODK 2.0 tools themselves and should not be touched by the user (the system files), and what files hold dynamic settings and data in the user's data tables (the data 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/survey -- default application name (directory) for ODK Survey
  • /sdcard/opendatakit/tables -- default application name (directory) for ODK Tables

Within these applications, the following directories are present:

  • assets -- contains files that initialize your data tables and define the overall appearance and behavior of your app. you will use to write a custom app. In the example we are going to look at, this means the app-wide css files, the app home screen, the configuration file, and the csv files that define the tables.
  • framework --contains files that Tables (and Survey, if you are using Tables with Survey) requires to run. You should not modify files in this folder unless you know what you're doing.
  • output -- contains files that are generated or exported by the ODK Tables app on the device.
  • tables -- contains your table-specific files. The convention in the tables directory is that it includes only directories that are named with table ids. The files that are specific to each table belong in their respective folder. The list view html file for the tea houses table is found in tables/Tea_houses/html/Tea_houses_list.html.

The automatic configuring and loading of data into ODK Tables is governed by the assets/config.properties file. It tells Tables the names of the data files it needs to import.

Assuming everything has worked, you can now start navigating the app. This is described in the Guided Tour section below

 

A Guided Tour

ODK Tables allows you to customize the app home screen. If you supply a custom home screen (assets/index.html), you will have the option of using this as the home screen of the app. This is what launched when you re-started ODK Tables after downloading the 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 Sync (the cloud icon), or change user settings.

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 with application-level settings. If the assets/index.html file exists, you will see a checkbox saying "Use custom home screen". Select this so that the box is checked, and then exit entirely out of the app (hit back until the app closes) and then open it again. You should now see the custom home screen.

Within ODK Tables, each data set can be viewed in four 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.
  • Graph -- as a graphical representation rendered using html, D3, and your own specifications
  • 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 4 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 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”, and is green to show you that the rows have not yet been synced to Aggregate. Hit the device’s back button to go back to the Spreadsheet view, 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. You can click on this to open ODK Collect to edit the row using a form that is generated on the fly. Here you can edit the row data as you might be familiar with in ODK Collect. (Because the data relates to other tables, if you change the “Tea House Id” field, you might lose some of the connections of the data.) If you defined one, you can also have ODK Tables open either an ODK Survey form or an ODK Collect form.

Swipe through the screens to the right to see if the tea house offers iced tea. If it says “Yes”, change it to “No”, or vice versa. Then navigate all the way to the end of the form and click “Save form and Exit”. The detail view will have been updated appropriately to save your change.

 

Graph View

NOTE: This remains under development and is non-operational at this time. This functionality may be moved to the App Designer, and graphs may simply be another form of HTML webpage that you can create in that tool.

 

Another example: Geo Tagger

Another example demo app, Geotagger, is also included in the sample application. It is unrelated to Tea Time in Benin, and 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 see in the unzipped alpha directory in /tables/geotagger/html/geo_list.html as well as /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 the unzipped alpha directory in /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 make sure the box is checked to mark the form as finalized, and press “Save Form and Exit”. 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

Beginning with this release, 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 Sync 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

Beginning with this release, the ODK Application Designer will automatically write the table definition and properties files if the formId matches the tableId of the form and you choose the "Save to FileSystem" option on the XLSXConverter tab.

 

Initialize from ODK Survey

If you define a form whose formId matches the tableId, the Application Designer now performs all of these actions and you no longer need to do these manual steps. See above.

To initialize a table from an ODK Survey form, place the form under the appropriate directory path in the ODK Tables directory (/sdcard/opendatakit/tables). E.g., if the form_id is household_new and the table_id is household, then you would place the formDef.json for the form here:

/sdcard/opendatakit/tables/household/forms/household_new/formDef.json

Next, Create a shortcut widget for ODK Survey to launch into the ODK Tables application directory. On Android 4.x devices:

  1. Choose to view the installed applications.
  2. Select the Widgets tab at the top of that screen.
  3. Navigate through the available widgets, and select and hold the ODK Survey Form widget. Drag and drop it onto one of your Android launcher (home) screens.
  4. A list of available applications and forms will appear, in the form of application folder for applications, and application folder > form name for each form within an application. Choose tables. This will place an ODK Survey App shortcut to the tables application on your launcher (home) screen.

Now, click on that ODK Survey App shortcut to open ODK Survey in the context of the 'tables' (ODK Tables) application. Open each of the forms (you don't need to create any instances of the forms, just open each, waiting for it to render the initial screen, then exit). Close ODK Survey.

Now, Open ODK Tables and go to the Table Manager screen. Each of the forms you opened will have created its data table and made it available in ODK Tables.

Finally, from within the Table Manager, click the '->' and export the tables. This writes them to the output/csv directory under the table application (/sdcard/opendatakit/tables). The tableid.definition.csv and tableid.properties.csv files need to be copied back to the Application Designer, renamed and placed into the tables/tableid directory. See the instructions in the App Designer for details.

 

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, if you wanted to load data into table of people's names, with column (field) names of 'Name' and 'Age', your CSV file would look like:

Name,Age
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 /assets/csv/ directory with a filename of tableid.csv. For example, /sdcard/opendatakit/assets/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: If you want to distribute this table across many devices, you should then export the data table and use the exported CSV file for that purpose. This ensures that all devices will have the same internal ids for all of your data rows. Otherwise, each device, when it loads the CSV file, would assign different unique ids for each of the rows, causing much duplication and confusion.

 

Exporting Tables to CSV

NOTE: We currently do not export media attachments along with the CSV files. The media attachments are under the tables/tableid/instances directory and must be manually copied in order to be retained.

You can export any of your tables to a CSV. 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 specifie the columns and their column types
  • tableid.properties.csv -- this defines the column heading names, translations, and the html files associated with list views, detail views, map views, etc.
  • tableid.csv -- this holds the data file that you can import to recreate the contents of your data file

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”

The CSV file will be written to the output/csv directory.

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

In future releases, we are likely to also copy the media attachments of the form submissions into the output/csv/geotagger.demo.csv/ directory so that users only need to copy the output directory off of the device. This is not currently done.

 

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/assets directory. Below is a shortened version of the tables.init file distributed with the sample application:

table_keys=teaHouses, teaTypes, teaInventory, teaHousesEditable, geotagger, 
   householdmember, householdsurvey, plot, plotVisits, 
   femaleClients, maleClients, geopoints, follow
teaHouses.filename=assets/csv/Tea_houses.updated.csv
teaTypes.filename=assets/csv/Tea_types.updated.csv
teaInventory.filename=assets/csv/Tea_inventory.updated.csv
teaHousesEditable.filename=assets/csv/Tea_houses_editable.updated.csv
geotagger.filename=assets/csv/geotagger.updated.csv
householdmember.filename=assets/csv/household_member.updated.csv
householdsurvey.filename=assets/csv/household.updated.csv
plotVisits.filename=assets/csv/visit.example.csv
plot.filename=assets/csv/plot.example.csv
femaleClients.filename=assets/csv/femaleClients.allfields.csv
maleClients.filename=assets/csv/maleClients.allfields.csv
geopoints.filename=assets/csv/geopoints.allfields.csv
follow.filename=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 assets/csv directory and the name should begin with the tableId, followed by an optional qualifier (e.g., allfields), and end with .csv. Each filename is relative to the application directory on the device, e.g., /sdcard/opendatakit/tables/. These tables must already have their table definitions and metadata values defined in the definition.csv and properties.csv files within their corresponding tables/tableId directory.

Tables attempts to import based on the tables.init file on startup.

 

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