This article covers the following topics:
- Introduction to database lookup
- Using remote lookup (for Online databases)
- Using local lookup (for Offline databases)
Introduction to database lookup
Using database lookup, your survey can search for a user-defined field in a database that is hosted on the dashboard. This field, called the key, could be the respondent's answer to a survey question, a scanned barcode, a sample field, or more.
QMob supports both online and offline databases; offline databases are stored on the server but downloaded by the app with the survey content, while online databases remain on the server and require an internet connection to be accessed. Online databases can be queried using remote lookup, while offline databases can be accessed using local lookup.
To upload a database to your survey:
- On the create page, select upload db.
- Click choose file, and find your database csv.
- If using local lookup, check the box next to offline database.
- Click import.
Databases must be in csv format. The first column must always be named 'id'. Any column name in the csv file must not contain any empty spaces and it is recommended to use only lower case characters. For example a column called 'Product Name' is not allowed, but 'product_name' is allowed. The filename of the database must start with a letter (having a number at the beginning is not supported).
Using remote lookup (for Online databases)
Remote lookup can be used as actions in conditions. To do so, use the following xml function:
New: db_read(db_name,key,var1,var2,...)
Old: barcode_lookup(db_name,key,var1,var2,...)
| Parameter | Description |
| db_name | The file name of the uploaded database. Do not include the .csv extension in this parameter. |
| key | This is the field to be searched in the database. This can be a question answer, survey variable, sample field, etc... |
| var | The column header of the value to be returned by the lookup. Multiple returns can be specified for each lookup key, separated by commas. These return variables will automatically be created as survey variables with the same name as the column header. |
In addition to creating survey variables with the returns of the lookup, this function will also create variables in the format r_var that return the result of the lookup.
| r_var value | Description |
| 0 | The lookup is unsuccessful |
| 1 | The lookup is successful |
| 2 | The device is offline |
| 3 | The request timed out |
| 4 | The value (key) that is searched in the database begins with “2” and is not included in the database, or if it is a 14 digit value that begins with “02” and is not included in the database |
Please Note. db_read works in the same way as barcode_lookup, however, it prevents the app from caching previous values and repeats the look up every time. db_read is designed to work with db_write
Using local lookup (for Offline databases)
Local lookup can be used as actions in conditions. To do so, use the following xml function:
local_lookup(db_name,key,var1,var2,...)
| Parameter | Description |
| db_name | The file name of the uploaded database. Do not include the .csv extension in this parameter. |
| key | This is the field to be searched in the database. This can be a question answer, survey variable, sample field, etc... |
| var | The column header of the value to be returned by the lookup. Multiple returns can be specified for each lookup key, separated by commas. These return variables will automatically be created as survey variables with the same name as the column header. |
In addition to creating survey variables with the returns of the lookup, this function will also create variables in the format r_var that return the result of the lookup.
| r_var value | Description |
| 0 | The lookup is unsuccessful |
| 1 | The lookup is successful |
| 4 | The value (key) that is searched in the database begins with “2” and is not included in the database, or if it is a 14 digit value that begins with “02” and is not included in the database |