Staff certification disappear on rollover workbook in RMI [RMI24H]

[Newton Langidrik]

‌

[Ghislain Hachey]

Since this is only RMI I suspect this may have something to do with the customized lookup for the Highest Ed Certification (which is Highest Ed Qualification by default).

[Brian Lewis]

@Ghislain Hachey I suspect this is fixed using the current MEIMIS_Census.xlsx as you updated in issue 1336 Merged on 13 7 2023, some months after this report?

[Ghislain Hachey]

Possible, but MIEMIS is surely been upgraded since that PR. And if you rollover in production right now you do not get any teacher certification. All the online test website are very recent and you could try out this rollover for 2023.

https://miemis-test.pacific-emis.org/

I believe your user should be in there already.

[Ghislain Hachey]

So looking at the latest MEIMIS_Census.xlsx in branch develop and comparing the file with that on the production server it is identical (same SHA256 hash). Yet in production we loose all certification data. Perhaps it is my very commit you pointed out that fix something but broke that? Did you look into my suspicion?

Since this is only RMI I suspect this may have something to do with the customized lookup for the Highest Ed Certification (which is Highest Ed Qualification by default).

‌

[Brian Lewis]

It is not the rollover that is losing the data, it was the upload of 2023 data. The field tchEdQual was not populated in the TeacherSurvey records.

This field uses an Alias (HighEst Ed Certification) in place of Highest Ed Qualification.

‌

But this has changed over the years, looking at the source XML on TeacherSurvey we see which names were captured in the Upload:

2020: Highest Ed Qualification => tchEdQual

2021: Highest Ed Certification => tchEdQual

2022: Highest Ed Qualification =>tchEdQual

2023: (neither name record) =>TchEdQual non updated

‌

I have pushed the 2022 values for tchEdQual for each tID on to 2023, and now the Rollover works as expected .

But the question is now: will the rollover fail in the same way in 2024, and if so , why?

‌

‌

[Ghislain Hachey]

Yeah, so this does seem to related to my original suspicion.

I’ve been trying to get them to use the built-in rollover in RMI but they have kept the habit of building the thing from scratch every year possibly with slightly varying versions of the workbook. This process needs to be reinforced.

But also, with the column mapping it needs to be clear technically how to make sure that even if we localize a term (Highest Ed Certification (as oppose to Highest Ed Qualification) in a workbook column that the data will be handled the same way correctly.

[Ghislain Hachey]

But the question is now: will the rollover fail in the same way in 2024, and if so , why?

On this. We can easily test. Rollover and reload the workbook unchanged (no new kids). Reload it in the test system and we can observe tchEdQual on TeacherSurvey no?

‌

[Brian Lewis]

This is data related. As above after updating missing values for 2023 (from 2022), generating the workbook - > the values are in the Workbook

Uploading that Workbook → the values are in TeacherSurvey in tchEdQual

So yes it is critical to:

• use the current version of the workbook
• generate by using the rollover function

• make sur the=at the Census Columns lookup is set up correctly to make the translation. For RMI should look like this:

  

‌

You can backfill the missing data for year 2023 from 2022 like so:

‌

 begin transaction
  declare @T TABLE
  ( tID int,
  Q nvarchar(100)
  )

  INSERT INTO @T
  SElect tID, tchEDQual from TeacherSurvey WHERE ssID in (Select ssID from SchoolSurvey WHERE svyYEar = 2022)

  update TeacherSurvey
  SET tchEdQual = T.Q

  FROM TEacherSurvey
  INNER JOIN @T T
  ON T.tID = TeacherSurvey.tID
  WHERE ssID in (Select ssID from SchoolSurvey WHERE svyYEar = 2023)
rollback

‌

[Brian Lewis]

Verified as data issue