Question Type Table Lookup Form

From EpiHandy

Jump to: navigation, search

Contents

Description of TableLookUp question type in EpiHandy

The following is a description of the usage of the table lookup functionality in EpiHandy. This function allows users to use existing datasources without having to manually copy them one by one into the options of a question. In addition they allow users to setup a set of lists that are dependant of each other i.e. country, region, village and area so that only areas of a selected village in a selected region of a country is displayed.

Upto 5 nested levels are supported.

  • TODO: ADD IMAGES FROM MOBILE CLIENT
  • TODO: ADD IMAGES FROM STUDY MANAGER
  • TODO: UPLOAD SAMPLE XML FILES
  • TODO: UPLOAD DATA IMPORT TOOL

Quick overview of process

  1. Create question using "Table Lookup Form" type
  2. Create table lookup xml
  3. Create data syncronization xml
  4. Prepare existing data / import into server
  5. Test the xml and imported tables (in study designer)
  6. (if creating database tables manually, then:) Copy data synchronization xml to handheld computer
  7. Synchronize handheld computer

WARNING 1! - It is very important that the xml files are formatted exactly as stated.

WARNING 2! – The current version is not safe against sql injections, meaning if you put malicious sql code here it will have sufficient priveliges to cause problems. Also make sure none of the values contain reserved sql words i.e. SELECT, DELETE, UPDATE, etc. Reserved word as part of other words are OK, i.e. DeleteList is possible to use

Part 1 – Setup in study designer.

  1. Add a question with question type "Table Lookup"
  2. State your main question and in the XML defining table lookup items field you need to insert xml as described in detail below (see sample files at the end of this document).

Part 2 – Table Lookup XML specification

