Survey Database

Note: When the Source Type is set to Survey Database, the Key Field property is hidden, the Columns options become active, and the Validation Setting options are only available if Columns is set to Survey Data Template. In addition, the Map Fields button is available when the Source is set to Delimited Text File or Forsta Survey Database.

Figure 1 - The Target tab when the Source Type is set to Triple-S Data and the Target Type is set to Forsta Survey Database

When Target Type is set to ForstaSurvey Database, the properties and options available on the tab are as follows:

Project ID – type in here or select the project ID of the target.
Use Test Database – check this box if you are testing the setup and wish to use data from the test database.
Clear existing data – becomes active when the Source Type is set to Forsta Survey Database (it is inactive if Source Type is set to Triple-S Data File). Check to remove all existing data from the database before adding the new data.

WARNING:
All your data will be deleted.
If you select Clear Existing Data, ALL existing data will be removed from the database when the rule is run; not just the variables in the schema. This deletion process cannot be undone. Be very careful when using this option.

Execution Mode - this property has different options available depending on the Source and Target selections:

When both Source and Target are Survey Database, and the same project is selected for the Source and the Target:

Update Data - records from the source will update any matching records in the target database. The selected key field will be used to match records from the source to records in the target database. Any records in the source database that do not match the selected key fields in the target will be ignored.
Delete Data - when the Action tab contains an action script that includes the DeleteCurrentResponse() or the DeleteCurrentResponseAndRespondent() function, selecting Delete Data will perform the specified action. DeleteCurrentResponse() will delete the responses from the survey database that are selected by the filter currently applied in the Source tab (note that use of a filter in this case is mandatory). DeleteCurrentResponseAndRespondent() will delete both the responses from the survey database and the respondents from the respondent database that are selected by the filter currently applied in the Source tab. Note that if you continue with this action, the selected responses and/or respondents will be permanently removed from the databases and cannot later be retrieved. Note that only one project can be selected as source when Delete Data is selected as Execution mode.

Important
If your survey contains quotas, deleting data could cause quota counts to be incorrect. This can be rectified by running a quota recalculation for the quotas. Note that this is not performed automatically when the data is deleted

When both Source and Target are Survey Database and different projects are selected for the Source and the Target, or if a source other than Survey Database is selected, you can choose between the three modes listed below. In all the modes the user will be asked to provide a key (see Key field below).

Update Data - any records in the source that already exist in the target survey database will be updated with values from the source. Any records in the source that do not already exist in the target survey database will be ignored.
Append Data - any records in the source that do not exist in the target survey database will be added to the target survey database. Any records in the source that already exist in the target survey database will be ignored.
Merge data - all the data from the source will be added to the target survey database. Any records in the source that already exist in the target survey database will be updated, and any records in the source that do not currently exist in the target survey database will be added to the target survey database.

In these scenarios, when new records are added, the new records are inserted both in the response data and in the respondent table (respondent list).

Map Fields - available if Source Type is Delimited Text File or Forsta Survey Database. Use this to map fields/columns with different names in source and target, from the source to the Forsta Survey Database (go to Mapping Fields for more information).
Key field - the key field is used as a unique identifier to indicate which records are to be updated and which are to be appended. The key field is also used to map the responses in the top-level file with the responses in loop response files. The key field must exist in both the import file(s) and the database.

System variables respid and responseid and all survey variables with the "Indexed" property set, will be displayed in the "Key field" drop-down. You may choose any survey variable that has a unique value for each respondent as key field. The key field should either be one of the system-provided ids (respid or responseid), or an open text question with a defined field width. Examples of key fields could be membership number, customer id or email address.

In the event the Source is Delimited text file or Excel, a key field is not required when appending data. So in this case, when Execution Mode is set to Append Data, the Key Field can be set to None.

Important
The fields respid and responseid are system-provided ids in Forsta. When appending records (inserting records that are not already present) through data import with one of these fields as key field, the system will replace the ids you import with new system-generated ids.

Columns - select the columns you wish to include; all, selected, or as used in the selected template.
Source / Target variable usage - This option is used when columns are selected either manually or by using a template. When both the source and the target are Survey Database, and Columns are selected, this property is active. Two options are then available:
As filter (Target variables match source variables) (default) - only columns that are selected from the source and that exist in the target will be transferred to the target. For example: The source contains the columns [responseid, interview_start, interview_end, status, q1, q2], and the target has the columns selection [responseid, status, q1, q3]. The columns transferred to the target database will be those that exist in both the source and the target [responseid, status, q1].
Convert Source variables to new Target variables - this option can only be used when the source is a survey database from a different project. The selected columns will be available as output columns in the Action Script, where a value can be assigned. The output columns are prefixed with "T.". Only the columns selected in the target will be transferred to the target database. Source columns can be mapped to target columns that have different or same names. In this way, source columns can also be transferred to the target database automatically. Columns responseid and respid will always be transferred from source to target.

Example: The source contains the columns [responseid, interview_start, interview_end, status, q1, q2]. The target has the columns [responseid,status,q5_a, q5_b]. Status from source is mapped to status in target. The Action Script looks like:

T.q5_a = q1;

T.q5_b = q2;

If the following row comes from the source [23, '01-01-2014 12:33:23 PM','01-01-2014 12:37:23 PM', 'complete', 1, 6],
then the input to the target will be [responseid, status, q5_q, q5_b] with values [23, 'complete', 1, 6].
Validation Setting - check the boxes as required (go to Validation Rules for more information).
Disable Population of Respondent Data - this checkbox appears only if both Source and Target are set to Forsta Survey Database and different databases are selected. Selecting this will cause the import to run faster because no respondent data is added. This property must therefore only be selected for projects where the target database will not be accessed by respondents and respondent data is not required, for example if the project is used to collect data from other surveys.

Important
Do not check this box for live surveys.

Use Transaction - if this option is selected, all changes made to the target project will be rolled back if an error occurs.

Note: If your Target Survey Database or Respondent Data does not contain any records and you are using Action scripting to push data from the Source, if the script does not change any values and contains only the “AddLogMessage “ function, data will only be transferred to the Target if you use “Append” as the Execution mode.

Forsta Data Processing Support

Articles in this section

In this article