GM-X Survey/Questionnaire Application

This subsystem allows the organisation to create surveys or questionnaires which may be sent sent to people for their responses. These responses may be kept anonymous. Each survey has a time limit, and a person cannot respond to the same survey more than once.

Surveys and Questions

Each Survey/Questionnaire is comprised of a number of questions which are grouped into sections. Each question can have a particular type of answer - text, a number, or a choice from multiple options. Each survey has a range of dates outside of which new responses will not be accepted.

SURVEY_TYPE table

Field	Type	Description
survey_type_id	string	This is the unique identity for this entry.
survey_type_desc	string	This is the description for this entry.
rdcaccount_id	numeric	This identifies the account which owns this entry. The value '1' represents the shared account while other values are for private accounts.

SURVEY_STATUS_TYPE table

Field	Type	Description
survey_status_type_id	string	This is the unique identity for this entry.
survey_status_type_name	string	This is the short name for this entry.
survey_status_type_desc	string	This is the longer description for this entry.
sort_seq	numeric	This allows the user to change the sequence in which these entries will be displayed when choosing from a dropdown list.

SURVEY_HDR table

This identifies each different Survey or Questionnaire with its own range of dates, sections and questions.

Field	Type	Description
survey_id	numeric	This is the unique identity for the entry which is generated by the system.
rdcaccount_id	numeric	This identifies the account which owns this entry. The value '1' represents the shared account while other values are for private accounts.
survey_name	string	This is a short name for this entry.
survey_long_name	string	This is a long name for this entry.
survey_type_id	string	This links to an entry on the SURVEY_TYPE table.
open_on	date	This is the date on which this survey opens for responses.
close_on	date	This is the date on which this survey closes for responses.
survey_status_type_id	string	This links to an entry on the SURVEY_STATUS_TYPE table. This shows the current status of this survey.
template_id	string	This links to an entry on the EMAIL_TEMPLATE table. It identifies the email template that will be used when sending out invitations to participate.
is_anonymous	boolean	If this is set to 'Yes' then all responses will be anonymous.

SURVEY_STATUS_HIST table

Field	Type	Description
survey_id	numeric	This links to an entry on the SURVEY_HDR table.
seq_no	numeric	This is a sequence number which is generated by the system.
survey_status_type_id	string	This links to an entry on the SURVEY_STATUS_TYPE table.
status_date	date	This is date on which this entry was created.

SURVEY_SECTION table

This identifies the various sections (question groups) which exist within a particular Survey/Questionnaire.

It is possible to alter the sequence of sections by first selecting a section then pressing one of the "Move Up" or "Move Down" buttons. This will change the sequence number of the selected entry and also the next/previous entry (as appropriate) in order to swap them around. After the swap the selected entry will remain selected in case it needs to be moved again.

Field	Type	Description
survey_id	numeric	This links to an entry on the SURVEY_HDR table.
section_id	numeric	This is a value generated by the system which makes the primary key unique.
section_seq	numeric	This allows the user to change the sequence in which the sections in this survey will be processed and displayed.
section_name	string	This is the description for this entry.
is_confidential	boolean	This indicates if the contents of this section is confidential and should be omitted from any PDF output.

SURVEY_QUESTION table

This identifies the individual questions which exist within a particular Section of a Survey/Questionnaire. It also identifies the form that each answer should take - text, number, or a choice from a list of options.

It is possible to alter the sequence of questions in a section by first selecting a question then pressing one of the "Move Up" or "Move Down" buttons. This will change the sequence number of the selected entry and also the next/previous entry (as appropriate) in order to swap them around. After the swap the selected entry will remain selected in case it needs to be moved again.

