Finding the table for a custom field

Before contacts many schools had added custom fields for their phone/email data entry. Even after contacts a lot of schools have other custom fields they want so they can use them to filter off of. When you have this we make a custom plugin for you with your custom fields and we ask you where those fields are. Most people go to the student field list and send us what they find there, however that doesn't give us the whole story.

PowerSchool lists custom student fields by Group.Field. A query doesn't know or care about the group (in fact that's just a PowerSchool thing) it needs the Table.Field. Why display it that way then? We could go on a rant about why groups are a good idea but group.field is a terrible and useless idea... but we'll hold off for now.

What if I made my own fields and I made the table name match the group name?

If you made your fields and you know for sure that the table name matches the group name then just send us that, no need to dig. We might ask you to confirm but you can just let us know that what you sent is the table name.

Do I need to look up all tables from fields I didn't make?

No. While the list of "safe" groups is not carved in stone, there are some we can tell you to help you skip the line.

Groups that we know the table name:

Any of the "corefields" groups such as StudentsCoreFields, UsersCoreFields
Any of the state groups. State groups always start with S_[stateabbreviation]_
Activities

Are there groups that are always wrong?

Yes, the U_STUDENTS_EXTENSION group is a giant red flag. Most of the time the table will be U_DEF_EXT_STUDENTS but it could also be U_DEF_EXT_STUDENTS0, U_DEF_EXT_STUDENTS1, ... U_DEF_EXT_STUDENTS999. Because it's not 100% consistent we have to have you look it up to confirm which table contains the field you want to use. We've even had customers with custom fields in 9 different U_DEF_EXT_STUDENTS tables

How do I find the table name?

There are 2 fairly easy ways of finding the table name

Data Export Manager (DEM) - requires you have access to custom table in DEM, if not try the other option
1. Navigate to DEM. If you need help getting there you can find direction in the article Getting To Data Export Manager (DEM)
2. For Category select Database Extensions
3. Export From is going to list all the DB extensions you have including default ones that come with PowerSchool and state reporting tables. Your custom table likely starts with U_. If it doesn't then it's a table that wasn't user created. Since you don't know the table you'll have to do a little guessing.
4. You can confirm that the table is correct as the field will show up in the list under the table's name. If the field isn't in that table change the table in the Export From until you find it.
Manage Database Extensions
1. Find the Group.Field name by clicking View Fields List. Note the part before the period, that's the group. If there is no group then it's not a custom table and you can move on.
2. On the left side menu click on System
3. Click on Page and Data Management
4. Click on Manage Database Extensions
5. This page takes a minute to load. Once it's done loading it should default you to "Students" in the first drop down. If this is not a student based field change that, otherwise leave that drop down alone.
6. Change Basic Extension to Advanced Extension
7. Click Next
8. You are now presented with a list of groups that have anything to do with the core table you selected above. Select the extension that matches what you found earlier.
9. Click Next
10. Step 3 shows you all the tables within the selected group that link to the core table from Step 1. You may see more than one table here. If you only see 1 table then that's the table you want. If you see more than 1 table you may need to try several to find out. In Step 4 we'll confirm where the field is.
11. Choose a table and click Next
12. Step 4 shows you the fields in the chosen table. If the field you are looking for is in this table then tell us that table for the field. If the field is not in the table you tried go back to Step 3 and choose a different table and check again.

I need to make custom fields, is there best practices?

Yes. If you go to the PSUGEvents conferences you can attend a class all about the ways to create good, sustainable custom fields and tables. We do have a slide deck for Jason Treadwell's version of the class we can share if you need, let us know.