Webinar: "Pivot!" Enhance your Dynamic Schedule Queries With Function Fields

Date Recorded: Sep 5, 2019
Presenter: Kevin Holt

In this webinar recording, learn about using function fields to produce dynamic reports showing scheduling information, just in time for back to school!

Script: Function Fields for Academic Schedule Data

Here is Kevin's script from the webinar in case you find it helpful. It includes several links to the queries he references. 

WHAT IS A FUNCTION FIELD?

  • Function fields provide a way for Axiom users to display customizable aggregate data. Conceptually, they are similar to calculated fields, though with the added ability to change the input of the calculation directly in the field’s parameters.
    • In a Find Classes query, you could pull in a calculated field called "Class List"
      • This gives you a list of the students classes sure, but what if I wanted to see the room too? The class ID? Limit which types of enrollments were displayed?
    • Calculated fields are great, but function fields are better...
  • Simply put, function fields allow you to run a query within a query...
  • This is why I want to talk about them today... It's tough to find the right time to talk about these
    • When you are a brand new school you are trying just to learn how to query.... and when you've been a user for a while, you may already rely on calculated fields and haven't felt the need to update your reports...

Introduction to Schedule Function Fields

  • Schedule function fields aggregate information related to class enrollments and schedules.
  • The schedule function fields can be used to show which classes students are enrolled in for the whole year, for certain grading periods, which sports they are playing and when, etc.
  • The schedule-related function fields can help determine when students and teachers are busy or free throughout the week.
  • Before Function Fields, you were relying on standard reports, that were tied to the scheduling reference date. Or you were pulling a report that displayed multiple rows for each student

Student Schedule Function Fields 101

  • I want to start by showing you a Find Class Enrollment query for one student
  • Here you will see a row for each enrollment record...
  • But, what if I just want to be able to see one row per student, showing their name and the list of classes they have. If I run a find class enrollments query, I get multiple rows per enrollment...
    • One row, two columns (Name & Enrollments)
  • Starting from a Find Student Query - Bring in the Student Schedule Function Field
  • Here you will see a number of parameters that could be set - We will come back to this - For now, leave the parameters open. For cleanliness, I would at least say that the grading period should be S1 and ALL
  • In the Display Options field, we can pull in which data points should be displayed in our field!
  • Run Query

Student Schedule Function Fields 201

  • Now let's take this a step further
  • You see that we have a number of parameters, like block, that could be set for which enrollments should be displayed in the field. We can use this to our advantage to transform what you typically pull from a Find Class Enrollments query into a display you may set in excel through a Pivot Table function.
  • This type of query will also allow you to easily identify free periods from a high level.
  • Starting from the Same Find Student query - Let's look back at our first Student Schedule Function Field.
  • Click on the field and expand the Blocks parameter - Check off Block 1
  • Highlight the field description under the "Properties" tab in the top right and rename this field to Block 1
  • Hover over the gear icon and select "Duplicate Selected Field"
  • On this field, expand the Blocks Parameter - Check off Block 2
  • Rename field to Block 2
  • Repeat one more time for Block 3
  • You now have a row per student and a column for each block
  • If the field is blank the student is free
    • Another reason why Function Fields are so great, they will also show you the absence of data!
  • I always like to go to the style tab and set "Highlight if not populated" to raise additional awareness to the free period
  • Final Product

Teacher Schedule Function Fields

  • Similar to the Student Schedule Function Field, we can use the Teacher Schedule Function Field to see a list of classes a teacher teaches during a particular grading period, day or block.
  • Starting from the a Find Staff/Faculty query - Bring in the Teacher Schedule Function Field
  • This field is different from the Student Schedule field in that it requires that a Role is specified in the parameters. Meaning should this only pull in classes where the teacher is the primary teacher? Additional Teacher? etc?
  • After indicating the role, expand the Blocks parameter - Check off Block 1
  • Now similarly to the Student Schedule query we just worked on, we could build out a field for each block. Giving us a high level display of each block a teacher teaches/doesn't teach.
  • But this time, I want to discuss another way this field can be used.
  • What if I wanted to see a very precise view of who teaches block 1?
  • After setting up my function field, I will flip my filtering from "begins with" to "is not blank"
  • Run query
  • Similarly, what if I wanted to see who exactly is free during block 1?
  • Back on our query design, I'd just flip my filtering to "is blank"
  • Final Product

Conclusion

  • After reviewing both function fields, I hope the wheels are starting to turn and you and find applicable uses for both fields
  • As function fields are designed to allow them to be configured in a way that is specific to your school (meaning respecting your exact grading period and block config) I hope you will continue to explore the use of these two fields as you move through the add/drop period and into the school year.

Next Webinar

Access the next webinar on attendance function fields.

  • If you found these fields helpful, you may really enjoy learning about the next set of function fields that I will discuss next week!
  • I will review the attendance function fields so that you can begin constructing precise reports that may answer the following questions:
    • How many absences has X student received?
    • Who has been tardy to class 3 or more times this grading period?
    • Who has been tardy to class 3 or more times AND absent 5 or more times?