Design details: hm.Survey

From hm.Survey
Jump to: navigation, search


  • The system is completely determined by schema.json which contains the table as well as layout definitions (including lookup values).
  • Generates the data entry screens (for both the browser and Android clients) on the fly using schema.json.
  • There is only one ‘open’ period at a time which will always be the most recent period. Survey data can be entered only for an open period which has been chosen as the current period (it is possible for a closed period to be the current period for which the data is read-only). You can close the existing period by adding a new period. Once the most recent period is closed, it cannot be opened again.
  • Tested for MySQL, Oracle and MS SQL Server (in principle it could be any relational database)


GPS functionality is enabled by setting the ‘register.gps’ flag to true. This triggers a number of changes in the underlying scripts and programs:

  • the schema generate scripts (,, create the two fields, ‘longitude’ and ‘latitude’, in the database
  • the browser and Android applications create two floating point fields in the schema for ‘longitude’ and ‘latitude’ which in turn trigger create the two fields on the screen corresponding to these two fields in the schema.
  • the Android application, in addition to the above, adds a button to ‘Get GPS Location’ that allows capture of GPS coordinates and save it to the longitude and latitude fields. This is done only if ‘register.handheld’ is set to ‘true’ (see ‘Android data entry’ section below)

Single/Multi Period Surveys

  • System can either be used either for:
    • multiple period surveys (schema.period is present, and current year is given by the first record in the ‘period’ table), or for
    • a one-off survey (schema.period is absent; survey year is given by the schema.application.year attribute; i.e., schema.application.year and schema.period cannot be present at the same time in schema.json).
  • Periods (which can be interpreted as visits) can be added only for multiple period surveys (i.e., if schema.period is present, for e.g., seasonal survey). Periods are stored in ‘period’ table with the fields:
    • year
    • period
    • status (closed=0, open=1 or ‘null’); closed indicates that data entry can no longer be done. Since the most recent period is always open (i.e. past periods cannot be reopened), this flag is redundant as it always possible to identify the open period as the most recent period in the period table (which might not be the currently selected period). If editing of past data is allowed, this flag becomes necessary (as the most recent period is not necessarily ‘open’).
  • The following operations are possible on periods (for multiple period surveys):
    • Select a chosen period as current. If the chosen period is the most recent (i.e. open for data entry), data entry is possible; otherwise data is read-only. The current period is the one chosen explicitly and not simply the one selected in the periods table.
    • Add a new period (the next one in sequence) thereby closing the current period (this cannot be undone). The new period will be automatically designated as open.

Table Structure

Register table

The ‘register’ table will have records that correspond to entities that represent each survey unit (e.g., households, establishments, farmers), and it will be keyed on an auto-incremented ‘id’ field (and year and period if it is a multiple period survey; see below). Key fields for the 'register' table are:

  • period: only if multiple-periods are allowed
  • id

An additional search_id text field (also unique) is also created to allow for quick searching of register (and survey) entries. A ‘name’ field should be present in the definition as it is the ‘name’ field which is displayed in the list of register entries. An ‘active’ field (1=Yes; 2=No) will be added by the system. Only active register entries will be added to the survey.

Section tables

Key fields for section tables are:

  • period: only if multiple periods are allowed
  • id (from register)
  • Section table names are of the form section where 'i' is the 1-based index in the schema.sections array (or they can alternatively be defined by the 'schema' attribute for each entry in the 'sections' array).

Parent-child relationships within register

Parent-child relationship within a register (for e.g. to maintain enterprise-establishment relationships) can be maintained using parent lookup with predefined but optional ‘parent_id’ field in the ‘register’ that looks up parent search_ids and names using a predefined ‘parent’ lookup table. This table is fetched dynamically from database and incorporated into schema whenever application.parent_link is present in the schema. ‘application.parent_link’ has two attributes: ‘indicator’ (the field in register which indicates whether the record is a parent) and ‘is_parent_when’ (the value of ‘indicator’ field that indicates is_parent status). Whenever application.parent_link is present in schema, it is assumed that parent_id field is defined in the register as a lookup field. Both Browser and Android applications support this functionality. Unlike the browser which refreshes the schema (and consequently fetches an updated parent lookup table to be embedded in the schema) every time it is refreshed, the Android application needs to have a ‘Refresh Schema’ button since the Android application is supposed to be run offline.

User levels and online data entry by 'Register Users'

There are three types of users based on value of 'userlvl' field in 'users' table

  1. normal: all options are allowed to normal user as admin except adding periods (normal user functionality needs to be further refined)
  2. reserved for read_only user (yet to be added)
  3. admin: all functionality available to administrative user.
  4. register user

To allow register users (i.e, those that belong to the register like company and establishment users). If ‘userlvl’ is 4, then ‘username’ will contain the ‘search_id’ of the register user.

When register user is logged in (userlvl == 4):
  • Limit register grid to single record corresponding to search_id==username
  • Hide ‘Add to Register’ and ‘Add register entries to survey’ buttons
  • Disable survey navigation buttons


  • Field names (which map to database column names) are all converted to lowercase before finalization.
  • System will automatically add ‘modified_by’ (string) and ‘modified_at’ (date) fields to the ‘period’, ‘register’, and each of the ‘section’ tables.
  • Adding a register record automatically adds a blank record for each survey table
  • (Handling of survey/questionnaire ID) If the “unique=’true’” attribute is specified for a string field, the string field is enforced to be unique. Only one unique field is allowed. Unique fields are not currently displayed in the Android application.

Field Types


  • display
    • for field labels and column headers
    • if name begins with ‘header’, it is displayed in bold in browser
  • string
    • option for lookup (see below)
    • if ‘header’ attribute is present, it is displayed in bold in browser
  • integer
  • smallint
  • tinyint
    • option for lookup
      • if ‘header’ attribute is present, it is displayed in bold in browser
      • lookup mechanism handled as trigger (see below)
    • option for display as checkbox
  • float

Readonly field types

  • Calculated -> hidden
  • Lookup
  • Display

First perform calculations and then lookups (to allow lookup fields based on calculations to work as in mouhafazat and qada)


  • if ‘header’ attribute is present, it is displayed in bold in browser
  • These are calculated on the fly as user enters data (see triggers below).

Calculated ‘later’ fields which are calculated in stages upon user action

Applicable only to formula calculations. They are calculated only when ‘Calculate’ button is pressed (present on screens which have at least one ‘calculated-later’ field). Having stages (1, 2, 3 …) attached to calculations makes it possible for system to handle calculations which refer to other calculations, thus avoiding repetition in specifying lengthy formulas.


Three types: Validity, Calculation and Lookup. For each field in each section, there can be:

  • One or more validity checks
  • One or more calculations
  • One lookup


Apply to non-calculated fields, and are triggered every time the field value changes. They are of the following types:

  • range
min and max bounds (integer) specified for a field along with error message when out of range
  • formula_range
min and max bounds (integer or float) for the result of the ‘formula’ (described below) which is re-calculated upon field value change.
  • trigger_skip
  • coexist_with


  • substring
  • sum
  • list of fields from same section (to include other section fields, use ‘formula’ below)
  • formula
    • specified in schema as a postfix formula
    • field specification include section and field name


  • display options as i) pull-down or ii) fetch
  • unique_fields option within range of fields to ensure that the code entered is unique within those fields.

