Apple School Manager - Roster.csv help

[mbonifacio]

Hello,

We are preparing to upload our powerschool SIS information to Apple School Manager but the roster.csv file has got us confused.

What is the difference between a roster and a class? I spoke with our powerschool admin and she also could not see why both values would be needed.

I looked through the ASM template help files and support article but it is still not clear

Also, The support article says "student_id A person_id for each student, separated by a comma. " and then right below "Each line in the Roster must have a completely unique roster_id, and contain only one class_id and one person_id."

Does this mean each student will need a separate line or can the student_id field contain multiple person_ids separated by comma?

Can someone who has successfully done this upload provide us with some help?

Thanks!

[cdenesha · Accepted solution]

Please note this post by @jbutler135 as he is ahead of us all in determining actual PS fields. I hope to get into this tomorrow if the PS admin is here.

[strider_knh]

Here is what I have learned from our rep.

1) Courses
Course are the generic group: Algebra III - North High School

2) Classes
The specific class: Mr Smith Algebra III 5th period

3) Roster
Each student that attends each class: Roster ID - Class ID - Student ID
Since the Roster ID is unique, a student would have a row for each class they are taking. A student in high school may have 7-8 rows. A class may have 30 entries, one for each student.

Unlike the previous cvs files, think of these are more filling the tables of the relational database behind the scenes and not making entries in the database. If that makes sense.

Edit: Does not make full sense to me yet.
Edit Again: The support info on the Apple School Manager website its still for the old cvs files, the info has not been updated to the new method.

[DLarson]

We're stuck at about the same spot and have a call in to PowerSchool about both the roster and the classes file. Mvillafuerte or anyone, how did you create the classes file from PowerSchool? We're also anxious to see how to create the roster file.

[eoleksishina]

Hi there,

We're sitting here with Mark breaking our heads over why in the world we need this RosterID and what could we use as such. But before I go into our plan, here's a response from James Thomas (Apple rep) regarding our question on Roster file:

I got some more information regarding the upload CSV’s for ASM. In regards to the rosters.csv file, you do not have to upload information here. I was able to confirm that not all SIS systems include this info, if yours does not you need only remove the default information from the template before uploading. When doing this the header must remain on the file. In regards to the information shared between different CSV’s, if the field identifier matches, the information should also match. So in the case of Location ID or Person ID these should be the same for the records represented. I hope this answers your questions, however if you require more information please let me know and I’ll be sure to see what I can find.

Even though it seems like Roster ID is not required, it kind of is - judging from the file specifications and the template. In PowerSchool, the table containing Student-Classes links is called CC, and each record in there has an internal ID that's unique and used probably in a gazillion ERDs connecting who knows what to whatever else. That means, if we build the Roster file with [CC]ID as RosterID, we'll have this many unique RosterIDs:
[Number of Students] x [Number of Classes in each Student's schedule]
Hopefully that RosterID is not going to be used on anything interface-related as that stuff is going to be unreadable by Teachers - they're definitely not used to seeing internal IDs of anything.

The question still stands though: even if we can get a unique ID for each RosterID line, what purpose does it serve? It definitely does NOT connect students to classes, so what in the world DOES it do in Apple Universe?

[eoleksishina]

Unless Roster_Id is just a primary key for Rosters table - it's simply required (maybe even automatically assigned if imported blank, or something Roster table can't exist without in this system), but not used for any connections.

[mbonifacio]

@strider.knh - Thanks for that info. I have two template zip files from the ASM website now. An older templates.zip that only included 4 csv files and had all help info within the csv file itself. A more recent ASM_CSV_Templates.zip that has 6 csv files and a separate readme. Is there supposed to be a new method coming?

@DLarson - We have not yet exported a classes.csv file but we did find all the values needed from powerschool.

@eoleksishina - Thanks for all the help!

[cdenesha · Accepted solution]

Please note this post by @jbutler135 as he is ahead of us all in determining actual PS fields. I hope to get into this tomorrow if the PS admin is here.

[DLarson]

