PS Custom Fields and SQL

When it comes to using PowerSchool custom fields in SQL, you must use what's called the Custom Fields API rather than just trying to reference the fields by their field names.  There is an exception and that's if you've migrated your custom fields to extended tables and in those cases you do not need to use the API.  Both instances are covered below, with how to do the non-API approach at the bottom of the page. 

To use the API method, the first thing one should do is download the PS_CustomFields API Guide from PowerSource.  This is a pdf that has been around since PS 6 and covers how to extract data from custom fields.  You should be able to find it in the same area where you'll find the Data Dictionaries for the version of PS you're using.

With any custom field, you cannot just simply reference the field name in the select clause like you can with built-in fields.  For example, the following would error out because motherdayphone and fatherdayphone are custom fields:

SELECT lastfirst, grade_level, motherdayphone, fatherdayphone
FROM students
WHERE enroll_status=0
ORDER BY lower(lastfirst)

If you're not sure which fields are custom and which are built-in, you can either look at a record from the table in DDE and all the built-in fields will have their own row versus falling under Custom, or you can look at the information for the table in the Data Dictionary and it will list the built-in fields for that table.  The way to pull custom field data is to use the following main command:

ps_customfields.getcf('table',id,'field')

The Custom Fields API guide goes into more detail about the main command and some special circumstances related to it.  For example, there are some tables where you may have to replace id with something else, storedgrades is a good example, and if your sql is based on more than one table you need to add table names or aliases to the id part (see below).  Using the command above, here is the proper syntax for pulling the motherdayphone and fatherdayphone information:

SELECT lastfirst, grade_level, ps_customfields.getcf('students',id,'motherdayphone'), ps_customfields.getcf('students',id,'fatherdayphone')
FROM students
WHERE enroll_status=0
ORDER BY lower(lastfirst)

The api command could also be used elsewhere.  For example, if you have an activity called Football and football is the field name, the following would pull student data for those students in that activity:

SELECT lastfirst, grade_level, ps_customfields.getcf('students',id,'motherdayphone'), ps_customfields.getcf('students',id,'fatherdayphone')
FROM students
WHERE enroll_status=0 and ps_customfields.getcf('students',id,'football')=1
ORDER BY lower(lastfirst)

In addition to the main command, there are five shortcut commands for pulling data from the Courses, Schools, Sections, Students, and Teachers tables.  The API Custom Fields guide has the five commands, but here's the one for the Students table:

ps_customfields.getStudentscf(id,'field')

Using the shortcut method, the sql above could be written this way too:

SELECT lastfirst, grade_level, ps_customfields.getStudentscf(id,'motherdayphone'), ps_customfields.getStudentscf(id,'fatherdayphone')
FROM students
WHERE enroll_status=0
ORDER BY lower(lastfirst)

One does not have to use the shortcuts because the main command will work with any table.  With the shortcuts though, remember there are only five of them, and be sure to reference the Custom Fields API guide for the syntax.  If the table you're trying to pull data from is not one of the five, then you must use the main command instead.


Data From Multiple Tables

If you're pulling data from more than one table, you need to make a reference to the table before the word id in the custom field reference.  For example, the following sql pulls student and school data:

SELECT s.lastfirst, s.grade_level, s.schoolid, ps_customfields.getstudentscf(s.id,'motherdayphone'), ps_customfields.getstudentscf(s.id,'fatherdayphone'), sch.abbreviation
FROM students s
INNER JOIN schools sch ON s.schoolid = sch.school_number
WHERE s.enroll_status=0
ORDER BY lower(s.lastfirst)

In the example above, id is replaced by s.id because s is the alias for the students table.  If you're not using a table alias, then you would use the table name instead.


Aliases

The following does not apply to sqlReports because field aliases aren't needed because of column heading section.  However, if you're using a SQL client, aliases will make the column heading shorter, and if you're embedding SQL into a custom page you may not get any output unless you're referencing the alias.  Using the above example, you could add mdphone and fdphone like so:

SELECT s.lastfirst, s.grade_level, s.schoolid, ps_customfields.getstudentscf(s.id,'motherdayphone') mdphone, ps_customfields.getstudentscf(s.id,'fatherdayphone') fdphone, sch.abbreviation
FROM students s
INNER JOIN schools sch ON s.schoolid = sch.school_number
WHERE s.enroll_status=0
ORDER BY lower(s.lastfirst)


Extended Database Method

If you've migrated your legacy custom fields to extended tables, then you do not need to use the custom fields api method.  What you do instead is make a join to the extended table and reference the field from the table.  Here's an example of how you would list out motherdayphone and fatherdayphone that are in the studentcorefields table:

SELECT s.lastfirst, s.grade_level, sc.motherdayphone, sc.fatherdayphone
FROM students s
LEFT OUTER JOIN studentcorefields sc on s.dcid = sc.studentsdcid
WHERE s.enroll_status=0
ORDER BY lower(s.lastfirst)

In the sql above, a left outer join is used to join the studentcorefields table to the students table in order to get all the students.  If an inner join had been used, only records with data in both tables would be returned, meaning any students without data in motherdayphone and fatherdayphone would have been left off.   Unlike legacy tables that typically use the student id field in joins, a join to an extended table is based on the student dcid field.

If you have data in the extended student table, then the above method is recommended because the difference in speed of the sql is substantial due to the fact the ps_customfields.getcf coding used to get data out of the Custom field in the Students table in not needed.  Also, the getcf coding only works with legacy custom fields.  If you create a new field that only exists in an extended table, then the getcf code will not work with it and you must join it's table into the sql instead.  Just be sure to join to the correct table.  For example, the studentcorefields has a specific list of fields that are part of it.  District created legacy fields are not in it, but are in other tables created by either the system if the mass migration is used, or tables created by users.

 

  Back to the List of Articles