Configuring translations for database questions
Introduction
This topic explains how database questions support multiple languages. Database question are designed to generate categories, that are used during surveys, from external data sources. Database questions are typically used when responses can dynamically change, or when responses are stored in an external data source. The database question syntax is similar to the following example:
CountryLiveIn "<br/>In which country are you now living?"
text
db(
ConnectionString = "Provider=Microsoft.ACE.OLEDB.12.0;
Data Source=%PROJECT_DIRECTORY%/makes_data.xls;
Extended Properties=""Excel 12.0;HDR=YES;IMEX=1"";",
Table = "Country$",
MinAnswers = 1,
MaxAnswers = 1,
Columns(
ID = "MakeID",
Label = "MakeLabel"
),
CacheConnection = false
);
The following database question types support multiple languages:
▪Single response database questions are rendered similarly to single response questions (which have only one valid response). Internally, the database response's ID column value is stored as the question value. A database response is rendered externally as a categorical question, but is rendered internally as a text, date, long, or double question. The value that is written into the case data is the text or numeric value of the database's ID column, not a categorical value.
▪Multiple response database questions are rendered similarly to multiple response questions. Internally, a multiple response database question is the same as a loop question; the iterations are retrieved from the MinAnswers and MaxAnswers values, and the question contains a single subquestion named DBID. The database elements that are retrieved from the external database are added to the DBID’s Elements.
▪When categories are chosen during a survey, the data is stored in the corresponding iteration. For example, when category 1 and category 3 are selected, the case data is stored as:
MRDBQ[1].DBID = "ModelID1"
MRDBQ[2].DBID = NULL
MRDBQ[3].DBID = "ModelID3"
▪Database array questions extend to numeric loop questions, but the iterations are database elements instead of numeric. As such, database array questions are rendered similarly to categorical loop questions. As is the case with multiple response database question, database array questions are added to the sub question collection, and the database elements are added to the DBID question.
▪Database array question case data is stored like this:
DBA[1].DBID = "Part1"
DBA[1].Q1 = "value1"
DBA[1].Q2 = "value2"
DBA[2].DBID = "Part2"
DBA[2].Q1 = "value3"
DBA[2].Q2 = "value4"
DBA[3].DBID = "Part3"
DBA[3].Q1 = "value5"
DBA[3].Q2 = "value6"
Supporting multiple translations
To support for multiple translations, you must update the metadata syntax to include translation columns that are stored alongside the other columns. For example:
The translation columns must follow the following naming convention:
LabelColumnName_languageName_contextName_LabelTypeName
▪languageName must refer to an existing language, as only languages that are already included in the document can be used. The same rule applies to contextName and LabelTypeName (the named Context and LabelType must exist in the document). The translation column is ignored when the specified Language, Context, or LabelType does not exist.
▪When a translation column is not specified for a specific language (not the current language), an alternative language is considered in the survey. When no alternative language is specified, the default language is considered. Similar rules apply for both Context and LabelType.
▪When the cell value is DBNull, a label is not written for the corresponding Language, Context, and LabelType. The alternative Language, Context, and LabelType are considered when the survey in run.
▪Part of the column name can remain empty, and the ending underscores characters can be ignored. When this is the case, the current Language, Context, and LabelType are used when writing the label. For example:
MakeLabel_en-US
MakeLabel__myQuestion
MakeLabel___myLabelType
MDM implementation
The following sample code demonstrates how translation columns are written into the element’s label.
Dim IDColumn, LabelColumn
IDColumn = "MakeID"
LabelColumn = "MakeLabel"
Dim rs
Set rs = adoDB.Execute(“select * from Table1”)
//check which column are the translation columns
Dim field
Dim cachedTranslationColumns[]
For each field in rs.Columns
If field.Name.StartWith(LabelColumn)
//parse the language/context/labeltypes
Dim lang,context,labletype
lang = currentlang
context = currentContext
labelType = currentlabelType
Dim lclPart
lclPart = filed.Name.Right(len(LabelColumn))
Dim lclArray = lcl.split("_") '_lang_context_label
if len(lclArray) >=2 then
lang = lclArray[1]
end if
if len(lclArray) >=3 then
context = lclArray[2]
end if
if len(lclArray) >=4 then
labelType = lclArray[3]
end if
cachedTranslationColumns[field.Name] =
LCL(lang,context,labelType)
End If
Dim row
For each row in rs.Rows
Dim element
element = pDBElements->AddNewElement(row[IDColumn])
element.Label = row[LabelColumn]
//write translations
For each field in cachedTranslationColumns
Dim lcl
lcl = cachedTranslationColumns[field.Name]
element.Labels[lcl.labeltype].Text[lcl.
context][lcl.lang] = row[field.Name].Value
Next
Next
See also
