This article is probably best suited for Network and Campus Office teams who need to generate lists and/or find specific data stored in PowerSchool that aren't accessible in reports (or aren't in a usable format - WHY MUST YOU PDF EVERYTHING, POWERSCHOOL!?!?!?!)
A few common applications for this include -
- Pulling a list of information about all of the students in a given class
- Pulling attendance records for a group of students or a given day
- Figuring out enrollment details from previous years
What's nice, is that you can easily pull data from multiple tables in PowerSchool after you select records you care about - that's the problem with "quick export," you only get data in the Students table, which admittedly is probably fine for most people.
Below I'll walk you through the steps for getting a list of all of the grades for a given term. It's a random sort of task, but the skills are usable for any need of getting data out.
Getting to the correct screen:
Once you're logged into your admin account to your instance of PowerSchool you can complete this task. AIM Instance | CICS Instance | Michigan Instance. Navigate to Setup>System>Direct Database Export (DDE)
Selecting a table:
The first thing to do is selecting the table where the information is that you need to access in this case we need to access stored grades so we need to look in the..."stored grades" table. Don't get too snarky, it's not always that straight forward.
Filtering:
Next we have to filter. There are 5,030,040 records in the CICS stored grades table as of 9/22/22! You can't work with that many rows (especially in a locally hosted instance of PowerSchool like the CICS instance). Note, this will require some familiarity with what lives in the fields of the tables. In this case, if we look at what fields exist in the dropdown on the "Search StoredGrades" table we see many available fields. As a pointer, we often want to filter information based on time (all records for all years are stored here, so...there are a lot in some tables), location, or person. In this case we're going to search for all of the final grades in Middle School Reading classes from last year at a given school.
Which variables you filter on are important, but the order you filter doesn't matter at all. Think of these tables as a giant spreadsheet with a filter on. If you filter out a certain year first, and then filter out the course title it's going to give you the same list as if you do it in the opposite order.
Let's first filter by TermID. TermID is a field that is pretty common in PowerSchool tables. Terms stem from the school year and it's corresponding YearID. In the SY2022-2023 School Year YearID is 32 and the TermID is 3200 (quarterly courses, and grades created in PowerTeacherPro may have a different 4th digit - Quarter 1 might be 3201, Q2 = 3202, etc.). You might be asking yourself, "why is SY2022-23" year 32, and the answer is, "I have no idea. Just memorize the pattern and don't ask so many questions."
We're going to look for data from SY2021-2022, so the TermID we're going to select is >= 3100. That's not a new angry emoticon, kids. That's greater than or equal to operator. It's so that it catches all TermCodes greater than (or equal to) 3100. Then I "Search within the current X# records only." This is important for the second filter on down, unless you want to start over.
Now, you might be saying, "won't that pull stored grades from every future year too, if it's greater than or equal to?" And the answer is "yes, you catch on quickly." Since records are only stored here at the end of a quarter, I'm in this sweetspot where I don't have to filter again for TermID<3200 (see what I did there?). It might take a long time to process with 5M records, so be patient. Go check your email, or ZenDesk or Happeo or whatever. It's not that bad, but you get it.
Once it loads after filtering out a lot of records, it should be faster from there. That narrowed the records from more than 5 Million to a measily 315,582. Since we don't care about all of the grades, we're going to filter them based on the "FINAL" grades. Now, you have to know that final grades are stored under a store code. You might have noticed store codes on the "Historical Grades" view of the Student pages. The Q1,Q2,S1,Y1 grades are all the StoreCode values. We store final grades in Y1 (or S1/S2 for .5 credit High School courses) in most cases. (Two quick notes - 1, it doesn't matter if you navigate away from DDE, when you go back it should still keep your progress unless your session times out, but I like to open in a new tab. 2, I don't know why it's called StoredGrades on the DDE and Historical Grades in the user interface.)
Filtering by StoreCode = Y1 and searching within the current selection only.
Now we're down to 37490 records and the data we have includes only records from SY2021-22, and only the records that were stored in the Y1 StoreCode.
Next, let's filter out only Reading grades. Course_Name = Reading search within current records.
Next we have to filter by School. Let's go with Prairie, because I used to teach at Prairie and it's awesome. You could search based on SchoolName OR SchoolID. All the cool kids use SchoolID. I think because it makes them feel superior and really they have low self esteem. SchoolID=4220. I'm going to spare you the screenshot, but remember to search within the current records only, otherwise you'll creep back up again. The resulting number is 297. From 5 million to under 300 in 4 filters!
Now we have something to work with.
Exporting:
We've gotten rid of all the stuff we don't want, so let's play around with what we have. You might have (or might not have) noticed the "List View" and "Table View" options in the Current Records in Selection box. Those links take you to magical places. Or really just a summary of a few pertient facts about the data (though on many tables it is unclear to me why they chose to include what they did.
In the case of Stored grades, it mostly makes sense (thought you're confirming much of the information that you just filtered (SchoolID, TermID and Course Information). You might notice the field StudentID here. That's a moniker that gets thrown around a lot, but in PowerSchool language, it is in no uncertain terms, not the number that we care about at all ever for any reason. In the CICS instance of PowerSchool, it looks shockingly similar to valid CPS ID numbers which are stored under Student_Number, which is the number we care about.
If you like you can go into individual records by clicking on the hyplerlinked field (first column after the index numbers) and view the record. It will have more details. Nichole (SIS Manager) likes that a lot. I (Director of Data and Analytics) am not fond of it, but see it's use. I'm anxious to get to exporting the data so I can work with it outside of PowerSchool. She's anxous to understand the data in her baby, PowerSchool. Different strokes. Since we're making a list and not going deep into the records, let's move to export.
When you click "Export Records" next to "Table View", it takes you to a screen where you can choose what fields to export and how you want the data to look. Just like in the filtering area, you can choose from fields that exist in that table and often that is enough.
Sometimes, though, you want fields that don't exist in that table, for example a teacher's email address or Student's Grade_Level or Student_Number. In those cases, you can actually reference another table in your export list. Be patient, because this isn't easy as you're starting, but it becomes second nature. You might (or again, might not) remember that when we chose what table we were exporting from that there were numbers in parentheses next to the name of the table. Those were not random.
You can use those numbers during export. For example, I want to have Student_Number, LastFirst, and Teacher's Email in the export and none of those values exist in the "StoredGrades" table. So...I just reference the table number before the field I want and put it in brackets. (ex: [1]Student_Number; [5]Email, etc.). It won't work with everything, as there has to be some unique identifier that exists to match the tables (In this case TeacherID and StudentID) are in both "StoredGrades" and "Students"/"Teachers," respectively. You also don't have to use the numbers if you forget which numbers go with which tables and don't want to go back. [Students]Student_Number will work just as well as using the table number. This is a game changer in exporting data from the SIS, because it eliminates the need to export different data from different tables separately and join them somehow (vlookup or Index/Match in Sheets or some database function).
A note on ID fields... here is one kicker about IDs and I can't begin to tell you why. When you're working in a table there is (almost?) always a field called "ID." That field is relevant to that table only. When referecing that field from another table you have to put the table name (or part of it) in the ID. For example, from the "Teachers" table, the field ID refers to a unique identifying number generated by the system when creating that record. If I try to reference that number from another table (either in filtering or exporting) I must call it TeacherID. Why don't they just call it TeacherID in the Teachers table too? I don't know. Maybe they were paying for character count?
Lastly, you just have to decide how you want to export the data (I never touch these, but I don't get weirded out when I get a tab delimited file or tsv. I just copy and paste it from my text reader to a spreadsheet). You can, though, export a CSV (thought sometimes you get jagged rows if there is a comma in the field you're exporting (like Comments on a report card). You can change the "Field Delimter" to comma if you like. I don't know why you'd want to change the record delimiter, but live your dream if you so choose to experiment and change it.
I'm guessing the audience for this article will want the header row and would want to leave the "Surround Fields" unchecked, but there are applications where you might want it checked. You can use the sorting features, but I find the export to run slowly enough. Again, at this point, play around with it, see what you can break. (Seriously though, don't break anything. Nichole already works hard enough.)
When you click submit, you should get a tsv (or csv if you're fancy) file that you can copy and paste into a spreadsheet and do all of the things with it.
I hope this was helpful!
Comments
0 comments
Please sign in to leave a comment.