Teachers tab in pohnpei schools disabled

[Ghislain Hachey]

‌

[Brian Lewis]

The teachers tab shows info from the most recent census for the school. If oyu look in Surveys, you’ll see that surveys for Pohnpei for 2022 have been loaded. However, in teachers list, there are no teachers for Pohnpei in 2022. Hence, the Teachers tab is disabled.

Not sure how the survey got created without loading the teachers…. however, you can downloaded the workbook from the Surveys tab form which these surveys were loaded. When you open it, you’ll see there are broken links, and this has been caused probably by a bulk copy-paste - you have to be careful to choose the option to ‘Use the names defined in the current workbook’ when doing this:

So I would go back to this workbook, recreate the teacher pages again taking care to protect the existing names, and reload. once the teachers data for 2022 is there ( ie teacher data for the latest survey) the reported problem will go away.

‌

[Brian Lewis]

not really a bug

[Ghislain Hachey]

If that is the case how come we had all expected teachers (in total count) for this year?

[Ghislain Hachey]

I'm not convinced this should be resolved. The whole workbook loads without errors but fails to load teachers. This is after having loaded all the data into a fresh workbook from the FedEMIS rollover.

I have narrowed down the problem to three duplicate teachers. The stored proc [pSurveyOps].[censusValidateStaff] has validation for this yet it did not pick it up possibly because something was different (date of birth?!) In either case, after removing all three duplicates teachers loads so a minor refinement is needed in the following code for this to work correcly and not fail silently.

INSERT INTO @validations
(
    field
    , errorValue
    , valMsg
    , severity
    , NumRows
    , FirstRow
    , LastRow
)
SELECT
'Duplicate Row'
, first_name + ' ' + last_name
, 'duplicate must be removed before loading'
, 'Information'
,  count(*)
, min(rowIndex)
, max(rowIndex)
From @ndoeStaff ndoe
WHERE employment_status = 'Active'
GROUP BY school_No
, first_name
, last_name
, inDate_of_Birth
, Job_Title
HAVING count(*) > 1


-- different school, but the same teacher
INSERT INTO @validations
(
    field
    , errorValue
    , valMsg
    , severity
    , NumRows
    , FirstRow
    , LastRow
)
SELECT
'Multiple Teacher Record'
, first_name + ' ' + last_name
, 'duplicate row must be resolved before loading'
, 'Information'
,  count(*)
, min(rowIndex)
, max(rowIndex)
From @ndoeStaff ndoe
WHERE employment_status = 'Active'
GROUP BY first_name
, last_name
, inDate_of_Birth
, isnull(FSM_SSN,'')
HAVING count(*) > 1

‌

[Ghislain Hachey]

Could be that #1235 is also related

[Brian Lewis]

fix(census): better etsting for duplicate teachers in Validate phase

Resolves #1168

→ <<cset 2131311ba72d>>

[Brian Lewis]

The issue is a mismatch on how dups are reported in the validate proc, and how dups are identified in the upload.

These changes to the SP mean that dups are captured in the Validate. Silent fail in process is alos fixed (but should not fail now anyway since validation has caught the dup already)