Getting to Know Calculated Fields

Calculated FieldsYou’ve probably heard of Calculated Fields in Aspen but you might not be too familiar with what they are or how they work. And even if you know the basics you might not be aware of how you can customize or create Calculated Fields to meet the needs of your district. This article is intended to give you that information. Keep reading for a brief overview followed by two real-world examples that will get you on your way.

Overview

Calculated Fields in Aspen provide a way to store a value on a field based on values from one or more related fields. When any of those related fields, often referred to as trigger fields, are changed then the calculated field will be updated appropriately (the related fields “trigger” a change to the calculated field).

The easiest way to explain this is with an example: the Name view field on the Student table displays values like “Smith, John” or “Brown, Suzy” which are based on the First name and Last name fields on the related Person table. The name view is automatically updated when either the first or last name is changed. This is all accomplished with a Calculated Field.

Calculated Fields are managed in the District view under the Admin tab, Data Dictionary > Calculated Fields side-tab. Calculated Fields have either an expression or a procedure that “does the work” (more on that in a minute) and one or more children trigger fields (officially called Calculated Field Members). Once a Calculated Field is defined, it is assigned to one or more fields in the Data Dictionary. From that point on, whenever a trigger field is changed, the Calculated Field is invoked and all the assigned fields in the Data Dictionary are updated.

Whoa, that’s already a lot to digest and there’s more to come! Don’t worry if some of the details aren’t making sense, the examples will help. But before we get to those we need to first talk about mechanics.

Working with Calculated Fields

The Calculated Field user interface (UI) is lacking in Aspen. Although you can create and edit a Calculated Field, there’s no way to manage its children trigger fields (Calculated Field Member records). And there’s no way to delete either a Calculated Field or a Calculated Field Member except via Global Lists. The only real way to create Calculated Fields and their trigger fields is via a custom procedure. Fortunately the customizations team at Follett Software has done the work for us (thank you Derek Leadbetter!). Before you do anything serious with Calculated Fields you’ll need to download the bundle for the Calculated Field (Create/Update) procedure from the wiki and install it in your district. Once you do you’ll have a new menu option on the Admin tab, Data Dictionary > Calculated Fields side-tab.

Procedure on Options menu

As we mentioned earlier, there are two ways that a Calculated Field can calculate its value (i.e., “do the work”): via an expression or a procedure. A Calculated Field expression works the exact same way as a Mass Update expression. Check Aspen’s online help for details on how to work with expressions (search for the topic “Advanced Mass Update with an Expression”). The first example below will use an expression.

Alternatively, a Calculated Field can use a custom procedure which implements the com.follett.fsc.core.k12.business.CalculatedFieldProcedure interface. Creating your own custom procedure is beyond the scope of this tutorial. The second example below will use a procedure but no edits are required. As you might guess, it’s easier to work with expressions but a procedure can be much more powerful.

After you create a Calculated Field, you must then assign it to one or more fields in the Data Dictionary. Once again, the UI is lacking and there’s no way to do this on the default template for a Data Dictionary field. You’ll need to either add the Calculated Field > Name property to the default template or add it to a field set (my recommendation).

Modify List to Assign Calculated Fields

The Calculated Field’s expression or its procedure must be compatible with the Data Dictionary tables and fields to which it’s assigned. What does that mean? Take a look at the Calculated Field “Person Name View” – its expression is “{person.lastName}, {person.firstName}”. That Calculated Field is assigned to multiple Data Dictionary tables and fields. Two examples are the Name view field on the Staff table and the Name view field on the Student table. The expression works for both fields because both Staff and Student are related to the Person table.

Two more things to note regarding the Data Dictionary:

  1. You must reload the Data Dictionary after assigning a Calculated Field
  2. Any Data Dictionary field linked to a Calculated Field is treated as read-only, regardless of how the actual “Read-only” flag is set (which makes sense because those fields are calculated, right?)

Now you have a Calculated Field, you’ve assigned it to one or more Data Dictionary fields, and reloaded the Data Dictionary. Going forward any changes you make to a trigger field will update the calculated values. But what about all those records that already exist – how do you calculate the values for all of them? Time to “Recalculate All References”! Go to the details page for your Calculated Field and select “Recalculate All References” from the Options menu. This will update every record for each table that contains a field linked to your Calculated Field. As you might imagine, this operation can take a loooooong time to complete. Imagine recalculating the “Person Name View” Calculated Field. That would update every record in the Staff table, Student table, Contact table, User table… lots and lots of records! Use this option only when you really need to do so.

Since we’re on the topic of performance, what’s the overall impact of using Calculated Fields? In general (aside from the “Recalculate All References” option) the answer is “negligible.” You won’t notice any additional time when saving an individual record. You might notice an increase in save times when editing multiple records via Modify List or Mass Update. One point to make is that a Calculated Field based on a procedure incurs more overhead than one using an expression and will take slightly longer to complete. Again, you won’t notice that when saving an individual record but it might become evident during a Mass Update.

Example 1: Person Name View with Middle Initial

Many districts would like to include the middle initial in the “Person Name View” Calculated Field. Their first instinct is to change the expression to something like this:

{person.lastName}, {person.firstName} {PADR(person.middleName,1,’ ’)}