Field	Type	Description
survey_id	numeric	This links to an entry on the SURVEY_SECTION table.
section_id	numeric	This links to an entry on the SURVEY_SECTION table.
question_id	numeric	This is a value generated by the system which makes the primary key unique.
question_seq	numeric	This allows the user to change the sequence in which the questions in this section will be processed and displayed.
question_text	string	This is the question which is to be answered.
answer_type	string	Text - the answer is entered as free format text Number - the answer must be a number which falls within the min/max values defined on the NUMBER_OPTION table. Multiple Choice - the answer must be chosen from those entries defined on the ANSWER_OPTION table.
answer_control	string	Identifies how the answer can be entered. If the answer_type is "Text" or "Number" this must be "Text Box", but for multiple choice answers the options are as follows: Dropdown List - choose only one entry Multi-Dropdown List - choose one or more entries Horizontal Radio Group - choose only one entry Vertical Radio Group - choose only one entry Horizontal Multi-Check Box - choose one or more entries Vertical Multi-Check Box - choose one or more entries Please refer to Answer Controls for details.
advice_text	string	Optional. An additional piece of text which may supply some advice regarding the answer.
matrix_type	string	Optional. Used only when several questions share the same group of answers which are displayed horizontally across the page. Two types of matrix are allowed: Start Matrix, one response per column Start Matrix, multiple responses per column This is used only on the first question in a matrix as it defines all the answer options for all subsequent questions in the same matrix. Please refer to Question Matrix/Grid for details.
question_id_matrix	numeric	Optional. If a question is the second or subsequent entry in a matrix then this identifies the first question in the matrix as all answer options are defined for the first question only and not repeated for all subsequent questions.
advice_text	string	This gives advice which may be useful when constructing the answer.
risk_status_id	numeric	This links to an entry on the RISK_STATUS table.
is_hidden_respondent	boolean	If this is 'YES' then the question and answer will be hidden from the respondent but not the reviewer.
object_id	string	Optional. Refer to Copying answer to another database table
column_id	string	Optional. Refer to Copying answer to another database table
object_id_link	string	Optional. Refer to Copying answer to another database table

a) Answer Controls

Various controls are available which allow the answer to be entered in an appropriate fashion. It is therefore very important that the right control be selected for the answer to each particular question.

Figure 1(a) shows a multi-line text box which is suitable for answers which consist of strings of text, such as addresses.

Figure 1(b) shows a single line text box which is suitable for answers which are numbers.

Figure 1(d) shows a horizontal group of radio buttons from which a single option can be chosen.

Figure 1(e) shows a vertical group of radio buttons from which a single option can be chosen.

Figure 1(g) shows a horizontal group of check boxes from which multiple options can be chosen.

Figure 1(h) shows a vertical group of check boxes from which multiple options can be chosen.

b) Question Matrix/Grid

Some questions may be grouped together in the form of a matrix or grid by virtue of the fact that they share a common set of answer options. The first question in the matrix is identified by selecting the matrix_type and then defining the answer options for that matrix. By then pressing the "Matrix Questions" button the user will be taken to another screen which will allow all the following questions for this matrix to be entered. All these subsequent questions will share the same ANSWER_OPTIONS and answer_control as the first question in the matrix.

When responding to a Survey/Questionnaire the entire matrix will be shown in a single screen, like the example shown in Figure 1(i). Each question will be on its own line with the answer options as either radio buttons or check boxes in a horizontal group on the same line as the question. The same set of answer options will be shown for each question in the matrix, with each answer appearing as a vertical column. Note the use of the advice_text field for supplying additional information regarding the question and the possible answers.

Each column of answers can have its own label, such as "Most Important", "Important" and "Least Important" in Figure 1(i). The method used to identify these labels is shown in Figure 1(j)

Depending on which matrix_type is chosen (either "one response per column" or "multiple responses per column") it may or may not be possible for the same answer to be used for more than one question, or to be used more than once in the same vertical column.

Figure 1(i) shows a matrix of radio buttons from which a single response can be chosen for each question. By choosing "one response per column" as the matrix_type this prevents the same answer from being used more than once in the same vertical column. In this situation there must be the same number of questions as there are answers so that each question is answered once and each answer is only used for one question.

Figure 1(j) shows how to define the labels which appear above each column in a matrix. The text which is separated out is enclosed in square brackets ('[' and ']').

Figure 1(k) shows a matrix of radio buttons from which a single response can be chosen for each question. By choosing "multiple responses per column" as the matrix_type this allows the same answer to be used more than once in the same vertical column.

Figure 1(l) shows a matrix of check boxes. Unlike radio buttons more than one answer option can be selected for the same question.

c) Copying answer to another database table

By default each set of answers is restricted to the SURVEY_ANSWER_DTL table, in which case the object_id, column_id and object_id_link fields should be left blank. However, there may be situations where a particular answer needs to be copied to some other part of the system, in which case object_id should be used to identify the database table, and column_id should be used to identify the column on that table which should be updated. Note that these two fields go together - they must both be blank or both be non-blank. You cannot have one without the other.

The format of the object_id (and object_id_link) value is <subsystem>/<tablename> where:

In order to identify which record should be updated the table must contain a column called survey_answer_id so that it can be related to the current set of answers by the respondent (who is identified by the party_id on the SURVEY_ANSWER_HDR table).

If the table identified in object_id does not contain a column called survey_answer_id then an additional table should be identified in object_id_link. A record will be read from this table using the current value of survey_answer_id, and data from this table will be used to construct the primary key of the object_id table. As an example the following object names could be used:

