Tables - rev 006

This is an Alpha release. It should never be used for a production deployment. There will be significant changes in subsequent releases. Migration paths for content or data collected using an Alpha or Beta release are not supported.

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

Alpha and Beta releases are intended to show users where we are with the process, and to get feedback on what works and what needs work. Alpha releases are more likely to Force Close than Beta releases, which are more likely to fail than Release Candidates. Release Candidates are suitable for production deployments and will have well-defined migration paths for content and data.

What is ODK Tables?

Tables is a program that allows you to visualize and edit data, revisiting existing data and syncing it up to an ODK Aggregate instance in the cloud. Using Tables as your entry-point to data collection, you will be able to gather data using Collect (and eventually Survey), sync it to a server, and have other users download and edit the data on their own devices.

Installation

Tables is currently in alpha. (See the scary note at the top of this page, and don’t use it with data you cannot afford to lose.) For this reason you can’t download it from the Play Store as you might be used to. Instead, you have to follow these instructions.

We are including a sample installation 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.

Install the APK

The “APK” is the actual Tables App. It is available on the Downloads page. Open the browser on your device, browse to the above URL, select to download the ODK Tables APK, choose the Alpha-1 APK, and download it.

After downloading, ensure that the 'Unknown Sources' checkbox is checked on your device's Settings/Applications screen. Then view the Downloads, select the ODK Tables APK there, and choose to install it. Depending on your device, you might also have to rename the file to end in '.apk'.

Install Supporting Applications

Install the following apps from Google's Play Store:

  • ODK Collect
  • OI File Manager

Open ODK Collect and close it. Open ODK Tables and close it. This ensures that the correct directories exist on the phone.

Install the Sample Data

The sample data includes a number of tables about fictional teahouses in Benin. If you like fictional tea, this is the app for you. Download the ODK Tables ALPHA rev 6 Demo Files zip from the Downloads page and unzip it. There are three folders in the resulting directory: tables, forms, and formTemplates.

First navigate to the tables directory. You need to put all of the files in this folder into the /sdcard/odk/tables directory on your Android device. (Be sure that you’ve opened ODK Tables once already, or else this folder won’t exist!) There are directories in this folder as well--these also need to go into /sdcard/odk/tables/. On Windows, you can connect your device via USB, elect to mount it as a Media device, and drag-and-drop these files over to it. On MacOSX, you can use https://www.android.com/filetransfer/ to do the same. Otherwise, if you’re using adb, you can do this by navigating in a terminal to the unzipped directory and typing:

adb push ./tables/ /sdcard/odk/tables/

Next put all of the ODK Collect form definition files in “forms” into the /sdcard/odk/forms directory on the device (Be sure that you’ve opened ODK Collect once already, or else this folder won’t exist!). If you’re using adb, you can do this by navigating in a terminal to the unzipped directory and typing:

adb push ./forms/ /sdcard/odk/forms/

Open ODK Collect and press “Fill Blank Form”. This will allow ODK Collect to find the forms you’ve just added, and you should see the six new forms in its list of available forms.

Import the Data

The data is on the device, and now we need to get it into Tables. One of the files you pushed into /sdcard/odk/tables/ was a configuration file called config.properties. This tells Tables the names of the files it needs to import. Open Tables, and you’ll see a dialog telling you to wait as it imports the files.

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

A Guided Tour

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.

ODK Tables also allows you to customize its homescreen. If you specify a custom screen (homescreen.html), it will be displayed when ODK Tables is first started.

In our demo, we supply a custom home screen which displays a “Tea Time in Benin” menu upon opening ODK Tables. This custom homescreen is in the unzipped demo directory at /tables/homescreen.html. You can use the buttons on the home screen to open the various tables and see the tea houses and the tea they offer in Benin.

The other main entry point is the list of all the tables in the app, also known as the Table Manager. If there is no custom homescreen, the Table Manager will be shown. If there is a custom homescreen (as is the case in our demo), you can reach the Table Manager by clicking on the icon with three lines at the top of the “Tea Time in Benin” screen.

