Explorations in Equation Engine

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).

The Data

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”.

Residency Data

The second term, 0330 (1998 Fall), is “Out of State”.

Residency Data Second Term

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' 

Residency Data from the Database

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

Creating a New Equation

After adding, I set the descriptions at the top of the page.

New Equation

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”

Setting the Equation Detail

To complete the change, we need to “compile”.  Set the “Equation Edit Function” to compile:

Compiling

When I compiled it, it gave me an “Incomplete MESSAGE found within the program on or before line 0000001. (14460,221) ” message.

Incomplete Message Error

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:

Correct Message Command

Testing

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.

Equation Test Data component

After clicking the Test button, you should see the “Hello, World” message in the Messages Logged grid:

Test Results

Adding Parameters

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.

EMPLID Parameter

To make sure it works, we need to add a message to display the ID.

Displaying the EMPLID Parameter

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”.

Adding parameter to test data page.

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.

Keys for SSF_RES_OFF_VW

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:

New Assign 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:

Find First

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.

Row Fpund Message

When I ran the test, you can see that the “Row Found” message says 0.  That means no row was found for term 0331.

Row not found results

 

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.

Correcting the Term

Don’t forget to compile.  Now, you should see that Row Found is 1.0000.

Row Found Message

 

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.

Adding the Residency Status field

Here’s what it looks like on the Test page:

Residency Status Results

 

Which Term?

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:

Removing the Term Value

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)

Missing Term Variable Message

 

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.

Changing Find First

 

Finally, we probably want to add into our message the term so we can see what it is finding.

Adding the Term to the Message

Don’t forget to compile.  And here is our result…

Result without the term

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.

Latest Term

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.

Residency Data from the Database

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.

Setting 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.

Loop Start

 

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:

If statement

 

Finally, at the end of the loop, we need to do a Find Next.

Find Next

After compiling, here is the result.  Notice it now has the latest term:

Updated Term

 

Conclusion

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.

Leave a Comment

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.