How Do I Validate Planning Form Data on Save with Groovy?

EPM Tech Tips · Planning · 11

Users save negative expenses, blow past a budget limit or leave a variance without its explanation, and someone finds out at review time. A Groovy rule set to run before save can check each of these and stop the save with a clear message on the cell. Most validation rules fail for the same few reasons: wrong timing, too many cells checked, or a comparison that treats #MISSING as zero. This guide walks you through the checks in order, with short snippets you can adapt.

Before you start

• Write down each rule in plain words first: which account, which cells, what limit, and whether a failure should block the save or only warn. Get the business to agree on that list.

• Pick your route. To block bad values cell by cell, use steps 3 and 5. To block a total, use step 4. Do steps 1, 2 and 6 in every case.

Step 1 · Is the rule a Groovy rule attached to Run Before Save?

What happens. Validation only works if the rule runs before the data is written. Run Before Save and Run After Load can only be selected for Groovy rules, not for calc scripts or graphical rules. Groovy rules need Planning on EPM Enterprise (or a legacy Enterprise subscription). The rule protects form saves only. Data loads, data maps, ad hoc submits and REST writes do not run form rules, so those paths need their own checks.

How to check.

1. In Calculation Manager, confirm the rule was created as a Groovy script, not a calc script.

2. Go to Navigator › Create and Manage › Forms, select the form, click Edit and open the Business Rules tab.

3. Add the rule and select Run Before Save. Oracle's own example also selects Run After Load, so errors show when the form opens. Leave Run After Save cleared for this rule.

Fix. Recreate the logic as a Groovy rule if it was built as a calc script, and attach it to Run Before Save on every form that writes to the accounts you are protecting.

Step 2 · Are you only checking the cells the user changed?

What happens. operation.grid is the grid being saved. If you loop over every cell, you flag old values the user never touched, and the rule gets slower on large forms. A second trap is that cell.data gives you a number even when the cell is #MISSING, so a blank cell can pass or fail a numeric test by accident.

How to check.

1. Open the script and find the iterator. A filter on edited cells looks like this:

operation.grid.dataCellIterator({ DataCell cell -> cell.edited }).each { DataCell cell ->
    // checks go here
}

2. Look for any numeric comparison that does not test cell.missing first.

Fix. Iterate only edited cells for cell-level checks, and test cell.missing before you compare cell.data.

Step 3 · Does a bad value get flagged on the cell?

What happens. addValidationError takes a color and a message. The cell turns that color, the message shows as its tooltip and in the grid validation messages, and the save does not go through.

How to check.

1. Inside the edited-cell loop, add a test for negative values on the account you care about:

if (cell.getMemberName("Account") == "Travel" && !cell.missing && cell.data < 0) {
    cell.addValidationError(0xFF0000, "Travel cannot be negative.")
}

2. Save the form with a negative value and confirm the cell turns red with the message.

Fix. Use one message per rule and say what the user must do. If the business only wants a warning, do the check in a rule set to Run After Load and set cell.bgColor = 0xFFFF00 there. The cell is highlighted when the form opens and the save is not blocked.

Step 4 · Does the total stay under the limit?

What happens. A limit on a total cannot be tested one cell at a time. Sum the cells first, then stop the whole save with throwVetoException if the sum is over the limit. The message comes from a message bundle, which also lets you add other languages later.

How to check.

1. Put the bundle at the top of the script and the total check after it:

def mb  = messageBundle(["val.travel.limit": "Travel on this form exceeds the limit of {0}."])
def mbl = messageBundleLoader(["en": mb])

double limit = 50000
double total = 0
operation.grid.dataCellIterator("Travel").each { DataCell cell ->
    if (!cell.missing) total += cell.data
}
if (total > limit) {
    throwVetoException(mbl, "val.travel.limit", limit)
}

2. Check what the iterator covers. dataCellIterator("Travel") sums every Travel cell on the grid, across all periods and entities in the rows and columns.

Fix. If the limit applies per entity or per period, build the sum per member inside the loop, keyed on cell.getMemberName("Entity") or cell.getMemberName("Period"). Keep the limit in the cube, for example on a driver account, rather than hard-coding it, so the business can change it without editing the rule.

Step 5 · Is the required comment there?

What happens. A common rule is that any change to a sensitive account needs an explanation. The simplest version keeps the comment in a text-type account on the same form, such as Travel Comment, so the rule can read it. crossDimCell moves from the edited cell to the cell with the same members except for the one you name.

How to check.

1. Confirm the comment account is on the form, in the same rows as the amounts.

2. Inside the edited-cell loop, add:

if (cell.getMemberName("Account") == "Travel" && !cell.missing) {
    DataCell note = cell.crossDimCell("Travel Comment")
    if (note == null || note.missing) {
        cell.addValidationError(0xFF0000, "Add a Travel Comment for this change.")
    }
}

3. Save a changed amount with the comment blank and confirm the amount cell is flagged.

Fix. If crossDimCell returns null, the comment member is not on the grid. Add it to the form layout. If the business wants a cell comment instead of a text account, cell.hasCellNote() tells you whether one exists, but not what it says.

Step 6 · Does it behave the same for real users?

Test with a planner's login, not only as an admin, and include a save with nothing changed. Check that the messages are readable, that one bad cell does not flag the whole row, and that the rule does not slow the save on the largest version of the form. Use println while you build the rule (the output appears in the rule's job details in the Jobs console) and remove it before go-live. Also test the form from Smart View if users enter data there.

Quick checklist

1. Groovy rule on Run Before Save · Form › Business Rules

2. Only edited cells, #MISSING handled · dataCellIterator filter and cell.missing

3. Cell-level errors with a clear message · addValidationError

4. Totals checked, save vetoed · throwVetoException with a message bundle

5. Required comment present on the grid · crossDimCell to the text account

6. Tested as a planner, in web and Smart View · user login

Pedro San Martín · Founder & Principal, Asher & Company