Email address

[Powerschool3040]

Hi all,

Can anyone help me identifying the table where student email is getting stored in PowerSchool. I do not find it in students table and also when I try to export student data using "List students" I dont see a student email field. Any suggestions?

[CindyM8]

The student email field is not in the Students table so you won't find it in the field list. However, in PS 22.9.0.0 you can now use a PowerQuery DAT to export student email:

~(*powerquery;query=com.powerschool.core.dats.students.student_email)

For more information on other PQ DATs added in version 22.9 check out the release notes.

[rbrownstone88]

Hi CindyM8,

I'm using SQL Developer. Is there no way to extract the student email field via a SQL query? SQL Developer doesn't recognize U_Students_Extension as a table or view.

Thanks,

Rick

[nicolebond]

I use this PowerQuery DAT to export student emails and get a list of students who don't have an email:

^(*powerquery;query=com.powerschool.core.dats.students.student_email;no-rows-message=No Email)

[rbrownstone88]

Nicole,

I appreciate your reply, but:

1) I guess I'll have to research how to run a PowerQuery DAT. I don't know what that is.

2) The student email field must be stored in some table in the Powerschool Oracle database. I would like to know what table it is so that I can extract student email as a field in SQL queries that I write on my own. I use SQL Developer to create various queries on my own as I find it much faster and more flexible than using the tools built into Powerschool.

Best,

Rick

[rbrownstone88]

SOLVED: The student email field is in the  U_STUDENTSUSERFIELDS table. Here is a SQL query to extract it.

SELECT
s.student_number, s.last_name, s.first_name, s.grade_level, u.student_email
FROM

STUDENTS S
Left Outer Join U_STUDENTSUSERFIELDS U on s.dcid=u.STUDENTSDCID
ORDER BY s.grade_level, s.last_name, s.first_name

Rick

[sshinabarger]

I currently have a case open about why student email addresses are not syncing to Schoology.  I used the student field U_Students_Extension.STS_STUDENT_EMAIL.   and imported all the student emails into PS and they appeared in the student email spot on each student's demographic page.  Is there another field where the student email should be?  This seems like it could be the answer to this problem.  Thanks

[sshinabarger]

I am answering my own question.  YES - there is another place where email address must appear in Powerschool for syncing to work.  

The final step to make this work is that you must have the student email address in TWO places in powerschool so that it will sync to Schoology.

1.  You need the student google email address to appear on their demographics page.  The best way to do that is make a spreadsheet with all the students' google addresses in one column, and their corresponding powerschool number in another column.   The field names at the top of the columns should be Student_Number and  U_Students_Extension.STS_STUDENT_EMAIL      You must save the spreadsheet as TAB DELIMITED file type to import it.

Then you can use QUICK IMPORT and it will upload all the email addresses.

2.  Next, You must go through each student, open to the student email page, and click the COPY button so and it fills in the primary email from the demographics page to this page.    NOW, the next time you sync to Schoology,  the email address with sync.  These directions NEED to be added to the documentation page for setting up Google SSO for Schoology in the knowledge base..... 

[gunthea]

Hi! This might help, but most all of our database tables that start with "U_*" or "S_*" are customized database extensions which can be managed from the Page and Data Management module. 

We store our student emails in one of these custom database tables, but I believe the default table that PowerSchool uses to store student passwords is the EMAILADDRESS table. 

You might try a query like this to list student emails: 

SELECT

    S.STUDENT_NUMBER,

    S.LASTFIRST,

    EA.EMAILADDRESS
FROM STUDENTS S
JOIN PERSONEMAILADDRESSASSOC PEA on PEA.PERSONID = S.PERSON_ID

JOIN EMAILADDRESS EA on EA.EMAILADDRESSID = PEA.EMAILADDRESSID

That is the location where the email is stored for us, and altering in the frontend through the Email Configuration screen found at: admin/students/emailconfig.html also alters it at that database location.

Hope this helps!

[daviskat]

NEVERMIND! I see you do it through DIM. Please disregard this comment.

Is there a way to import to the field located at admin/students/emailconfig.html?