Refactor citizenship in Census Workbooks [2020:H]

[Brian Lewis]

Citizenship is recorded for staff members is in the census workbook, and stored on TeacherSurvey (tchCitizenship).

The list of available values is hard-coded into the workbook. The selected value is recorded as a a literal value( ie not translated into a code).

Proposed changes:

1. Derive the list of citizenship codes from lkpNationality. ie in the census workbook, this is now a databound list
2. Store the code from lkpNationality onto TeacherSurvey, rather than the literal description.
3. By convention, in lkpNationality, where the nationality is a specific country, the code will be the two character ISO code for that country, Where the “nationality” is a group (“Other Pacific Islander”, “other”) use a 3-character code so as to definitively avoid collisions .
4. Set up lkpNationality as required in FSM and RMI masters.
5. Change the Staff upload stored proc to update teacher survey with the code, not the description.
6. UPDATE existing records to translate. Note that there is currently some erroneous values in here ( typos in older code sets
7. Add referential integrity constraint on tchCitizenship?
8. Add support to edit lkpNationality through the tables feature in Pacific EMIS.

Seems like a lot to do for minimal effect! Long term benefits are brings all versions of the workbook closer ( by making another column data-bound) ; allows end user to manage this list via the UI in a consistent way.

Part of the work towards #603

[Brian Lewis]

At time of writing , these are the values in lkpNationality in live FEDEMIS:

codeCode codeDescription
CHK FMS (Chuuk)
YAP FMS (Yap)
PNI FMS (Pohnpei)
KSA FMS (Kosrae)
ASI Asia
AUS Australia
BEL Belgium
BRA Brazil
CAN Canada
GIL Gilberts
FIN Finland
IDN Indonesia
JPN Japan
LAT Latin America
NOR Norway
PAK Pakistan
PLW Palau
PHL Philippines
ROU Romania
RUS Russia
VCT St.Vincent/Grenadines
USA USA
VNM Viet Nam
ZMB Zambia
O Other

these are the values in the workbook:

FSM
China
Fiji
India
Japan
Kiribati
Other
Palau
Phillipines
RMI
South Korea
Taiwan
USA

These are the values that appear in the TeacherSurvey table:

USA
Palau
U.S
Philippines
FSM
Gilberts
Phillipines
Russia
Japan
Philippine
Other

[Brian Lewis]

Points 1 -4 above are handled in the script uploaded in Pull Request #324

as at time of writing this sciprt has not been executed in miemis production. Best to check that this has no implication on reporting if tchCitizenship becomes a lookup code, not a string literal.

This field is not yet displayed in the EMIS UI so no issue there.

[Brian Lewis]

Re 8. above - Nationalities is already available through table edit.

[Brian Lewis]

Current MIEMIS lkpNationality still not updated - would prefer to use ISO codes for RMI and FSM in both places, as per the script in #324?

Workbook citizenship list is bound to the table lkpNationality now.

[Ghislain Hachey]

Agreed. I also think using ISO codes is better. Should probably even be preloaded in the system by default. FSM would however loose their state choices which they are used to? Are you suggesting we allow for those custom “nationalities”?

Regarding RMI/FSM there is no reporting on those yet so safe to go ahead with this improved design approach I think.

So remaining is 5-7 if I understand this correctly? Would you rather we bite the bullet and do this change right away?

[Brian Lewis]

feat(census): reorganise staff nationality codes (lkpNationality)

Resolves #606

→ <<cset 6cb07c73045b>>

[Brian Lewis]

The 'fix script ' has been extended to load all ISO 3166 codes. However only a subset of these are shown in the work: these are ones that are grouped by region ( codeSeq < 900)

and any others that appear in tchCitizenship in TeacherSurvey:

-- nationality codes
Select codeCode Code
, codeDescription Nationality
FROM lkpNationality
WHERE codeSeq < 900
OR codeCode in 
(   Select tchCitizenship 
    from TeacherSurvey 
    WHERE tchCitizenship is not null
)
ORDER BY codeSeq

So this ensures that the code used for any staff member will be in the list after rollover.

The ‘fix’ has been applied to FSM and RMI. data, including the addition of foreign key on tchCitizenship

[Brian Lewis]

feat(census): updated census workbook

Resolves #684 #799 #924 #606 #968 #969 #939 #798 #660

→ <<cset babf8e18c3e4>>