XML XML Comments
<?xml version="1.0" standalone="yes"?> XML Header, do not edit
<LOOKUPINFO> Main node encapsulating all dropdowns used
<ListItem> Node encapsulating all properties of a dropdown list
<ListNo>1</ListNo> The list order
<ListText>Please select COUNTRY</ListText> The text displayed above the dropdown list
<ListName>Country</ListName> The name of the list
<Query>Select Name, CountryID FROM CountryList</Query> The SQL query used to load the list
<DisplayMember>Name</DisplayMember> The column used to display an item in the list Must be part of the query (named or by using \* for all columns)
<ValueMember>CountryID</ValueMember> The column used as the stored value in the list. Must be part of the query (named or by using \* for all columns)
<IsSubQuery>False</IsSubQuery> Defines whether this list depends on the value of another list. The first list must be set to False, otherwise use True
<UpdateAfter>0</UpdateAfter> Defines after which list this list should be updated (loading its data)
<IsResult>False</IsResult> Defines which list should be used for display of the result.
</ListItem> End of ListItem node
<ListItem>
<ListNo>2</ListNo>
<ListText>Please select a REGION in the selected COUNTRY</ListText>
<ListName>Region</ListName>
<Query>Select Name, RegionID FROM RegionList WHERE CountryID = \’$\{\#\}$\’</Query> This query depends on the previous list and uses the selected value (ValueMember) to filter this list. The $\{\#\}$ is replaced with the selected value. I.e. if selected country is Norway, with code NO the query would be parsed to: Select Name, RegionID FROM RegionList WHERE CountryID = \’NO\’
<DisplayMember>Name</DisplayMember>
<ValueMember>RegionID</ValueMember>
<IsSubQuery>True</IsSubQuery>
<UpdateAfter>1</UpdateAfter>
<IsResult>True</IsResult> The DisplayMember of this list will be shown as the question result, but the ValueMember will be used as the stored value.
</ListItem>
</LOOKUPINFO> End of main node

Part 3. Setup of data from server to PDA.

''''There are two alternatives:

  1. Using the data import tool, all tables are automatically downloaded on each synch.
  2. Using an xml document as described below.

NOTE! Generally I strongly recomend to use the fully automated functions.

Data can be moved to the PDA from any RDA compliant data source using an xml file that must be copied manually to a specific folder as described below.

Defining XML used to copy data from desktop to handheld computer

This is an xml file that needs to be created and manually copied into \My Documents\ (or equivalent for other language editions) on the handheld computer. The file must be named RDASYNCH.xml.

<?xml version="1.0" standalone="yes"?> XML Header, do not edit
<RDA> Main node encapsulating all tables to be syncrhonized with / download from desktop
<RDAitem> Node encapsulating all properties of a table to be downloaded
<AlwaysUpdate>True</AlwaysUpdate> Setting this to True will update the list everytime a synchronization is performed, is needed for frequently updated lists. Setting to False will only download once.
<TableName>CountryList</TableName> The name of the downloaded copy of the table. I.e. a table can have separate names on the server and the handheld computer – Country and CountryList
<SELECT>SELECT \* From CountryList</SELECT> This is the actual SQL query used to select what data to download. WARNING! Ensure these statements are secure and does not contain malaicious code.
</RDAitem>
<RDAitem>
<AlwaysUpdate>True</AlwaysUpdate>
<TableName>RegionList</TableName>
<SELECT>SELECT \* From RegionList</SELECT>
</RDAitem>
</RDA>

Part 4. Preparing existing data / importing into the database

To this step there are two main options:
  1. Do it yourself using an existing database management tool i.e. MS Enterprise Manager, DTS, etc. Using these tools you can get highly customized data.
  2. Use the EpiHandy data import tool specifically designed for this purpose. This is a tool found on the properties of a question of type table lookup. The column length is maximum 255 characters.
    1. First select the file you want to import. This file has to be a TAB delimited file and should preferably have .txt as the extention/filetype.
    2. Type in the table name you want this file to have. NOTE to avoid interferance with EpiHandy system tables all of these tables names will have \’LookUp_\’ as a prefix. I.e. if you name your table Country, it will be named LookUp_Table in the database.
    3. The SQL Server and Database fields should be left as is unless otherwise instructed.
    4. Click on Import to start the import process.


5. Test the xml and imported tables (in study designer)

Once you have defined your xml and imported all referenced tables, you should test it in the study designer (Before synchronizing with a PDA!). Click on 
You will then be able to test it as it would work on the PDA, and you would be able to trap any errors etc in your xml. As well as see what data will be stored.

6. Sample XML:

Table Lookup XML

<?xml version="1.0" standalone="yes"?>
<LOOKUPINFO>
  <ListItem>
  <ListNo>1</ListNo>
  <ListText>Please select COUNTRY</ListText>
  <ListName>Country</ListName>
  <Query>Select Name, CountryID FROM CountryList</Query>
  <DisplayMember>Name</DisplayMember>
  <ValueMember>CountryID</ValueMember>
  <IsSubQuery>False</IsSubQuery>
  <UpdateAfter>0</UpdateAfter>
  <IsResult>False</IsResult>
  </ListItem>
<ListItem>
  <ListNo>2</ListNo>
  <ListText>Please select a REGION in the selected COUNTRY</ListText>
  <ListName>Region</ListName>
  <Query>Select Name, RegionID FROM RegionList WHERE CountryID = '${#}$'</Query>
  <DisplayMember>Name</DisplayMember>
  <ValueMember>RegionID</ValueMember>
  <IsSubQuery>True</IsSubQuery>
  <UpdateAfter>1</UpdateAfter>
  <IsResult>True</IsResult>
  </ListItem>
</LOOKUPINFO>

RDASYNCH.xml

<?xml version="1.0" standalone="yes"?>
<RDA>
  <RDAitem>
  <AlwaysUpdate>True</AlwaysUpdate>
  <TableName>CountryList</TableName>
  <SELECT>SELECT * From CountryList</SELECT>
  </RDAitem>
  <RDAitem>
  <AlwaysUpdate>True</AlwaysUpdate>
  <TableName>RegionList</TableName>
  <SELECT>SELECT * From RegionList</SELECT>
  </RDAitem>
</RDA>


Retrieved from "http://www.epihandy.org/index.php/Question_Type_Table_Lookup_Form"