OnSIS Staff Assignments to PS Import

OnSIS Educator Assignments xTrx to PS Import

NOTE: No file content is uploaded to any server in this utility - all processing is completed within the browser session
The purpose of this utility is to allow import of the OnSIS Extract for Educator Assignments to be transformed to a PowerSchool Import file.
You need two exports from PowerSchool to create the joins. The OnSIS files comes with an MEN identifier, but PowerSchool doesn't allow import using that key.

The MEN has to be mapped to schoolstaff.dcid

S_ON_USR_X has usersdcid and men

SCHOOLSTAFF has dcid and usersdcid

Both files have to be imported into this utility along with the OnSIS Educator file

Optional Scripts to get information directly from the Database:
select MEN, usersDCID, u.lastfirst from s_on_usr_x x inner join users u on x.usersdcid=u.dcid where not MEN is null order by MEN

select dcid, schoolID, users_dcid from schoolstaff where users_dcid in (select usersDCID from s_on_usr_x where not MEN is null)

Once processed, check for errors in the bottom box (missing staff in PS, missing school staff record)

To import, save the output box as a .tab file and use PowerSchool's District -> Functions -> Special Functions -> Importing & Exporting ->Data Import Manager -> S_ON_SSF_EDUCATOR_ASSIGN_C table
Check for existing assignments:
select s.abbreviation Sch
    , u.lastfirst Educator
    , c.*
from S_ON_SSF_EDUCATOR_ASSIGN_C  c
inner join schoolstaff ss on c.schoolstaffdcid=ss.dcid
inner join users u on ss.users_dcid=u.dcid
inner join schools s on s.school_number=ss.schoolid
order by Sch, Educator

(Sample Processable data preloaded)

S_ON_USR_X

Tab delimited, MEN & usersDCID **(Optional LastFirst from Query Above for Convenience) Load Content from a File or Paste into the box above (Note: Large files take up to 10 seconds to load and process)

SCHOOLSTAFF

Tab delimited, dcid & schoolid & users_dcid Load Content from a File or Paste into the box above (Note: Large files take up to 10 seconds to load and process)

OnSIS Extract #9

(School Educator Assignment)

"H1","School Educator Assignment    ","1","2020-2021","June Elementary School                                      ","2021/12/17","09:22"
"H2","123456","School Number A                                             ","02"
"DT",111111111,"V  ","   ","   ","      ",0.12,"000011156 ","2020/08/31","          ","ADM                                    ","N  ","F","              ","T","   ","M","                                                  "
"DT",222222222,"TEA","OTH","   ","      ",1.00,"000011017 ","2020/08/31","          ","                                       ","N/A","F","              ","T","   ","F","                                                  "
"DT",333333333,"LTO","   ","   ","      ",0.12,"000010739 ","2020/09/14","          ","                                       ","T  ","T","              ","T","   ","F","                                                  "
"DT",444444444,"TEA","   ","   ","      ",1.00,"10841     ","2015/09/01","          ","                                       ","T  ","F","              ","T","   ","F","                                                  "
"DT",555555555,"TEA","   ","   ","      ",1.00,"10577     ","2014/09/01","          ","                                       ","T  ","T","              ","T","   ","F","                                                  "
"H2","234567","School Number B                                             ","02"
"DT",666666666,"TEA","   ","   ","      ",1.00,"000010565 ","2019/09/03","          ","                                       ","T  ","F","              ","T","   ","F","                                                  "
"DT",777777777,"V  ","   ","   ","      ",0.12,"000011156 ","2020/08/31","          ","ADM                                    ","N  ","F","              ","T","   ","M","                                                  "
"DT",888888888,"TEA","   ","   ","      ",1.00,"001234567 ","2019/10/15","          ","                                       ","T  ","F","              ","T","   ","F","                                                  "
"DT",999999999,"TEA","   ","   ","      ",1.00,"000011575 ","2020/08/31","          ","                                       ","T  ","T","              ","T","   ","M","                                                  "
"DT",000000000,"LTO","   ","   ","      ",0.12,"000010739 ","2020/09/14","          ","                                       ","T  ","T","              ","T","   ","F","                                                  "

Load Content from a File or Paste into the box above (Note: Large files take up to 10 seconds to load and process)