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.