Unfortunately they’re greeted with a validation error on the Calculated Fields > Details page. Once again the UI is lacking, the error regarding the “Procedure ID” field is unnecessary and shouldn’t happen. Instead you can edit the expression on the Calculated Fields list via Modify List (quicker) or edit the default template to fix the problem with “Procedure ID” (more involved).

After the expression is finally updated, districts will “Recalculate All References” and everything looks great…. until someone changes the middle name for a Student and the name view doesn’t recalculate! Why? It’s because editing the expression is only half the equation. The middle name field must also be added as a trigger field. Use the Calculated Field (Create/Update) procedure to update the existing “Person Name View” Calculated Field and add the middle name field:

Person Name View with New Trigger Field

Example 2: Student Contact Mailing Flags

This example is actually the reason for writing this article. A district asked the following question on the mailing list (paraphrased):

We want to keep the various “receives mailing” flags synchronized on the Student Contact table. Specifically, we want the “Receives email” flag to automatically set the three “Receives XYZ mailing” flags (for grades, conduct, and other) to the same value. Our district doesn’t distinguish between these values but Aspen relies on the separate flags for different operations. Can this synchronization be done with a Calculated Field? If so, how?

The first answer is “Yes” and the second answer is via the steps below. Before going through the details though, let’s start with an overview:

  • We need a new Calculated Field
  • It will have a single trigger field for “Receives email”
  • It will be based on a procedure rather than an expression for two reasons:
    • This particular Calculated Field is updating a field on the same table that contains the trigger field; an expression-based Calculated Field would result in an infinite loop when trying to save the Student Contact record
    • This particular Calculated Field needs to update three fields on the Student Contact table; an expression-based Calculated Field can only update one field on a particular table
  • The Calculated Field will be assigned to a single “Receives XYZ mailing” field on the Student Contact record (the procedure will take care of the other two fields)

And now the steps:

1. Download and import the “Receive Mailing Calculator” Bundle

2. Use the Calculated Field (Create/Update) procedure to create a new Calculated Field with the following values:

  • Name: Receive Mailings
  • Description: Contact “receive mailing” flags
  • Procedure: Receive Mailing Calculated (imported in step 1)
  • Data table: STUDENT_CONTACT
  • Data fields: receiveEmailIndicator

"Receive Mailings" Calculated Field

3. Update the following fields on the Student Contact table in the Data Dictionary:

  • Assign the freshly created “Receive Mailings” Calculated Field to the Receive conduct mailing
  • Set the Receive grade mailing and Receive other mailing fields to read-only

"Receive Mailings" Data Dictionary setup

4. Reload the Data Dictionary

Note: This is a good time to test your setup. Edit the “Receives email” field on a Student Contact record and make sure the results you get are what you were expecting.

5. Go to the details page for the “Receive Mailings” Calculated Field and select “Recalculate All References” from the Options menu

Conclusion

That’s it! Calculated Fields in less than 2000 words. Not too bad, right? Let me know how your district has used (or wanted to use) Calculated Fields in the comments.

9 thoughts on “Getting to Know Calculated Fields

  1. Mark Ordonez

    Great post. The Calculated Field (Create/Update) bundle is exactly what I’ve been looking for! As soon as I find some free time, you had better believe I’ll be making use of this in several places.

  2. gvargasdc

    I was trying to use this to show the locker combination on the student portal. I have two questions:

    1. After adding the “Calculated Field > Name” to the field set and the template and making sure is list editable, I still cannot change the value of this field on the Data Dictionary for the Data Dictionary field I want to associate with my newly created calculated field. It comes as read-only. I am the admin of the site. Is this field protected somehow?

    2. In the Add/Update procedure, you can set multiple trigger fields within one table, but is there a way to set two trigger fields from two separate tables? In my case that would be the locker field in the student table and the combination field on the locker table.

    1. Brian Ciccolo Post author

      1. You will need to make the “Related calculation object identifier” (fddClcOID) list editable.

      2. You can have trigger fields from two different tables but the procedure will only let you pick from one table at a time. So if you want one trigger field from Student and another from School Locker then you can run the procedure twice. One point to note (which you may already know) – since there’s no direct relationship between Student and School Locker you will definitely need to base your calculated field on a procedure rather than an expression.

      1. Headsup

        Where would I find the “Related calculation object identifier”? I’m on the Student table (filter to All Records), and I see Related organization object id, Related person object identifier and similar, but not the one you outlined.

        I’m also unable to update the Calc Field > Name field in my field set or template.. Any help would be appreciated!

  3. gvargasdc

    In 5.2 the calculatedField procedure was slightly updated. The example procedure in this post will not work after updating to 5.2 You will need to slightly update the procedure. The change is minor: change the word “ModelBroker” to “X2Broker” in each method declaration to make it compatible with 5.2 .

  4. aspennewby

    Great explanation. I created a calculated field for proof of concept, but now the business owner does not want to go that route. How do I remove it?

    Thanks!

    1. Brian Ciccolo Post author

      You can remove the Calculated Field from your user-defined field (undo Step 3 from Example 2), save your changes, and then reload the Data Dictionary. If you want to delete the actual Calculated Field then you’ll need to do that from Global Lists.

Leave a Reply