Column Name	Table	Description
object_id_link	personnel/employee_assmt_appraisal	This contains a column called survey_answer_id which is linked to the current set of answers. Other values on this record can be used to create the primary key which identifies a record in the object_id_link table.
object_id	personnel/employee_assessment	This contains the column which is to be updated.

Note that if this standard use of object_id and object_id_link still cannot be used to identify the single record which is to be updated then it will be necessary to insert custom code into the SURVEY_ANSWER_DTL class.

RISK_STATUS table

This identifies the possible risk status values for a question in a Survey/Questionnaire.

Field	Type	Description
risk_status_id	numeric	This is the unique identity for the entry which is generated by the system.
risk_status_desc	string	This is the description for this entry.
rdcaccount_id	numeric	This identifies the account which owns this entry. The value '1' represents the shared account while other values are for private accounts.

ANSWER_OPTION table

When an answer is to be chosen from a list of options this identifies what those options are.

Field	Type	Description
survey_id	numeric	This links to an entry on the SURVEY_QUESTION table.
section_id	numeric	This links to an entry on the SURVEY_QUESTION table.
question_id	numeric	This links to an entry on the SURVEY_QUESTION table.
answer_id	numeric	This is a system generated number which makes this entry unique.
answer_seq	numeric	This allows the user to change the sequence in which the choice of answers will be displayed.
answer_text	string	This is the text that will be displayed to the user.
answer_weight	decimal	Optional. For multiple-choice answers this can be used to provide each answer with a score on the Likert Scale to aid in statistical analysis of the responses.
jumpto_section_seq	numeric	Optional. This can be used with multiple-choice answers to cause the system to jump to the start of another section of questions, thus skipping all intermediate questions. This should always be to a later section and not a earlier section. Do not specify a jump to the section which immediately follows the current one as this by default is the next section and does not require a jump. Note that this option cannot be used in a matrix/grid as this deals with multiple questions as a single group to which there can be multiple answers.

NUMBER_OPTION table

When an answer is a number this identifies the minimum and maximum allowable values.

Field	Type	Description
survey_id	numeric	This links to an entry on the SURVEY_QUESTION table.
section_id	numeric	This links to an entry on the SURVEY_QUESTION table.
question_id	numeric	This links to an entry on the SURVEY_QUESTION table.
min_value	numeric	This is the minimum value.
max_value	numeric	This is the maximum value.

DEFAULT_PROMPT table

This holds a range of possible prompt values which may be linked to any question in the survey.

Field	Type	Description
survey_id	numeric	This links to an entry on the SURVEY_HDR table.
prompt_id	numeric	This is a system generated number which makes this entry unique.
prompt_desc	string	This is the description for this entry.

QUESTION_PROMPT table

Field	Type	Description
survey_id	numeric	This links to an entry on the SURVEY_QUESTION table.
section_id	numeric	This links to an entry on the SURVEY_QUESTION table.
question_id	numeric	This links to an entry on the SURVEY_QUESTION table.
prompt_id	numeric	This links to an entry on the DEFAULT_PROMPT table.

Invited Parties

People cannot participate in a Survey/Questionnaire unless they have received an invitation via email. The survey administrator maintains a list of invitees/participants on the INVITED_PARTIES table, and when the invitations are sent out the email will contain a personalised hyperlink. When the user clicks on this hyperlink he/she will be taken to the screen which will accept answers for this survey. If the is_anonymous flag is set on the SURVEY_HDR record then all the hyperlinks will contain a new-generated anonymous_id instead of a party_id so that answers cannot be related back to an individual.

When the user clicks on the hyperlink for the first time the system will see that the value of survey_answer_id is currently empty, so it will create a new entry on the SURVEY_ANSWER_HDR table and use the generated id to populate this field on either the INVITED_PARTIES entry or INVITED_ANONYMOUS entry as appropriate. This will prevent any person from taking the same survey more than once.

To use the anonymous mechanism a record with the id of 'Anonymous' must be created on the MENU database and linked to a party with the name 'Anonymous' on the PARTY database. The party_id of this anonymous user will NOT be the same as the anonymous_id on the INVITED_ANONYMOUS table.

INVITED_PARTIES table

Everyone who is invited to participate in a survey/questionnaire will have an entry on this table. When the survey's status is upgraded to "In Progress" all pending invitations will be sent out and the email_sent field will be updated accordingly. Any new parties who are added to this list after the status has been set to "In Progress" will have their emails sent out immediately.

Field	Type	Description
survey_id	numeric	This links to an entry on the SURVEY_HDR table.
party_id	numeric	This links to an entry on the PARTY table.
email_sent	datetime	This is the date and time on which the invitation was emailed to this party.
survey_answer_id	numeric	Optional. This links to an entry on the SURVEY_ANSWER_HDR table. It is populated when this party accepts the invitation and begins to enter his/her answers. Note that if the is_anonymous flag has been set on the SURVEY_HDR entry then the value on this table will NOT be used - the INVITED_ANONYMOUS entry will be updated instead.

