Advanced lists displaying all class enrollments on a single row

Brian LeBlanc

Hi all,

One of the most useful advanced lists I've discovered is the one that lists a student's parents all on a single row instead of having multiple rows of the same student, using the Ordinal Numbers of the Parent/Child Relationship to filter. I was wondering if there's a way to do the same thing with class enrollments, where instead of having (for example) 1st through 8th periods on multiple rows, they could all be listed horizontally next to the student's name.

(@Bryan Lorenzo @john ronan @Brian Gray, you guys are the advanced list gurus…any ideas?)

Chris Goolsby

@Brian LeBlanc

Hi Brian,

Could you use this:

Academics / Reports / Schedule Reports / Student Reports / Student Block Schedule (HIDE)

I use this as a master student schedule.

Thanks!

Chris

Brian Gray

@Brian LeBlanc - It might be possible to do in some schools, but I've never tried to do that with our class schedules.

It would not work well for at my school, because we have a rotating schedule (so what class a student has first period changes day to day).

Scott Chrysler

@Brian Gray - Is possible to set a filter so that you only output one day? I was at a school that did something similar to what your school does, but every Monday, all classes met in block (A,B,C… or 1,2,3…) order. Whenever we needed a list with all of the classes a student was enrolled in, we would pull a Monday schedule. Just a thought. I defer to your Advanced List wisdom, Have a good weekend.

Scott

Brian LeBlanc

@Chris Goolsby That would work for a master schedule, but I'm trying to go one step beyond that and create a list that only displays students who are present on a given day. We're trying to create an easy way to run a report that will show everyone who needs to be accounted for in the event of a lockdown or evacuation. My theory is that we could sort that list by the class period when the lockdown/evacuation occurs, and we would have a ready-to-go list of who we need to check off.

We have an 8-period day; all eight periods meet on M/T/F and W/Th are A/B days. A student's “first period” class doesn't change over the course of the week, but the length of the class period can vary.

I won't be surprised if it isn't possible; I kind of knew that it was a shot in the dark. I know it's possible to create a list filtered by class period, and that's my backup plan if I can't get this to work.

Brian Gray

@Brian LeBlanc - Given the context of what you're looking for, I suggest you take a look at the Accountability module at Ruvna.com. They integrate directly with the SIS to pull rosters for classes and athletics and syncs attendance information every 30 minutes.

During an emergency, all employees have access to any roster, and can check in any student employee. It's an impressive service.

For the cost, it's not worth it to me to try to re-invent it.

Megan Morris

@Brian LeBlanc - I spent some time trying to build the same thing in advanced lists, and wasn't able to. It works in the flattened “student with parents” list because there is an ordinal indicator that you can use AND you can add multiple “student-parent relationship” objects, but I don't think that you can really do it in Advanced Lists because you're not able to add multiple “Course Enrollment”, “Course Group”, or “Course Base” objects that you could then add object filters to (i.e., “Course period = 1” for “Course Base 1”, “Course period = 2” for “Course Base 2”).

As a workaround, I have exported an advanced list of students with all of their enrollments by period for the semester (so I end up with 7 rows for each students, since we have 7 periods), and then used excel formulas to flatten the class enrollments. (Given your purpose for trying to build this list, it probably doesn't make sense for you - but I will share here in case it helps somebody else!). I then copy and paste the values into a new sheet and de-dupe the list to end up with a flattened list of students with enrollments. Definitely a bit more time consuming than being able to do it directly in Advanced Lists, but not awful!

You can find some excel tutorials online with formulas you can adapt, but here's a simplified version of what I do. From my exported data, I have these columns (among others):

• Student ID in column F
• Period in column J
• Course name in column M

Then, in column P, I add the header “Period 1”. Starting in cell P2, I use this array formula:

{=INDEX($M:$M,MATCH(1,(P$1=$J:$J)*($F2=$F:$F),0))}

• To get the array formula to work, you have to hit “ctrl+shift+enter” (you don't actually tupe the curly brackets)
• What this formula is basically doing is telling Excel to return the value in the “Course name” column when the student ID matches the the student ID in that row AND the period matches the column header (in this case, period 1)
• I then use that same formula in columns for all of our other periods, and I end up with flattened schedules for our students.

But it sure would be great if we could have multiple course base objects in one advanced list so that we could build a flattened list! If anybody has figured out a way to do that, would also love to know.

Lucien Piekut

@Brian LeBlanc

Hi Brian, you can do this by essentially dragging in a separate block per class – then filtering that block. Then you reference the field from that block.

Need to have very good naming conventions to keep your “ducks in a row” but it's pretty quick once you understand that each block is essentially doing a “left outer join”.

Megan Morris

@Lucien Piekut
Have you successfully been able to add multiple course base objects to one advanced list? If so, would love to see a screenshot of your object setup. When I've tried, I haven't been able to add multiple course enrollments/course base object groups in order to add the filtering by the different blocks. (The course base objects end up linked under each other, and the object filters then don't work correctly for me - I end up with no results.)

Brian LeBlanc

@Lucien Piekut I'm running into the same problem as @Megan Morris and yes, a screenshot would be quite helpful. Are you dragging in those blocks per class in academics or is it part of the advanced list object selector? I think I see where you're going, but I'm not quite sure how to get there.

John Ronan

@Brian LeBlanc I use perl scripts that I have written for this. One would aggregate many rows [for a given key value] into a single row; another does the opposite.

I've shared it here before. Let me know if you want it.

Amanda Lietman

@Brian LeBlanc would you mind sharing how you get parents in one row in advanced lists? With the ordinal number for relationship.

Brian LeBlanc

@Amanda Lietman It's an advanced list template - go to Reporting>Manage basic and advanced lists, then click List Templates at the top. Template category is Constituent Information, and the list you want is “Students with Parents (Flattened)”.

Amanda Lietman

@Brian LeBlanc Thank you!