The CRF determines two things:
1) Which teachers can see which individual students when they log in
2) What kind of aggregations the teachers can see
You need one line in the CRF spreadsheet for each Teacher/Student combo to allow that teacher to see that student. The "key columns" are the State Student ID (MEDMS Number) and the Teacher ID (FirstInitial+LastName). You need a unique Class Name for each aggregation of student data you want each teacher to see. Class Name is only used for aggregate data. The Principal (and/or other designated administrators) automtically has access to all these student and groupings.
You need one line in the SPF for each student who belongs to a special aggregate grouping the principal wants to see. The SPF specifies special aggregate groupings for the Administrators only. You need a unique "Special Pogram Name" attached to each student who belongs in that group.
-----
To take the example of Fran's multi-age class, suppose we have 4th graders "Student One" and "Student Two" being taught by Phillips and Raedel respectively, and 5th graders "Student Three" and "Student four" being taught by Phillips and Raedel respectively. You start with the four lines needed with the student/teacher combo:
Student One - Phillips
Student Two - Raedel
Student Three - Phillips
Student Four - Raedel
If all the teachers cared about for aggregate data was how their own multi-age class as a whole did, all you need to do is add the two Class Names and you're done:
Student One - Phillips - Mrs Phillips Class
Student Two - Raedel - Mrs Raedels Class
Student Three - Phillips - Mrs Phillips Class
Student Four - Raedel - Mrs Raedels Class
If, however, they also want to see aggregations of "How did MY 4th graders do overall?", "How did MY 5th graders do overall?", "How did ALL the 4th graders do overall?", and "How did ALL the 5th graders do overall?", you then need to make class groupings that allow both teachers to see all possible combinations:
(divide each teacher's class into two sub-classes)
Student One - Phillips - Mrs Phillips 4th Grade
Student Two - Raedel - Mrs Raedels 4th Grade
Student Three - Phillips - Mrs Phillips 5th Grade
Student Four - Raedel - Mrs Raedels 5th Grade
(group all by grade with Phillips allowed to see)
Student One - Phillips - All 4th Grade
Student Two - Phillips - All 4th Grade
Student Three - Phillips - All 5th Grade
Student Four - Phillips - All 5th Grade
(group all by grade with Raedel allowed to see)
Student One - Raedel - All 4th Grade
Student Two - Raedel - All 4th Grade
Student Three - Raedel - All 5th Grade
Student Four - Raedel - All 5th Grade
1: Access the NWEA site (http://www.nwea.org) and locate the "Class Roster File" template (CRFTemplate.xls ) and the "Special Programs File" template (SPFTemplate.xls). These may change locations as the website changes, but should be in the Member Support section as part of the Implementation directions and should not require logging in. At the time of writing they are located at
http://www.nwea.org/support/implementation/step3.asp
2: The templates must be opened in Microsoft Excel. One worksheet is the actual template with layout of data columns and the format of sample data shown. All other worksheets (containing instructions and hints) must be deleted before uploading. On the template worksheet, all rows except the column headers must be deleted before uploading, but the sample rows may remain as a guide while populating the spreadsheet and be deleted at the end. The data must be entered in the order and in the format shown. Excel formulas may be used to reformat existing data if necessary. The data fields at the time of writing are below. All the starred fields can be exported from Infinite Campus and pasted in. All the rest can simply be given a single entry in the top record and then use "fill down" to populate the column.
CLASS ROSTER FILE
Term Name
Beginning Instructional Date (1st day of school)
Total Number of Instructional Days (175)
Testing Window Start Date
Testing Window End Date
School Name
School Number (MEDMS school ID)
Previous Teacher ID (leave blank)
Teacher ID (first initial last name)
*Teacher Last Name
Teacher First Name (use first initial)
Teacher Middle Initial (leave blank)
Class Name (use teacher ID)
Class Period Name (all)
Class Subject Name (leave blank)
Previous Student ID (leave blank)
*Student ID (MEDMS number)
*Student Last Name
*Student First Name
Student Middle Initial (leave blank unless you have two students with the same name in the same grade)
*Student Date of Birth
*Student Gender
*Student Grade
*Student Ethnic Group Name
*Student Language Preference Name
SPEICAL PROGRAMS FILE
Term Name
*Student ID (MEDMS number)
*Student Last Name
*Student First Name
Student Middle Initial (leave blank unless you have two students with the same name in the same grade)
Special Program Name
In addition to the *'d fields, the following data fields should be downloaded for the Special Programs File:
Free Lunch Eligibility
Title1A
Title1C
Title3
Chapter 504
Gifted/Talented
SPED
---------------
SPECIFIC INSTRUCTIONS FOR EXPORTING FROM FMP AND CREATING CRF AND SPF
Export the information from Infinite Campus
Open the exported file in Excel, as well as the CRFTemplate and the SPFTemplate
In the CRF and the SPF, enter the desired Term Name in the top blank row
In the CRF, enter the date of the first day of school in the top blank row. If needed, use Format->Cells->Date to force MM/DD/YYYY format.
In the CRF, enter the number of student days in the school year in the top blank row
In the CRF, enter the testing window start date in the top blank row. If needed, use Format->Cells->Date to force MM/DD/YYYY format.
In the CRF, enter the testing window end date in the top blank row. If needed, use Format->Cells->Date to force MM/DD/YYYY format.
In the CRF, enter the desired school name in the top blank row
In the CRF, enter the MEDMS school code in the top blank row
In the CRF, leave the Previous Teacher ID blank
In the Export file, sort the data by Grade and then by Teacher
In the Export file, insert a new column and enter the teacher's first initial and Edit->Fill->Down for each teacher
In the Export file, insert a new column and for each teacher enter a formula to construct their Teacher ID with First-Initial-Last-Name and Edit->Fill->Down for all
Example: Last Name is in column C, First Initial is in column B, and blank column is in column A, enter in cell A1 and then fill down:
=B1&C1
In the Export file, copy the new column and paste it into the CRF file with Edit->PasteSpecial->Values
In the CRF, enter the Previous Teacher ID and Edit->Fill->Down for each teacher who is changing IDs (if any)
In the Export file, copy the teacher last name and paste it into the CRF
In the Export file, copy the teacher first initial and paste it into the CRF (as first name)
In the CRF, leave teacher middle initial blank
In the CRF, enter the desired class name and Edit->Fill->Down for each class
In the CRF, enter the desired class period name and Edit->Fill->Down for each class
In the CRF, leave the class subject name blank
In the CRF, leave the previous student ID blank
In the Export file, copy the student IDs and paste them into the CRF and the SPF
In the CRF, enter the previous student ID for any student that is changing IDs
In the Export file, copy the student last names and paste them into the CRF and the SPF
In the Export file, copy the student first names and paste them into the CRF and the SPF
In the CRF and the SPF, leave the student middle initial blank unless needed.
In the Export file, copy the student birth dates and paste them into the CRF. If needed, use Format->Cells->Date to force MM/DD/YYYY format.
In the Export file, copy the student genders and paste them into the CRF.
In the Export file, copy the student grades and paste them into the CRF.
In the Export file, leave the student ethnicities and languages blank.
In the CRF, Edit->Fill->Down all of the single items to the bottom of the names
In the CRF, copy/paste blocks of student records and change teacher and class data as needed to create additional class groupings (i.e. a group of students shared by more than one teacher)
In the CRF, remove all blank lines and template data except column headings
Save and close the CRF
In the Export file, create a formula in a blank column that constructs the special programs
Example:
SchoolLunch, Title1AFlag, Title1CFlag, Title3Flag, Chapter504StatusFlag, GiftedTalentedFlag, and SpecialEdFlag are in columns L-R, and column S is blank. In S1, enter and Fill Down:
=(IF(L1="None", "", "FRL ") & IF(M1="N","","T1A ") & IF(N1="N","","T1C ") & IF(O1="N","","T3 ") & IF(P1="N","","504 ") & IF(Q1="N","","GT ") & IF(R1="N","","SPED "))
In the Export file, copy the new column and Edit->PasteSpecial->Values it into the SPF
In the CRF, Edit->Fill->Down all of the single items to the bottom of the names
In the SPF, remove all blank lines and template data except column headings
In the SPF, sort by Special Program and delete all student data rows that are not in a special program
In the SPF, duplicate all student data rows that are in multiple special programs and separate the program descriptions manually
Save and close the SPF
1) Which teachers can see which individual students when they log in
2) What kind of aggregations the teachers can see
You need one line in the CRF spreadsheet for each Teacher/Student combo to allow that teacher to see that student. The "key columns" are the State Student ID (MEDMS Number) and the Teacher ID (FirstInitial+LastName). You need a unique Class Name for each aggregation of student data you want each teacher to see. Class Name is only used for aggregate data. The Principal (and/or other designated administrators) automtically has access to all these student and groupings.
You need one line in the SPF for each student who belongs to a special aggregate grouping the principal wants to see. The SPF specifies special aggregate groupings for the Administrators only. You need a unique "Special Pogram Name" attached to each student who belongs in that group.
-----
To take the example of Fran's multi-age class, suppose we have 4th graders "Student One" and "Student Two" being taught by Phillips and Raedel respectively, and 5th graders "Student Three" and "Student four" being taught by Phillips and Raedel respectively. You start with the four lines needed with the student/teacher combo:
Student One - Phillips
Student Two - Raedel
Student Three - Phillips
Student Four - Raedel
If all the teachers cared about for aggregate data was how their own multi-age class as a whole did, all you need to do is add the two Class Names and you're done:
Student One - Phillips - Mrs Phillips Class
Student Two - Raedel - Mrs Raedels Class
Student Three - Phillips - Mrs Phillips Class
Student Four - Raedel - Mrs Raedels Class
If, however, they also want to see aggregations of "How did MY 4th graders do overall?", "How did MY 5th graders do overall?", "How did ALL the 4th graders do overall?", and "How did ALL the 5th graders do overall?", you then need to make class groupings that allow both teachers to see all possible combinations:
(divide each teacher's class into two sub-classes)
Student One - Phillips - Mrs Phillips 4th Grade
Student Two - Raedel - Mrs Raedels 4th Grade
Student Three - Phillips - Mrs Phillips 5th Grade
Student Four - Raedel - Mrs Raedels 5th Grade
(group all by grade with Phillips allowed to see)
Student One - Phillips - All 4th Grade
Student Two - Phillips - All 4th Grade
Student Three - Phillips - All 5th Grade
Student Four - Phillips - All 5th Grade
(group all by grade with Raedel allowed to see)
Student One - Raedel - All 4th Grade
Student Two - Raedel - All 4th Grade
Student Three - Raedel - All 5th Grade
Student Four - Raedel - All 5th Grade
1: Access the NWEA site (http://www.nwea.org) and locate the "Class Roster File" template (CRFTemplate.xls ) and the "Special Programs File" template (SPFTemplate.xls). These may change locations as the website changes, but should be in the Member Support section as part of the Implementation directions and should not require logging in. At the time of writing they are located at
http://www.nwea.org/support/implementation/step3.asp
2: The templates must be opened in Microsoft Excel. One worksheet is the actual template with layout of data columns and the format of sample data shown. All other worksheets (containing instructions and hints) must be deleted before uploading. On the template worksheet, all rows except the column headers must be deleted before uploading, but the sample rows may remain as a guide while populating the spreadsheet and be deleted at the end. The data must be entered in the order and in the format shown. Excel formulas may be used to reformat existing data if necessary. The data fields at the time of writing are below. All the starred fields can be exported from Infinite Campus and pasted in. All the rest can simply be given a single entry in the top record and then use "fill down" to populate the column.
CLASS ROSTER FILE
Term Name
Beginning Instructional Date (1st day of school)
Total Number of Instructional Days (175)
Testing Window Start Date
Testing Window End Date
School Name
School Number (MEDMS school ID)
Previous Teacher ID (leave blank)
Teacher ID (first initial last name)
*Teacher Last Name
Teacher First Name (use first initial)
Teacher Middle Initial (leave blank)
Class Name (use teacher ID)
Class Period Name (all)
Class Subject Name (leave blank)
Previous Student ID (leave blank)
*Student ID (MEDMS number)
*Student Last Name
*Student First Name
Student Middle Initial (leave blank unless you have two students with the same name in the same grade)
*Student Date of Birth
*Student Gender
*Student Grade
*Student Ethnic Group Name
*Student Language Preference Name
SPEICAL PROGRAMS FILE
Term Name
*Student ID (MEDMS number)
*Student Last Name
*Student First Name
Student Middle Initial (leave blank unless you have two students with the same name in the same grade)
Special Program Name
In addition to the *'d fields, the following data fields should be downloaded for the Special Programs File:
Free Lunch Eligibility
Title1A
Title1C
Title3
Chapter 504
Gifted/Talented
SPED
---------------
SPECIFIC INSTRUCTIONS FOR EXPORTING FROM FMP AND CREATING CRF AND SPF
Export the information from Infinite Campus
Open the exported file in Excel, as well as the CRFTemplate and the SPFTemplate
In the CRF and the SPF, enter the desired Term Name in the top blank row
In the CRF, enter the date of the first day of school in the top blank row. If needed, use Format->Cells->Date to force MM/DD/YYYY format.
In the CRF, enter the number of student days in the school year in the top blank row
In the CRF, enter the testing window start date in the top blank row. If needed, use Format->Cells->Date to force MM/DD/YYYY format.
In the CRF, enter the testing window end date in the top blank row. If needed, use Format->Cells->Date to force MM/DD/YYYY format.
In the CRF, enter the desired school name in the top blank row
In the CRF, enter the MEDMS school code in the top blank row
In the CRF, leave the Previous Teacher ID blank
In the Export file, sort the data by Grade and then by Teacher
In the Export file, insert a new column and enter the teacher's first initial and Edit->Fill->Down for each teacher
In the Export file, insert a new column and for each teacher enter a formula to construct their Teacher ID with First-Initial-Last-Name and Edit->Fill->Down for all
Example: Last Name is in column C, First Initial is in column B, and blank column is in column A, enter in cell A1 and then fill down:
=B1&C1
In the Export file, copy the new column and paste it into the CRF file with Edit->PasteSpecial->Values
In the CRF, enter the Previous Teacher ID and Edit->Fill->Down for each teacher who is changing IDs (if any)
In the Export file, copy the teacher last name and paste it into the CRF
In the Export file, copy the teacher first initial and paste it into the CRF (as first name)
In the CRF, leave teacher middle initial blank
In the CRF, enter the desired class name and Edit->Fill->Down for each class
In the CRF, enter the desired class period name and Edit->Fill->Down for each class
In the CRF, leave the class subject name blank
In the CRF, leave the previous student ID blank
In the Export file, copy the student IDs and paste them into the CRF and the SPF
In the CRF, enter the previous student ID for any student that is changing IDs
In the Export file, copy the student last names and paste them into the CRF and the SPF
In the Export file, copy the student first names and paste them into the CRF and the SPF
In the CRF and the SPF, leave the student middle initial blank unless needed.
In the Export file, copy the student birth dates and paste them into the CRF. If needed, use Format->Cells->Date to force MM/DD/YYYY format.
In the Export file, copy the student genders and paste them into the CRF.
In the Export file, copy the student grades and paste them into the CRF.
In the Export file, leave the student ethnicities and languages blank.
In the CRF, Edit->Fill->Down all of the single items to the bottom of the names
In the CRF, copy/paste blocks of student records and change teacher and class data as needed to create additional class groupings (i.e. a group of students shared by more than one teacher)
In the CRF, remove all blank lines and template data except column headings
Save and close the CRF
In the Export file, create a formula in a blank column that constructs the special programs
Example:
SchoolLunch, Title1AFlag, Title1CFlag, Title3Flag, Chapter504StatusFlag, GiftedTalentedFlag, and SpecialEdFlag are in columns L-R, and column S is blank. In S1, enter and Fill Down:
=(IF(L1="None", "", "FRL ") & IF(M1="N","","T1A ") & IF(N1="N","","T1C ") & IF(O1="N","","T3 ") & IF(P1="N","","504 ") & IF(Q1="N","","GT ") & IF(R1="N","","SPED "))
In the Export file, copy the new column and Edit->PasteSpecial->Values it into the SPF
In the CRF, Edit->Fill->Down all of the single items to the bottom of the names
In the SPF, remove all blank lines and template data except column headings
In the SPF, sort by Special Program and delete all student data rows that are not in a special program
In the SPF, duplicate all student data rows that are in multiple special programs and separate the program descriptions manually
Save and close the SPF