To begin the tour, open ODK Tables and click 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 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 the cloud. Scroll to the right to see all of the columns in the table. The last column is called “GeoPoint”. If you double click (alternatively you can long press) on the column heading, a menu will appear. Click on “Manage Column Properties”, and you’ll see that the column “Type” has been set to “Location”. This means we can view these data points in a Map view. 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 zoom out to see them. Their location is plotted based on what appeared in the “GeoPoint” column in the Spreadsheet view. If you click on a datapoint you will see a List view entry for that point. 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. Click it to open ODK Collect to edit the row using a form we installed to the phone. 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.)

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

The final view type is the Graph view, which we leave up to you to explore. This is still under development and will become more complete with future releases. To reach the Graph view, press the lined paper icon at the top of the screen when you are viewing a table and select Graph. You will be taken to a Graph Manager screen. Here you can press the plus icon to add a graph or revisit previously saved graphs.

Searching

Tables also supports some rudimentary searching of data. Navigate back to the Spreadsheet view of the Tea Houses table. We want to see only those tea houses in the District known as “St John’s”.

Press the magnifying class at the top of the screen. A search box will appear. Double click in one of the cells in the “District” column that says “St John’s”. A pop-up will appear, which you can drag into the search box. When the search box changes color, lift your finger and the text “District:St John’s” will appear in the search box. This tells Tables that you want to look for Tea Houses in the St John’s district.

Press the search button to the right of the search box. The data in the spreadsheet will change to show only those tea houses in St John’s.

More advanced searching is supported through the javascript API available to the List and Detail views, and additional search functionality will be included in future releases.

Another example: Geo Tagger

Another example table, Geo Tagger v3, is also included in the data package. 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 Tea Time in Benin home screen, press the icon with the three lines in the top right corner. This is the Table Manager, and lets you examine all the tables that have been imported to the app.

Open Geo Tagger v3. You’ll be taken to a more sophisticated List view. This table represents 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/tables/geotagger_v3/geo_list.html.

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/tables/geotagger_v3/geo_detail.html.

Press the device’s back button to go back to the list 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 Collect. Note that a custom Geo Tagger form has not been included in the package. The types of the columns in Geo Tagger v3 have been set correctly, and Tables auto-generates the form for you.

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

And, of course, you can also manage your own data in ODK Tables. There are two ways to do this. You can import a CSV, or you can construct the tables up from scratch. In either case, start by going to the Table Manager by pressing the icon with three lines at the top right of the Tea Time in Benin home screen. Then press the plus button at the top of the screen.

Adding a Table from Scratch

To add a table without a CSV, press “Add New Data Table” and enter the name of your table. Select that table from the list, and you’ll see that it has no data. Get to the Column Manager screen by pressing the gear icon at the top right of the screen and selecting “Column Manager”.

Press the plus button at the top right of the screen and enter the name of your column. You’ll then be taken to a screen where you can manage the properties of the column. Press the device’s back button to return to the Column Manager. When you’ve added all the columns you need, press the device’s back button from the Column Manager to be taken to the table. You can now press the plus button at the top of the screen to launch collect and add a new row. (Note that, as with all interactions between Collect and Tables, your changes will only be reflected if you save the data by marking the form as finalized and pressing the “Save Form and Exit” button at the rightmost screen of Collect.

Adding a Table from a CSV

A CSV is a comma-separated values file. It is a common way to transport spreadsheet 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 add a table of the name and age of various people, 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.

Push the CSV to the phone. Standard practice is to keep the CSVs either in the /sdcard/odk/tables directory, or in a directory for each new project. For example, /sdcard/odk/tables/people would be a directory where we could put the CSV we just created.

Once the file is on the phone, press the plus button at the top of the Table Manager screen and select “File Import/Export”, then 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). Find your file, select it, and press “Pick file”. Enter the name of the table and press “Import New Table”. If your CSV was formatted correctly, you can now navigate to the new table in the Table Manager.

