Using Lookup Tables with Multiple Languages

For larger applications, you may need to have a lookup table that supports multiple languages.  Based on the language selected for the application, CommCare can be setup to get the correct language information from the table.  

Defining a Lookup Table that Support Multiple Languages

Please review Creating and Updating Lookup Tables to understand the basics of defining lookup tables.   Unfortunately, the current interface cannot be used to completely define a table that supports multiple languages.  Instead, we can upload an Excel document that sets up the table for us. 

1. Define a basic table that includes the information you'd like to store.  Here is an example of a table that stores States.  Note: I've set this table up so that its visible to all users (for simplicity).
   
2. Download the table from CommCareHQ to add language information. 
   

3. Modify the types sheet to identify which columns will have multiple languages.  This is done by adding an extra property on the field that will indicate the language.   This is highlighted in Red below. 

   Delete(Y/N)	table_id	is_global?	field 1	field 2	field 3	field 3 : property 1
   N	state	yes	population	id	name	lang

4. Modify the state sheet to add the list of states with each language.  We'll also need to modify this sheet's columns to include the additional language information.  You will need to add columns for each language supported.  The example below includes two languages, but you can add additional columns titled name: lang X and field: name X for more languages.  

   In the lang column, specify the language code used in CommCareHQ for the language.  You can find this by reviewing the language configuration page in CommCareHQ.  In this example, I've used English and Hindi.   (Note: For new rows, you can ignore the UID and Delete(Y/N) columns).  

   UID	Delete(Y/N)	field: population	field: id	name: lang 1	field: name 1	name: lang 2	field: name 2
   		1000	up	en	Uttar Pradesh	hin	उत्तर प्रदेश
   		1500	gujurat	en	Gujurat	hin	गुजरात
   		1583	bihar	en	Bihar	hin	बिहार

5. Upload the updated Excel file to CommCareHQ.  You can check "Replace Existing Tables" to replace any existing data for this table on CommCareHQ.  
   

6. You can review the uploaded data by clicking the View Table link next to the table on the page.  

Using Multiple Languages with Multiple Choice/Checkbox Questions

1. First we need to setup a label that contains the language codes that you want to use in your app.  In your form, add a label with the question ID of lang-code and label text that matches the language code for each language.  We'll also set a Display Condition of 1=2 on this to ensure that it doesn't appear in the form.  This label will be used to determine which language to display, based on what language is chosen. 
   
   
   
2. Now add a single or multiple answer lookup table question question to the form 
   
3. Choose the Lookup Table Data item
   1. Set the lookup table to the lookup table you uploaded (ex. state)
   2. Set the value field to the column in your table you want to store (ex. id)
   3. Set the label to the following: name[@lang = jr:itext('lang-code-label')] . You can replace name with the column that contains multiple languages. 

   
   
   

4. Update and Save your form.  Now the list of options should change their label based on what language is chosen for the application.  However, the data stored will remain the same.

Testing an application with lookup tables

In order to test the filtered choices and other logic works in the application, you must sign on as a mobile user and submit 'real' data to the server.  If you see an error when opening the form that states "Could not find an appropriate fixture for src: jr://fixture/item-list:table_name" you have not properly setup your lookup tables (make sure you did tap the blue button in the app menu "sync with server" before going into the form as this can give the same error message. When you sync, the updated lookup table(s) will be downloaded to your device). 

1. Sync your application. Some users have reported this error with large lookup tables that have not properly downloaded which choosing "Update Commcare" from the menu. Run the sync to ensure all assets are properly downloaded to the device
2. Make sure that the current user has lookup table rows assigned to them (Creating and Updating Lookup Tables) 
3. Alternatively, setup your lookup table so that it is available for all users (For the lookup table, set it up so that it is "Visible to All Users" by clicking Edit Type). 
4. Once you've done this, make sure you've synced your phone. 

If you run into issues testing your app with lookup tables, try to Clear user data. After you sync your phone if the lookup table still is not working, you may need to clear user data and log-in again. To do this, go to the login screen and click on Settings. After the Settings screen shows up, click the menu button, and select the first option, "Clear User Data." This will clear the username and force you to re-enter login credentials. Once you log in again, the new or edited table should be accessible to your user.

Training an application with lookup tables

1. Generic User: Create a generic user called "dimagi" or "demo" and update the table specifically for this user - populating all the conditional select options with the same 'dimagi' or 'demo'. As long as the partner is aware of the use case, they will be able to filter out this user in reports. During training, mobile users will sign onto CommCare and submit live 'practice' data. Work with the partner to specify a date where all data submitted to CommCare HQ is considered actual data for program purposes. This should be made clear to the FLWs and to the data analyst/M&E officer.
2. Training Application: Copy the final application - remove the conditional select options from the application. This app can be used during training as a training application and the users can enter data in demo mode.