I took a nice foray into Equation Engine the other day, and I don’t want to forget what I learned. On a demo instance, I want to go through a quick example of writing an Equation using Residency Data. We’ll try to grab the Residency status for a particular person (in state or out of state).
You can see the residency data at this navigation: Campus Community > Personal Information > Identification > Residency Data
I’ll be working with Empl ID FA0027, who has 2 terms of residency status. The first term, 0350 (1999 Spring), is “In State”.
The second term, 0330 (1998 Fall), is “Out of State”.
I picked this person to test with because she has two different terms with different status. Notice that the first term comes first.
SELECT EMPLID, ACAD_CAREER, INSTITUTION, EFFECTIVE_TERM, RESIDENCY_DT, RESIDENCY FROM PS_SSF_RES_OFF_VW WHERE EMPLID = 'FA0027'
Hello World Equation
First things first, let’s create a simple equation. We can go to the equation editor page and create a new equation.
Navigation: Set Up SACR > Common Definitions > Equation Engine > Equation Editor
After adding, I set the descriptions at the top of the page.
Now, we need to add the equation detail. We’ll start with a simple “Hello, World” example. Set the following fields on the first line:
- Keyword: “Message”
- Operand Type: “String”
- Operand: “Hello, World”
To complete the change, we need to “compile”. Set the “Equation Edit Function” to compile:
When I compiled it, it gave me an “Incomplete MESSAGE found within the program on or before line 0000001. (14460,221) ” message.
The problem was that I put all of the message command all on one line. The equation was expecting three lines. The “String” and message text should have been on line 2 and I needed an “End Message” keyword. This works:
PeopleSoft offers a nice utility for testing the equations. You can go to the Equation Test Data component.
Navigation: Set Up SACR > Common Definitions > Equation Engine > Equation Test Data
You simply search for the equation name that you were editing in the Equation Editor. If you jump directly from the editor, it will probably open up that equation without a search. Then, you simply click the big test button. For our current Hello, World example, you don’t need to enter any parameters.
After clicking the Test button, you should see the “Hello, World” message in the Messages Logged grid:
Next, I need to be able to give my equation a parameter. I need to give the Employee ID for which I want it to check the residency. So, at the bottom, I will define an “EMPLID” string parameter.
To make sure it works, we need to add a message to display the ID.
At this point, don’t forget to compile the equation to capture these new changes.
Now, on the Test Data page, we have to add the EMPLID parameter. I set it equal to “FA0027″.
Looking Up the Residency
Okay. Now it is time to actually make our equation do something. We need to look up the residency status from the SSF_RES_OFF_VW record/table. The first thing that we need to know is what the keys for the record are.
Next, we need to have a variable defined for each one of these key fields. We already have a global variable defined because it is a parameter to the program. Now, we need a variable defined for ACAD_CAREER, INSTITUTION, and EFFECTIVE_TERM. We can just make these local variables. We can use the Assign keyword to define and set these variables:
Note: I intentionally set the effective term to an invalid one just for testing. We’ll fix that in a minute.
Next, we need to find the row in the table. We can use a Find First keyword to do that. It looks like this:
The first parameter is the return result. The Find First will return either a 1 or a 0. A 1 means that the row was found in the table, and a 0 means that it was not found. I decided to capture that result in a local variable called “ROW_RESIDENCY_FOUND”.
The second parameter is the table that you are searching. For us, that is SSF_RES_OFF_VW.
Finally, the last parameters are the key fields on the record. Notice that EMPLID is set as a Global variable (Keyed Global Equal). For each of these parameters, we are saying to search where the field in the table matches the value of the variable of the same name.
Note: you do have to define all of the key fields. If you do not define one of them, you will get this message when you try to test it:
Find parm mismatch on parameter count in equation PSST_TEST near line 20. (14460,330)
Finally, we need to add a message keyword so we can see the result.
When I ran the test, you can see that the “Row Found” message says 0. That means no row was found for term 0331.
Finding the Row
If you remember, we entered the wrong term. That’s the reason that it didn’t find the row. So, let’s correct the term and set it to 0330.
Don’t forget to compile. Now, you should see that Row Found is 1.0000.
Loading the Value from the Table
So, we know whether a row was found for that term, but what if we want to know what the actual status is in the table? We can use the Table and Field Operand Types.
Here’s what it looks like on the Test page:
All of this assumes that we know the term that we want to look up. What if we don’t want to hard code the term but look up the latest status for the latest term?
We can remove the term setting. We still need the assign or it will complain about the variable not being defined. But, we can set the variable to blank:
Note: If you remove the whole Assign keyword for the EFFECTIVE_TERM, you’ll get this message:
Uninitialized local in equation PSST_TEST near line 20 is EFFECTIVE_TERM. (14460,320)
Next, make sure to change the Keyed Local Equal to Keyed Local Greater Equal. That way, that criteria will match any term greater than blank, which is pretty much any term.
Finally, we probably want to add into our message the term so we can see what it is finding.
Don’t forget to compile. And here is our result…
If you want to, you can even repeat the same steps for the Career and Institution. That way, you won’t have anything hard coded.
Here’s the rub: the 0330 term is not the latest term. The view is keyed such that the effective term should be sorted descending. Unfortunately, the database doesn’t pay any attention to the view’s keys. The Equation Engine doesn’t use an order by clause when doing the Find First. So, we are getting the first term rather than the last.
You could use a SQL keyword accomplish this such that a single row is returned with the latest term. Here’s an alternative. What if we look through the rows looking for the latest?
First, let’s capture the term and residency in their variables.
Next, I started a loop to loop through the rows of the table. When no more rows are found, we need to stop and exit the loop. If the “ROW_RESIDENCY_FOUND” variable is false, no rows exist and that is where we exit the loop.
Next, if we have a row, we need to evaluate the term. If the term is later than the one currently in the local variable, then we need to capture the current one in the row. That’s what this IF statement does:
Finally, at the end of the loop, we need to do a Find Next.
After compiling, here is the result. Notice it now has the latest term:
The Equation Engine is a pretty cool tool. It is a little finicky and takes some practice, but it will work in time. Please comment if you see anything wrong that I did or if you have any suggestions.