Exporting Tables to CSV

You can also use Tables to export your tables to a CSV. A Tables-exported CSV can include metadata that will allow the table to be imported exactly the same on another device. This allows you to configure a table with column types, List and Detail view files, and copy those CSVs to another table, and import the file to have the identical table on the other device.

To export a table, press the plus icon at top of the Table Manager screen and select “File Import/Export”. Press the “EXPORT CSV” tab and select the table you want to export. Check all the boxes to include all the metadata, and press “Select CSV File to Export”. Enter the name of your output file and press “Pick file”. (Note that if you do not use OI File Manager to add the file, you might try to write to a directory to which you do not have permission and the export may fail.) Once you have picked the export file, press “Export” to export the table to CSV.

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 will be the most practical approach to setting up phones with data during the alpha release, as you will need access to the phone to push the CSV, HTML and javascript files necessary to use Tables. In future releases, all content files (such as collected images), as well as HTML and javascript forms will be pulled from the server during sync. For now, however, the files must be added to the phone manually.

The configuration file must be titled config.properties and placed in the /sdcard/odk/tables/ directory. Below is a shortened version of the config.properties file distributed with the alpha to serve as an example:

table_keys=teaHouses, teaType,
teaType.tablename=Tea Type
teaType.filename=beninTeaTypesWithMD.csv
teaHouses.tablename=Tea House
teaHouses.filename=beninTeaHousesWithMD>.csv

The table_keys key contains a comma and space separated list of table keys. Each table key can then have a .tablename and .filename that indicate, unsurprisingly, the table name and filename of the tables that should be imported. The teaHouses.tablename key, for example, indicates that the imported table should be called “Tea House”. The filename from which to import the data, meanwhile, is indicated by teaHouses.filename and is beninTeaHousesWithMD.csv. Each filename is relative to the /sdcard/odk/tables/ directory on the device. Note that if the imported CSV was exported using Tables and contains metadata, such as the table name, the .tablename key will be ignored.

Tables attempts to import based on the config.properties file on startup, and only if the config.properties file has been updated since the time of last import.

Configuring a Custom App

A powerful feature of ODK Tables is the ability to create custom apps using HTML and javascript. Tables provides a javascript API against which pages are designed, giving designers the ability to access the data in Tables and guide users through a specific workflow. Tea Time in Benin does not make use of much of this functionality, so we have also included files for a sample app called CKD Study. CKD stands for chronic kidney disease, and will highlight the capability of Tables to function as a workflow guide in complex medical applications.

Toward this end, the example uses things like the ability to launch Collect to add data using a specific form, and relies on more complicated logic in the html and javascript. The names of the files used in the tutorial are mentioned as they are used. These files are located in the tables folder of the unzipped directory and include comments that explain the API calls they are using.

Additional ODK Tables 2.0 ALPHA-1 Web File Templates with comments can be found at the Downloads page.

Overview

The "CKD Study" example outlines the process of designing a Tables application for a longitudinal study in which patients will be followed for a given amount of time. The user will be able to screen patients for eligibility, follow-up with the study participants and view lab results and other collected data. The application launches specified ODK Collect forms to capture patient data. Multiple tables are used in this example to illustrate how data can be shared and accessed across tables.

The CKD Study app requires three tables: Patients, Patients Follow-Up, and Lab Results. These tables were included in the config.properties file and were therefore imported at startup.

User-defined Collect Forms

Three custom forms are used in this example, which were pushed to the device at the beginning of the walk-through:

  • screeningForm
  • labResults
  • sixMonthFollowUp

Note:

  • flat is set to true in the XLSForm settings sheet
  • All variable names must match the original import name of the column in the CSV, and must be prepended with an underscore. For example, if the column name was age in the CSV, the corresponding element in the form must be _age. This construct allows forms to continue to function even if display names change.

Home Screen