INVITED_ANONYMOUS table

If the is_anonymous flag has been set on the SURVEY_HDR entry then when emails are sent out a new anonymous_id will be created for each invitation and included in the email's hyperlink. This will ensure that the user's response will be linked to the central ANONYMOUS party instead of his/her individual party_id.

Field	Type	Description
anonymous_id	numeric	This is generated by the system whenever an email is sent out to a participant who should be kept anonymous.
survey_id	numeric	This links to an entry on the SURVEY_HDR table.
survey_answer_id	numeric	This links to an entry on the SURVEY_ANSWER_HDR table. It is populated when the recipient of this anonymous_id accepts the invitation and begins to enter his/her answers. It is also used to ensure that this anonymous_id does not supply more than one set of responses to the same survey.

Survey Answers

Each individual response to a survey will create an entry on the SURVEY_ANSWER_HDR table to identify the respondent (who may be "Anonymous") with entries on the SURVEY_ANSWER_DTL table for each answer.

Questions and answers will be displayed one to a page. Once an answer has been submitted the system will automatically move on to the next question. The only exception is in the case of a matrix, in which case the entire matrix will be displayed on a single page.

SURVEY_ANSWER_HDR table

This identifies which set of answers belongs to which person. Note that the person may be "Anonymous".

Field	Type	Description
survey_answer_id	numeric	This is the unique identity for the entry which is generated by the system.
survey_id	numeric	This links to an entry on the SURVEY_HDR table.
party_id	numeric	This links to an entry on the PARTY table. This identifies the respondent (responding party) who supplied this set of answers. For anonymous responses this will always be the single 'Anonymous' user.
answer_date	date	This is the date on which this series of answers was first started.
is_complete	numeric	A YES/NO flag which indicates if all the questions have been answered. This excludes any questions which were skipped due to a jump which was specified on the ANSWER_OPTION table.
rdcaccount_id	numeric	This identifies the account which owns this entry. The value '1' represents the shared account while other values are for private accounts.

SURVEY_ANSWER_DTL table

Field	Type	Description
survey_answer_id	numeric	This links to an entry on the SURVEY_ANSWER_HDR table.
survey_id	numeric	This links to an entry on the SURVEY_QUESTION table.
section_id	numeric	This links to an entry on the SURVEY_QUESTION table
question_id	numeric	This links to an entry on the SURVEY_QUESTION table.
answer_text	numeric	This is the answer to this question. It may be a string of text, a number, or a link to and entry in the ANSWER_OPTION table.
weighting_id	numeric	This links to an entry on the RISK_WEIGHTING table.
measure_adequate	boolean	This records a value of YES or NO.

RISK_WEIGHTING table

This identifies the possible risk weighting values for an answer in a Survey/Questionnaire.

Field	Type	Description
weighting_id	numeric	This is the unique identity for the entry which is generated by the system.
weighting_desc	string	This is the description for this entry.
rdcaccount_id	numeric	This identifies the account which owns this entry. The value '1' represents the shared account while other values are for private accounts.

16 Nov 2016	Updated the SURVEY_QUESTION table to include the is_hidden_respondent, object_id, column_id and object_id_link columns.
15 Sep 2016	Updated the SURVEY_SECTION table to include the is_confidential column.
22 Dec 2015	Updated the SURVEY_QUESTION table to include the matrix_type and question_id_matrix columns. Updated the ANSWER_OPTION table to include the answer_weight column.
08 Dec 2015	Added the INVITED_ANONYMOUS table.
05 Dec 2015	Added the INVITED_PARTIES table.
04 Dec 2015	Removed the ASSET_TYPE table. Removed the LOCATION_ADDRESS table. Removed the LOCATION_TYPE table. Removed the ORG_TYPE table. Removed the TREE_NODE table. Updated the SURVEY_ANSWER_HDR table to remove the node_id column.
03 Dec 2015	Updated the RISK_STATUS table to include the rdcaccount_id column. Updated the RISK_WEIGHTING table to include the rdcaccount_id column. Updated the SURVEY_ANSWER_HDR table to include the rdcaccount_id column. Updated the SURVEY_TYPE table to include the rdcaccount_id column. Updated the SURVEY_QUESTION table to include the answer_control column. Updated the SURVEY_HDR table to include the rdcaccount_id, open_on, close_on, survey_status_type_id, template_id and is_anonymous columns.

GM-X ERP for Blockchain - The SURVEY/QUESTIONNAIRE subsystem

By Tony Marston

Introduction