PowerSchool Extract Tool: Changes needed to extract co-teachers from PowerSchool
NOTE: This will not work for Michigan PowerSchool clients as MI uses the teacher PIC in the PowerSchool extract scripts.
Extracting co-teacher information from PowerSchool requires changes to the three scripts listed below. The scripts are located in the c:\ps_illuminateed_exporter folder on the computer running the extract process.
Using a text editor like Notepad ++ or an editor that displays the current line number would be helpful when editing the scripts.
Before starting make a backup copy of your current users.sql, mastschd.sql and ie_export_script.sql files.
1) users.sql ->
- Insert a line below Line #75 which will become Line #76. On Line #76 paste the following:
INNER JOIN ps.sectionteacher st on t.id = st.teacherid
- Change line 77 ... change t.id to st.sectionid and change cc.teacherid to cc.sectionid
The updated version would then look similar to what is shown below:
--BEGIN additional data columns
--Acceptable fields:
--Teachers table(t). Custom teacher fields: ps_customfields.getTeachersCF(t.id, 'FIELDNAME')
--END additional data columns
FROM
(SELECT * FROM ps.teachers ORDER BY last_name, first_name) t
INNER JOIN ps.sectionteacher st on t.id = st.teacherid
INNER JOIN ps.cc cc ON(st.sectionid = cc.sectionid)
INNER JOIN ps.terms trm ON(cc.termid = trm.id)
AND (cc.schoolid = trm.schoolid)
WHERE
trm.YearID = &IE_YEAR_ID
/
SPOOL OFF
2) mastschd.sql ->
- Insert a line below Line #55 which will become Line #56. On Line #56 paste the following:
INNER JOIN sectionteacher st ON (s.id = st.sectionid)
- Change line #57 from this;
JOIN ps.teachers t ON t.id = s.teacher
To this;
INNER JOIN ps.teachers t ON t.id = st.teacherid
- Change line #44. Change the double ticks '' to what is shown below:
|| :TAB || CASE TO_CHAR( st.start_date, 'MM/dd/yyyy' ) WHEN '01/01/1900' THEN NULL ELSE TO_CHAR( st.start_date, 'MM/dd/yyyy' ) END
- Change line #45. Change the double ticks '' to what is shown below:
|| :TAB || CASE TO_CHAR( st.end_date, 'MM/dd/yyyy' ) WHEN '01/01/1900' THEN NULL ELSE TO_CHAR( st.end_date, 'MM/dd/yyyy' ) END
- Change line #46. Change the double ticks '' to what is shown below:
|| :TAB || CASE WHEN st.roleid = &IE_TEACHER_ROLE_ID THEN 1 ELSE 0 END
The updated version would then look similar to what is shown below:
|| :TAB || CASE TO_CHAR( st.start_date, 'MM/dd/yyyy' ) WHEN '01/01/1900' THEN NULL ELSE TO_CHAR( st.start_date, 'MM/dd/yyyy' ) END
|| :TAB || CASE TO_CHAR( st.end_date, 'MM/dd/yyyy' ) WHEN '01/01/1900' THEN NULL ELSE TO_CHAR( st.end_date, 'MM/dd/yyyy' ) END
|| :TAB || CASE WHEN st.roleid = &IE_TEACHER_ROLE_ID THEN 1 ELSE 0 END
|| :TAB || ''
|| :TAB || CASE WHEN s.EXCLUDE_ADA = 1 THEN 0 ELSE NULL END
--BEGIN additional data columns
--Acceptable fields:
--Terms table(t). Custom term fields: ps_customfields.getTermsCF(c.id, 'FIELDNAME')
--Section table(s). Custom section fields: ps_customfields.getSectionsCF(c.id, 'FIELDNAME')
--END additional data columns
FROM (SELECT * FROM ps.terms) trm
INNER JOIN sections s ON (trm.ID = s.TERMID and trm.schoolid = s.schoolid)
INNER JOIN sectionteacher st ON (s.id = st.sectionid)
INNER JOIN ps.teachers t ON t.id = st.teacherid
JOIN schools sc ON sc.school_number = s.schoolid
WHERE trm.yearid = &IE_YEAR_ID
/
SPOOL OFF
3) ie_export_script.sql ->
- Insert a line below Line #16 which will become Line #17. On Line #17 paste the following.
DEFINE IE_TEACHER_ROLE_ID = 21
Save all of the files.
Please sign in to leave a comment.
Comments
0 comments