Overall Checks

Apply to calculated fields and are checked for before saving a section (as opposed to being triggered upon field update). Purpose is to ensure total fields follow certain restrictions which are of the following types:

  • ‘not_zero’
  • ‘equals’ and ‘greater_than’ - checks if this field is equal to (or greater than depending upon type) given comparison with the following two options:
    • if_not_zero option (check only if_not_zero)
    • is_value option (compare_with attribute is a value instead of a field)


Android database

For the local database, Android provides classes for SQLite, but for this application, rather than generalizing the functionality of the SQLite database calls, I found it more natural to replicate the database as a directory structure on the phone (the default is the SD card). It is a "period"/"id" folder structure at the bottom of which are the individual data files stored as .json files, each file corresponding to a section (which in turn corresponds to a table). Everything is stored under the “hm.Survey” folder created on the SD card at startup. The id is the search id and not the generated id by the underlying database (which is the primary key).

Android data entry

  • filtering by region.code and
    • region.code corresponds to the prefix of the search_id that is filtered
    • is the name is just a descriptive label given by the Android application (it is not retrieved from the database)
  • instance
the name of the application (used by the server database script to identify the application folder)
  • url
Url of the server where the application resides
  • register.handheld
If present, data entry is enabled for the register as well (in addition to the survey sections which are enabled always)
  • register.gps
Allows capture of gps coordinates; assumes presence of ‘longitude’ and ‘latitude’ fields

Business Register

Two survey applications can enter into a Survey-Business Register relationship. The idea is to allow one (Business Register) to be the source of establishments for the other (the Survey end of the relationship). At the beginning of each survey cycle, establishments are brought in from the Business Register and at the end of the survey, Business Register establishments are updated. The linkage is specified by having a attribute in the schema corresponding to the Survey end of the relationship which is set to the name of the instance (i.e., folder name within the 'instance' folder) that points to the Business Register.