NIPO ODIN Version 5.17
Scenario 4: Store Several Records in a Table (Variables with Arrays)
This scenario applies the following directives:
- We have a survey called
Sqladd5. - We have created table called
TestPanelmembers. - The primary key for this table is the integer field
PanelMemberNumber, that has a constraint of 1 to 99999999. - In the survey we ask for the names of all family members. These names will be saved in the
TestPanelmembers table, each family member in a new record. - We use array variables so maximum 9 records will be saved in one the
*SQLADD command.
Example NIPO ODIN questionnaire for *SQLADD: scenario 4
*VARS FamilyNumber, FamilyMemberNumber[9], FamilySize, Count
*TEXTVARS Name[9]
*QUESTION 1 *FORM
1:FamilyNumber: *NUMBER 61L6 *MIN 1 *MAX 999999 *SAVE FamilyNumber
2:FamilySize: *NUMBER 67L1 *SAVE FamilySize *MIN 1 *MAX 9
*QUESTION 1001 *CODES 68L9 *MULTI
*REPEAT 9
*IF [?R <= FamilySize] *INCLUDE Q1001 [?R] \
*PUT FamilyMemberNumber[?R] [(FamilyNumber * 100) + ?R]
*ENDREP
*QUESTION 2 *FORM *CONTROL Q1001 W
Names of family members
1: First person: *ALPHA 77L35 *SAVE Name[1]
2: Second person: *ALPHA 112L35 *SAVE Name[2]
3: Third person: *ALPHA 147L35 *SAVE Name[3]
4: Fourth person: *ALPHA 182L35 *SAVE Name[4]
5: Fifth person: *ALPHA 217L35 *SAVE Name[5]
6: Sixth person: *ALPHA 252L35 *SAVE Name[6]
7: Seventh person: *ALPHA 287L35 *SAVE Name[7]
8: Eighth person: *ALPHA 322L35 *SAVE Name[8]
9: Ninth person: *ALPHA 357L35 *SAVE Name[9]
*SQLADD Count FamilyMemberNumber,Name \
"SELECT PanelMemberNumber,Name \
FROM TestPanelmembers"
*QUESTION 3 *IF [Count = FamilySize]
Writing a new records was successful *?Count times
*QUESTION 4 *IF [Count = 0]
Writing new records failed
*QUESTION 5 *IF [Count >= 1 & Count <> FamilySize]
Writing some new records failed. Only *?Count records were created
*QUESTION 6
That's all
*END
In this example we can write maximum 9 records per *SQLADD command, as the arrays have an index of 9. But obviously not all families have 9 family members. We solved this by setting the FamilyMemberNumber only for existing family members. But this means the *SQLADD command may report an error to the Fieldwork Maanger for each family smaller than 9. These errors may be ignored.
The solution for these messages, that may confuse your fieldwork managers, would be not to use array variables and to create a *REPEAT loop and only execute the *SQLADD command as often as you have family members.
Example NIPO ODIN questionnaire for *SQLADD: scenario 4b
*VARS FamilyNumber, FamilyMemberNumber, FamilySize, Count
*TEXTVARS Name,Person
*QUESTION 1 *FORM
1:FamilyNumber: *NUMBER 61L6 *MIN 1 *MAX 999999 *SAVE FamilyNumber
2:FamilySize: *NUMBER 67L1 *SAVE FamilySize *MIN 1 *MAX 9
*QUESTION 1001 *CODES 68L9 *MULTI *DUMMY
1: First person
2: Second person
3: Third person
4: Fourth person
5: Fifth person
6: Sixth person
7: Seventh person
8: Eighth person
9: Ninth person
*REPEAT 9
*IF [ ?R > FamilySize ] *END
*PUT Person Q1001,?R
*PUT FamilyMemberNumber [(FamilyNumber * 100) + ?R]
*QUESTION 2 *FORM
Name of
1:*?Person: *ALPHA 77L35 *SAVE Name
*SQLADD Count FamilyMemberNumber,Name \
"SELECT PanelMemberNumber,Name \
FROM TestPanelmembers"
*ENDREP
*QUESTION 6
That's all
*END
|