We need to first change to the CKD home screen. Tables looks for a file called homescreen.html in the /sdcard/odk/tables directory and launches the home screen using that file. If homescreen.html is not present, it instead launches immediately to the Table Manager screen. The CKD home screen is in the unzipped directory as homescreens/CKDHomescreen.html. Copy it to /sdcard/odk/tables as homescreen.html, then open Tables.

The homescreen offers three options: "Screen New Patient", "Follow Up with Existing Patient", and "View Lab Results". These options were chosen because they are the most common use-cases for a worker in the CKD study.

Selecting "Screen New Patient" will launch the "patientScreening" form in Collect, and is being specified in the javascript of the CKD homescreen.html file. Once the form is saved and finalized, the responses will update in the "Patients" table. By selecting "Follow Up with Existing Patient", you can see the list view of all screened patients. The list view file is in the tables directory as patients_list.html.

When you select on a specific list item, a detail view will be launched to display further information about the patient. This file is located in the tables directory as patients_detail.html. The user can click the "Six Months Follow-Up Form" button to launch a follow-up form from the detail view. The follow-up form will be prepopulated with the patient id number that was provided at the time the patient was screened. The saved and finalized responses from this form will be added to the "Patients Follow-Up" table.

Additionally, the user can select "Lab Results Form" to launch a Collect form that captures laboratory results. The form is prepopulated with the patient id value and the saved results will update to the "Lab Results" table. To view the responses from the Lab Results form, you can navigate back to the home screen and click on the "View Lab Results" button. This action will open up a list view with the file located in the tables directory as lab_list_chunked.html. The user can select a list item to view more detailed data about the patient’s lab results.

Syncing--Advanced

The final thing you might like to try is synchronizing data to the cloud. First you’ll have to install ODK Aggregate v1.3.2 to a server (see the Aggregate page for these instructions).

  • Log onto your ODK Aggregate v1.3.2 instance.
  • Go to the Site Admin / Preferences page.
  • Check the checkbox for "ODK Tables Synchronization Functionality"

Once you sync data to the server, it will show as a table under the top-level "ODK Tables" tab.

Authentication and authorization of ODK Tables communications are disabled for this ODK Aggregate release; the import-from-CSV functionality is incomplete.

In this example we will sync only the Tea Houses table. Go to the list of tables by clicking on the icon with three lines at the top of the “Tea Time in Benin” screen. Click on the gear next to the “Tea Houses” table, and then select “Edit Table Properties”. Scroll to the bottom and select “Save Current to Defaults” (confirm with “OK”), then select “Copy Defaults to Server Sync Set” (again confirming with “OK”), and then hit the device’s back button.

This option is included because it allows you to store different settings for different purposes. Only those tables with entries in the Server Sync Set may be synced, and only those settings in the Server Sync Set are pushed to the cloud.

At the top of the screen, click the icon with two arrows forming a circle. This will take you to a sync screen. Enter the IP address (and port, if necessary) of the server running your Aggregate instance, and select the account you would like to use to sync.

Press “Save settings”, and then press “Authorize account”. You can now press “Choose set of tables to sync”. In the list of tables to sync, only “Tea Houses” should appear. (This is because you saved the settings to the sync server set in the table properties menu.) Press “Tea Houses” to select that you want to sync the table.

Hit the device’s back button, and press “Sync now”. Tables will contact Aggregate and attempt to add the table to the server. You will be notified of the results. If it was successful, you can now point to the same instance with another device running Tables, save the settings and authorize the account, and press “Download table from server”. You should see “_Tea_Houses”. Pressing on this table name will download the
table. Changes to the table will be synchronized between devices when future syncs are requested.

Note that if two users alter the same row, the second person to sync will not be able to push their changes. This is because the server doesn’t know which user’s row to trust. The status column for this row will be marked red to show conflict, and the row will no longer be synced from that device. In future releases Tables will support resolving these conflicts, but not in the alpha.

References

ODK Tables Paper

ODK 2.0 Paper