This is a survey engine scripting function used to retrieve data from an additional column or columns associated with a specific table inside of a table lookup list.
GetDBColumnValue(schemaID, tableID, keyID, additionalColumnName, [languageID])where:
schemaID is the ID of the schema in database designer.tableID is the ID of the table.keyID (string) is the ID of the value being searched on (ID row from the contents of the table).additionalColumnName is the name of the column that the data is to be pulled from.[languageID] is the ID of the language to be retrieved from. This is optional; if not specified it uses the respondent's language for this survey.
Note: The schema and table IDs being referenced must be part of the survey, either as an answer list or loop list. If a table is referenced that is not used in the survey, the function will fail at run-time.
This function can be used to retrieve values from additional columns that are associated with answers for a table lookup list defined within database designer. For example a table lookup list may be defined in the following way:
The database schema “Basic_Cars” (schema ID 805) contains table “car_list” (table ID 1543) with a list of cars, each containing their own unique string key value. Additionally there are 2 columns “cost” and “spec” containing background information relating to the specific cars.
Figure 1 - Retrieving values
During the survey, the author would like to pipe in the answer to “cost” and store the value of “spec” in a hidden question. If table lookup question named car_owned is based on this table lookup as follows:
Figure 2 - Table lookup example
Piping in the value of the cost of the selected car can be achieved using the following syntax:
^GetDBColumnValue(805, 1543, f('car_owned').get(), 'cost')^as shown here:
Figure 3 - Table lookup/piping result
Similarly, if the value for spec for the English language (language ID is 9) is to be stored in the hidden question c_spec, the following script syntax can be used:
f('c_spec').set(GetDBColumnValue(805, 1543, f('car_owned').get(), 'spec', 9))If the keyID value is supplied explicitly, this must be contained within quotes as this is a string value. For example, to store the value of keyed 7, the following syntax can be used:
f('c_spec').set(GetDBColumnValue(805, 1543, '7', 'spec', 9))Note: The GetDBColumnValue() function is not executed during a Random Data Generator run. In this case the value returned will be empty.
Note: The GetAdditionalColumnValue method is described here (go to GetAdditionalColumnValue for more information).