I don't have my classes.csv or roster.csv so I tried removing the data but leaving the headers in both and uploading as a zip file with all the other files and still no joy, received error that the two files were missing (even though they were there with headers and no data as indicated by the Apple rep above) so it still eludes me how to successfully upload without these being populated. I should have a valid classes.csv tomorrow but will still need to deal with roster.csv.

[hsekiv]

@mvillafuerte I've found this to be the current (updated) method
[https://support.apple.com/en-us/HT207029](link URL)

I used the "old" method a few weeks ago, and now seemed to have duplicated the students.

Edit: I realised why the students have been duplicated, it's because I altered the person_id field, rookie mistake. Now I have to wait 29 days for the initial entries to be deleted, All of them are being imported into JSS, so I'm creating an advanced search to filter out the old ones and work with the new ones.

[strider_knh · Accepted solution]

@mvillafuerte
The Roster_ID is the unique key for that table. The reason it is there is that the next time you want to make changes it gives ASM something to compare against. If that Roster_ID is in the old table but not the new table it is removed and that student is no longer in that class.

This will get confusing I think because you will need to make sure you use the same numbers each time or just expect the whole class list to be rebuilt each time.

These six cvs files is the new method and now requires the use of a SFTP site. We have not actually uploaded anything using the new method yet, still working on building the cvs files properly.

[mbonifacio]

@cdenesha - Thanks for that link! This was really helpful.

Thanks to everyone else for the links and helpful info! I think we are on the right track now and will be trying our first export next week.

[cdenesha]

@Wheelspinning How badly messed up are the Managed Apple IDs being created? Or are you not using them? What happened in the JSS for it to create new users when you had old ones with the same usernames? Did it change the username structure? Unless you are matching on Source System Identifier, and then I have no idea what it would do.

If you want to cleanup all the old users and retain the new, I have a cleanup suggestion for you if you are confident in your import matching.

In Casper delete all your Classes from your earlier imports. This will remove the dependencies for the old users and allow you to delete them.

Here's the big nugget I learned in another thread: when ASM syncs it only does automatic user updates for students who are in Classes. I suggest Importing your Classes at Mobile Devices -> Classes first. Then don't manually Import users on the User side at all but instead allow the ASM sync to create/match all users.

chris

[jbutler47]

I think the Roster_ID should be a user created entry, concatenated fields to build a unique name, that you can find later. When you look at the way information is shared amongst the CSV files, you notice some fields are not interconnected with the others.

I've been playing with a nomenclature as follows:

16-17_HPT-Davis-K-4HR-576-202-25343

or

SCHOOLYEAR_SCHOOLACRONYM_TEACHERLAST-TEACHERFIRSTINITIAL-COURSENUMBER-COURSEID-SECTIONID-SECTIONNUMBER

Probably will trim it up to:

SCHOOL_YEAR-SCHOOL_ACRONYM-TEACHER_LAST-TEACHER_FIRST_INITIAL-COURSE_NUMBER-COURSE_ID-SECTION_ID.

Looking like this: 16-17_HPT-Davis-K-4HR-576-202

Granted you have to a little extra work an export an extra field or two out of SIS.

Still a work in progress.

UPDATE: I updated my ASM to PS data flow Picto-gram, see below.

[jgwatson]

Whereabouts are you seeing the error message from ASM when you upload a file? I am uploading using cyberduck, and all I can see is error - but no reason why.

Thanks

[hsekiv]

@jgwatson I use cyberduck as well, the zip file has to go in the Dropbox folder. If there is an error after the upload, you'll get an email from ASM.

[hsekiv]

@cdenesha my account is actually very new, only activated in the past two weeks. I don't have any user data or classes setup. Don't even have any iPads setup yet but have 100 with DEP ready to setup once I get this part working. I've been mass deleting after a sync and then starting over for the time being. During the import, JSS shows the person_id field but then it doesn't show up for the students or anyone when I click on a contact.

The managed Apple IDs got screwed up in ASM, where each user has fullname@appleid.schoolname and fullname-1. The -1 users are the new ones via the new process and actually linked to classes and everything. I may need to manually change 650 original users apple ids to something bogus and then schedule them for deletion.

[cdenesha]

@jbutler135 Thanks again for your diagram!

Our PS person said that when a nightly export happens you cannot do a Where clause and that everything in the DB would be exported for those fields - including classes from previous years. Can you shed some light on how you'll be exporting? And do you expect to do some post processing of the data files?

thanks!

chris

[Olson]

Did my first upload today and got tons of error messages that the class_id in rosters.csv was not found in classes.csv.
Not for one particular line but every line in the rosters file. Yet, I can open each file and find various class_id's in both files. When I look at the formatting requirements it makes me wonder if I'm way off.
For example, here's the header and first line in rosters and classes.

roster_id,class_id,student_id
bbb3b58e-7d34-4700-92dd-6931148da8ea,500101,42200621

class_id,class_number,course_id,instructor_id,instructor_id_2,instructor_id_3,location_id
050501,050501,0505,ST100,,,100

Just reading https://support.apple.com/en-us/HT207029 and it says

"The Read Me file included with the templates says that values must be wrapped in quotation marks (“). This isn't necessary unless the value contains a comma (,) or semicolon (;)."

So maybe it's not formatting. I'm still not seeing why the class_id are not matching between rosters and classes.

Ugh

[cdenesha]

Your files look ok, you shouldn't need the quotes. Are you sure there is a class_id of 500101? I don't think it would be s leading zero issue.. did you try wrapping in quotes?

[lizmowens]

So let me ask this about the classes & roster and maybe one of you brilliant folks will have some insight: Do I REALLY have to list every class separately?

We are a K-8 school, so for K-5, I'm listing their one homeroom class (K1, 2A, etc). For middle school, can I just list their section? We split the kids in half, so we have 6-1, 6-2, 7-1, 7-2, etc. Kids in those sections go to the same classes together. Is there a reason I have to break it down into 6-1Math, 7-2English, etc?

I guess I'm not fully understanding the purpose of having to import all of this and how it will affect my students or my ability to manage devices. What am I missing?

[ktrojano]

You don't have import any of it if you don't want to or need to. We are just importing our students to create managed Apple IDs. When I spoke to an Apple Enterprise Support Engineer I found out that a # can be used to populate any required field in the csv file that you don't want to upload data for. So for example, our staff.csv file has the header row and then a # in row 2 for each of the required fields. The same is true for the rosters, courses, and classes files.

[lizmowens]

Seriously, @Ktrojano ? If that's true I am so darn thrilled. With all the time I spent on the phone with Apple tech support yesterday, that was never presented to me as an option. I am so glad to hear this. You just gave me back at least an hour of my life this afternoon. Gracias!

[Olson]

@cdenesha Yes, that's the odd thing I can open the two files and point to the class_id in each file. I'm going to bang away on it some more.

@Ktrojano That's a great tip with the #. Thanks!

[cdenesha]

Normally optional fields can be just empty, i.e. you'd see two commas in a row since there is no data. I did a test import where I did this for the password type and it wasn't rejected.

[Olson]

Finally got my students, staff and courses into ASM. I did get a suggestion that having my class_id and class_number the same value could cause the error. I changed the class_number this time it worked.

Oh yeah... importing it into the JSS. I nearly forgot. Tomorrow is another day. Thanks for everyone's help.

[cdenesha]

@Olson I thought I posted this last night... just take it one class at a time at first, from Mobile Devices -> Classes -> Import.

[hsekiv]

@cdenesha

Every time I go through the steps to import, it states "all classes have been imported"

I'm integrated ASM already, and ASM has all of the right classes setup

[cdenesha]

Are you running the JSS locally? Make a backup before you do the ASM sync with users.. All users associated with a class you've already imported will come in on sync.. and you may not have the ability to verify the user matching with existing users. Personally I would consider deleting the classes and re-importing... if it is a small number. :)

[hsekiv]

The JSS is hosted with an external provider, so maybe that's causing an issue?

I've only setup 2 classes for now as a test, and have tried deleting and re-importing already. I just got the import done correctly, finally, so really would like to avoid any re-importing.

Kind of on a time crunch so might just create the 30 classes manually.

[michael_devins]

@Wheelspinning With regard to your "all classes have been imported" message, that will be displayed if there are no remaining classes left to import. It will also be displayed if there are simply zero classes to import (i.e. Apple School Manager sync hasn't occurred yet, or it is still in progress).

Have you verified that your Apple School Manager sync has occurred at least one time? When the JSS "syncs" with Apple School Manager, it is actually downloading a full copy of your Apple School Manager dataset. It may be that you've setup Apple School Manager to sync but it just hasn't happened yet?

[bbelew]

Has anyone spoken with anyone at PowerSchool to confirm which fields they will be using with the integration with ASM?

The field list that was shared with me by our Apple SE is different than the one above, but requires fewer file modifications to create. That said my issue is person_id on the student and staff files are not unique using the ID field from PowerSchool.

[cdenesha]

I would like to know the exact field list that PowerSchool will be using in their Connector as well.

In my testing, a student with one person_id will become Inactive if they are uploaded with another person_id. Which makes sense because it is a different person according to the unique person_id.

If we choose the wrong fields for any of the unique IDs then we'll have a mess, inactive users unable to use their Managed Apple ID, classes and rosters invalid, etc. ('Oddly enough' if you then go back and use the original person_ids they do not become Active, you have to Restore them manually)

I raised the issue on Monday with my SE and with Apple Enterprise so we'll see what happens. I'm not deploying Apple School Manager until this is straightened out.

chris

[eoleksishina]

@cdenesha If person ID will affect who gets deactivated, I think the issue with [Teachers]ID is that in PowerSchool it's different for every single affiliation that the user has with a school. Teachers at two schools will have 2 IDs, but that's the ID that links sections and class enrollments to them. The only other ID would be in Users table for teachers that can maybe be linked to sections via teacher ID.

Our PS person said that when a nightly export happens you cannot do a Where clause and that everything in the DB would be exported for those fields - including classes from previous years.

How is he/she exporting the data? AutoSend? Because we have a ton of automated SQL-infused exports out of PowerSchool at any hour of day and night, and never had an issue with SQL filtering.

[joelande]

Anybody else having an issue with PowerSchool roster_id value to be in a format like x(y-z)?

That is how our period expressions are, and it appears that ASM import doesn't like it?
Even though the documentation just says it expects a string

[Olson]

Here's where I stand... I imported my .csv's into ASM, I synced with the JSS, then imported. I went in and cleaned up my Users in the JSS. I kept all new users created from the ASM import and deleted any matching users that existed before the import. I also had to update all the Users email fields to reflect their true email addresses, since the real email addresses don't have "appleid." jammed in the front of the domain name, and/or a -1 concatenated on the back of their username.

The Classes generated look pretty good. Now we have roster changes and I'm not sure what to do to have them reflected in the JSS. We've had one user account added and one user account updated directly in the ASM, where the name was Jane Doe, but should have been John Doe, we also updated the AppleID to reflect the name change. Beyond that, the only thing that's changed is the roster info. Do I use the same set of .csv's that I originally imported into ASM and just tack on the roster changes after the original roster records? Do I delete the all the original records in rosters.csv and just add the roster's changed records? Can I leave the Classes as is in the JSS, do a sync and import again?

[cdenesha]

@Olson ASM is expecting a full dump of current data. For example if you leave out some students they become Inactive. I'm not sure exactly what will happen for roster changes, but if you deleted them all it would probably wipe out your Classroom students as they would no longer be linked. I would simulate a full current PS dump by adding the new entries at the bottom, and by deleting the rosters that the student is now no longer taking.

Leave all as is in the JSS, and during the sync any student in those classes will automatically be updated. You only need to import again if you have new classes or students not in a class.

[RLR]

We've managed to upload all our csv files successfully and the teacher can see their classes in Apple Classroom. The teacher and students are correct but the display names of the classes (inside the app) are not. It seems the names are getting mixed up with something. I've checked Casper and the Apple School Manager portal and the name is correct in both places.

Anyone know what might be causing this?

I've also noticed that the Classes display name in Casper seems to use the coursesID from the classes csv file.

[rzoppi]

How are you guys getting this information out of powerschool? I was advised by PS that all of this can be done via an AutoSend scheduled Export. Is this true? If not what method are you using?