Adventures in Groovy – Part 10: Validating Form Data
Introduction
One of the huge frustrations I have with Planning is the fact that you haven’t been able to stop a user from saving data that didn’t validate since Smart View was released and Data Forms could be opened in Excel. Prior to that, the forms could be customized with JavaScript and the form save could be interrupted and cells flagged. Well, thanks to Groovy Calculations, it is back, and every bit as flexible.
Example
The following Data Form allows users to add new products and enter financial information. In this form, 3 rules exist.
- The GP Level 2 % has to be between -10% and 30%.
- The Regular Cases CANNOT have one month that is more than 50% of the total cases.
- If Regular Cases is entered, a corresponding Average Price per Case is required.
When a user fills out the form and violates these rules, the cell background is changed and a tool tip is added. If violations exists, the form does NOT save any changes to the database. Before any changes can be committed, all errors have to be corrected. In this example, all 3 validation rules are violated and noted. If the user hovers the mouse over the cell with a violation, the tool tip is displayed with the row and column members, and an error message explains to the user what the issue is with the data that is entered.
The Code
The significance of this is huge, but the implementation is rather simple. It is probably be one of the more basic things created with a Groovy Calculation. Quite simply, to add a validation error and stop the form from saving, all that has to be done is to add a validation error to the cell.
cell.addValidationError(0xFF0000, “Customer Error Message“,false)
This method accepts 3 parameters.
- The first is the color you want the background to change to. This is the integer value of any color. Most people are familiar with the RGB code, and this can be retrieved in almost any image editor (even Windows Paint). There are many free options, like the free option at https://www.shodor.org/ to convert that to a value that can be interpreted in Groovy.
- The second parameter is the error message to be displayed in the tool tip.
- The third is optional, and defaults to false. False that it will identify the cell as an error and stop the form from saving.
This will likely becused in a grid iterator, which is how this example was constructed to get the screenshot above. If the grid iterator object is foreign to you, read Adventures in Groovy – Part 3: Acting On Edited Cells. The one function that is void from that article is the crossDimCell method. This acts like a cross dim (->) in a calculation. So, it references the POV of the cell and overrides the dimension of the member specified as a parameter. If multiple differences exist, separate the names with a comma.
def BackErrColor = 16755370 // Light Red
//Loop through the cells on the Regular Cases row
operation.grid.dataCellIterator('Regular_Cases','Jan','Feb','Mar','Apr','May','Jun','Jul','Aug','Sep','Oct','Nov','Dec').each {
// Set a variable equal to all 12 months
def CaseTotal = it.crossDimCell('Jan').data + it.crossDimCell('Feb').data + it.crossDimCell('Mar').data + it.crossDimCell('Apr').data + it.crossDimCell('May').data + it.crossDimCell('Jun').data + it.crossDimCell('Jul').data + it.crossDimCell('Aug').data + it.crossDimCell('Sep').data + it.crossDimCell('Oct').data + it.crossDimCell('Nov').data + it.crossDimCell('Dec').data
// Check the cell value to see if it is larger than 50% of the total year
if(it.data / CaseTotal > 0.5 )
it.addValidationError(BackErrColor, "Cases for a single month can't be more than 50% of the total year cases.", false)
// If cases are entered, make sure there is a corresponding price
if(it.data != 0 && (it.crossDimCell("Avg_Price/Case_Inp").data == 0 || it.crossDimCell("Avg_Price/Case_Inp").data == '#Missing'))
it.crossDimCell("Avg_Price/Case_Inp").addValidationError(BackErrColor, "A price is required when cases are entered.", false)
}
// Loop throught the GP input cells and validate the % is in the valid range
operation.grid.dataCellIterator('GP_2_%_Inp','Jan','Feb','Mar','Apr','May','Jun','Jul','Aug','Sep','Oct','Nov','Dec').each {
println "$it.MemberNames $it.data"
if(it.data > 0.3 || it.data < -0.1 ) {
it.addValidationError(BackErrColor, "GP2 has to be between -10% and 30%.", false)
}
}
Form Settings
The one gotcha is that this needs to run BEFORE SAVE. It makes sense, but I was expecting a similar result as validating a RTP when the Business Rule runs on save, so it took me a good night sleep to recognize that error in judgement.
Why This Is Significant
You may not thing this is a big deal because you can check this in a Business Rule after the data is saved and return an error message requesting the user to change it. However, the users are as busy, if not more busy, than you are. There are last minute changes that get slammed in at the end of a forecast or budget cycle. There is no design doc to go back to and say it is going to take longer and we need a change order. The CFO won’t accept that as an answer, so things get forgotten or missed. This example forces valid data (not necessarily accurate) to be entered, and all kinds of things can be checked to make sure human errors don’t turn into huge issues for financial reporting. Think if you had a product and forgot to put a price. You could be missing millions, and this type of proactive validation can prevent such things from happening. Little things like this reduce, or eliminate, fire drills later on in the budget cycle.
Conclusion
There is an infinite number of things that can be accomplished. Simple things like the above, to extremely complex validation can be added. Think about ensuring allocated dollars are fully allocated(100%), forcing salaries to be in predefined pay bands for titles, and forcing the results of driver based planning to be within a logical margin.
If you have some examples, please share with the community by posting a comment below.
Hi Kyle
interesting post and well explained. I have an requirement not quite right sure how to achieve it. Users are using planning adhoc to load data. The validations are stored in table in the below format
Dim1Mem1, Dim2Mem1, Dim3Mem1
Dim1Mem2, Dim2Mem2, Dim3Mem2
On saving the data or by running the business rule, the system should validate the intersections against the above table and if available the data should be submitted if not it has to be rejected or not accepted.
Yes we can achieve this by applying security but the problem is the validation is so granular we will end up creating like 1000+ groups. Any directions will be appreciated.
Regards
Pradeep Chandran
If you on prem, you could write a CDF to hit a relational database and do this. in PBCS, you don’t have any place to access a table on a relational environment.
Hi Kyle,
Thanks for the post, is there any way we can validate the form without clicking the save button that will trigger this code. As user will enter the data in the form it should turned into red if it will not satisfy the condition.
There is not.
Should the Groovy Rule still complete or should it error if it meets the criteria based on the IF logic?
Unless you force it to fail, it will go through all the cells and format them as needed. If you initiate an error rather than just act on logic that identifies issues, then it would immediate fail and the calc would stop execution. Does that help?
Hi Kyle,
Thanks a lot for coming up with this series.. I have a requirement similar to what you have described above.. Let’s say I have Period in columns and Business Units in columns (can be changed/modified over the period) .. I want to make sure that % entered in all Business units for a month doesn’t exceed 100% .. How can I pass business unit values dynamically ?? Your code works perfectly fine for row wise Data validation , how to achieve it for columnwise one with dynamically having members in columns
First, you can get the rows dynamically and can be done in many ways. Search my blog for colmbrs and you will find examples. If I understand the requirement, no matter how many units there are, the month has to be 100%. Why not loop through all the cells and append the cell value to a collection? For example,
variableSum[month of cell] += cell.data
Then loop through the different variables and if any of them are not 100, throw an error and tell the user which months are wrong.
Thanks Kyle .. I tried to do the same with following code , but stuck at adding values to a collection part .. Kindly help ..
def BackErrColor = 16755370 // Light Red
DataGrid curgrid = operation.getGrid()
def rowmbrs = curgrid.rows
String srowmbrs = rowmbrs
def Total_Jan
operation.grid.dataCellIterator(srowmbrs).each {
Total_Jan = Total_Jan + it.crossDimCell(‘Jan’).data
if(Total_Jan > 1 )
it.addValidationError(BackErrColor, “Total Can’t be more than 100”, false)
}
I don’t see a collection in the example. I would recommend you look at some Groovy examples of defining variables and define them as they will be used (string, integer, double, etc).
Thanks Kyle .. I am able to crack this one .. Once again thank you very much for this detailed Groovy Series ..
Glad to help. I am a huge supporter of sharing, but I also hope people pay it forward – give a penny, take a penny. It would be great for you to post what you did and examples. You could do it in a comment, or if you wanted, write it up and I will post it as a guest contributor. Hope to hear from you soon.
Here you go .. I know there will be better way to reduce this code length , but this is my first ever try with anything groovy .. 🙂
def BackErrColor = 16755370 // Light Red
DataGrid curgrid = operation.getGrid()
def rowmbrs = curgrid.rows
String srowmbrs = rowmbrs
double Total_Jan
double Total_Feb
double Total_Mar
double Total_Apr
double Total_May
double Total_Jun
double Total_Jul
double Total_Aug
double Total_Sep
double Total_Oct
double Total_Nov
double Total_Dec
operation.grid.dataCellIterator(‘Jan’,srowmbrs).each {
Total_Jan = Total_Jan + it.crossDimCell(‘Jan’).data
}
operation.grid.dataCellIterator(‘Feb’,srowmbrs).each {
Total_Feb = Total_Feb + it.crossDimCell(‘Feb’).data
}
operation.grid.dataCellIterator(‘Mar’,srowmbrs).each {
Total_Mar = Total_Mar + it.crossDimCell(‘Mar’).data
}
operation.grid.dataCellIterator(‘Apr’,srowmbrs).each {
Total_Apr = Total_Apr + it.crossDimCell(‘Apr’).data
}
operation.grid.dataCellIterator(‘May’,srowmbrs).each {
Total_May = Total_May + it.crossDimCell(‘May’).data
}
operation.grid.dataCellIterator(‘Jun’,srowmbrs).each {
Total_Jun = Total_Jun + it.crossDimCell(‘Jun’).data
}
operation.grid.dataCellIterator(‘Jul’,srowmbrs).each {
Total_Jul = Total_Jul + it.crossDimCell(‘Jul’).data
}
operation.grid.dataCellIterator(‘Aug’,srowmbrs).each {
Total_Aug = Total_Aug + it.crossDimCell(‘Aug’).data
}
operation.grid.dataCellIterator(‘Sep’,srowmbrs).each {
Total_Sep = Total_Sep + it.crossDimCell(‘Sep’).data
}
operation.grid.dataCellIterator(‘Oct’,srowmbrs).each {
Total_Oct = Total_Oct + it.crossDimCell(‘Oct’).data
}
operation.grid.dataCellIterator(‘Nov’,srowmbrs).each {
Total_Nov = Total_Nov + it.crossDimCell(‘Nov’).data
}
operation.grid.dataCellIterator(‘Dec’,srowmbrs).each {
Total_Dec = Total_Dec + it.crossDimCell(‘Dec’).data
}
operation.grid.dataCellIterator(‘Jan’,srowmbrs).each {
if(Total_Jan > 1.00)
it.addValidationError(BackErrColor, “Total Can’t be more than 100”, false)
}
operation.grid.dataCellIterator(‘Feb’,srowmbrs).each {
if(Total_Feb > 1.00)
it.addValidationError(BackErrColor, “Total Can’t be more than 100”, false)
}
operation.grid.dataCellIterator(‘Mar’,srowmbrs).each {
if(Total_Mar > 1.00)
it.addValidationError(BackErrColor, “Total Can’t be more than 100”, false)
}
operation.grid.dataCellIterator(‘Apr’,srowmbrs).each {
if(Total_Apr > 1.00)
it.addValidationError(BackErrColor, “Total Can’t be more than 100”, false)
}
operation.grid.dataCellIterator(‘May’,srowmbrs).each {
if(Total_May > 1.00)
it.addValidationError(BackErrColor, “Total Can’t be more than 100”, false)
}
operation.grid.dataCellIterator(‘Jun’,srowmbrs).each {
if(Total_Jun > 1.00)
it.addValidationError(BackErrColor, “Total Can’t be more than 100”, false)
}
operation.grid.dataCellIterator(‘Jul’,srowmbrs).each {
if(Total_Jul > 1.00)
it.addValidationError(BackErrColor, “Total Can’t be more than 100”, false)
}
operation.grid.dataCellIterator(‘Aug’,srowmbrs).each {
if(Total_Aug > 1.00)
it.addValidationError(BackErrColor, “Total Can’t be more than 100”, false)
}
operation.grid.dataCellIterator(‘Sep’,srowmbrs).each {
if(Total_Sep > 1.00)
it.addValidationError(BackErrColor, “Total Can’t be more than 100”, false)
}
operation.grid.dataCellIterator(‘Oct’,srowmbrs).each {
if(Total_Oct > 1.00)
it.addValidationError(BackErrColor, “Total Can’t be more than 100”, false)
}
operation.grid.dataCellIterator(‘Nov’,srowmbrs).each {
if(Total_Nov > 1.00)
it.addValidationError(BackErrColor, “Total Can’t be more than 100”, false)
}
operation.grid.dataCellIterator(‘Dec’,srowmbrs).each {
if(Total_Dec > 1.00)
it.addValidationError(BackErrColor, “Total Can’t be more than 100”, false)
}
Thank you so much, I think everybody can benefit from all of us, experienced or not, by sharing thoughts. Here are some thoughts. I don’t know what the purpose of the srowmbrs is. Anything inside the parens is a filter – more of a way to say don’t loop through everything, just loop x. If you just have the month, it will still loop through all the rows because there is no filter on that dimension. The crossdim is not needed either. If you filter on a month, then cross dimming on that month really doesn’t do anything except point to itself. It is really there so you can loop through Jan and point to another cell, like checking to see if Jan is more than x% of YearTotal.
I haven’t tested this, but directionally, this is what I would do to make this a little more manageable to maintain and replicate.
Normally allocations need to 100% allocated, but this could be changed easily below by changing the > to !=. Again, this has not been tested so sorry if it needs tweaked, but you will get the idea. Thanks again for contributing.
// create map to hold all months totals allocValues = [:]
// this assumes you only have one year
// if you had two years, you would probably need to make first a concatenated string or a list inside a map (my preference)
Map
// loop through entire grid and get totals for each month
operation.grid.dataCellIterator().each {
if(allocValues[it.getPeriodName()] == null){allocValues[it.getPeriod()]=0}
allocValues[it.getPeriodName()] += it.data
}
// color all cells if the month doesn’t equal 1
allocValues.each( month, total ->
if(total > 1){
operation.grid.dataCellIterator(month).each {
it.addValidationError(BackErrColor, “Total Can’t be more than 100”, false)
}
}
)
Lastly, if you want to thank me with a drink, there is no shame in donating.
Is it possible to get the name of the data form and build a calc script if the name of the data form is A or build another calc script if the name of the data form is B?
I don’t believe there is. I think the obvious question is why don’t you just assign the right rule to the right form, but my guess is there is a reason you want to do this (like making it dynamic so people don’t have to assign the right rule). If you can provide why you are doing this maybe I can give you some ideas.
The problem is that there is a limitation on the number of rules, forms that you can have in EPBCS when you enable Hybrid. So we have different forms with different layouts. We dont want to create multiple rules for agg for each of the form. So I was thinking if this is the form name, call this agg. if it is another call another agg. This way we can only have 1 business rule and have different aggs for different purposes.
I am on a client now where I dynamically get the members from all the areas and all forms have the exact same calc. It is possible to do.
The problem is that there is a limitation on the number of rules, forms that you can have in EPBCS when you enable Hybrid. So we have different forms with different layouts. We dont want to create multiple rules for agg for each of the form. So I was thinking if this is the form name, call this agg. if it is another call another agg. This way we can only have 1 business rule and have different aggs for different purposes.
Got it. How I am handling that, and currently have a very complicated project, I have only one calculation! It looks at what is in the POV and what is in the grid and dynamically builds the calculations – even ignoring dynamics in a fix. I think that is the way you should go. I pull all the POV into a map, then add the edited members for each dimension in the grid. I use that to dynamically build the logic. I cover all the methods you would need to do this in my class – https://in2hyperion.podia.com/groovyforplanning. There is a discount this month of 200 – https://in2hyperion.podia.com/groovyforplanning?coupon=COLLABFEB20
Ok. Thanks. How does the lab work? How many days will I have access to it? Can I do it multiple times? And do we get access to an environment for the labs?
I do not provide labs – way to expensive for me buy pods for your use. If I had to pay for that, these classes would be too expensive for you to purchase. If you are an oracle partner, you can open a ticket and request a loaner pod for a short time.
Hey Kyle,
Really loving all the Groovy materials you’ve created here; thanks for sharing 🙂 Wanted to share something we put together for a client around validating form data as well.
Use case: Users need to allocate employees across departments for Workforce Planning. We give them 3 lines to allocate to, and they select the allocated departments from a smart list (I initially wasn’t sure if the validation rule would work for smart list values like it does for data as you’ve outlined above). The validation error needs to make sure the users aren’t selecting the same department more than once.
Here’s the code (which i’m sure could be made more dynamic, but it functions as needed):
def BackErrColor = 16755370 // Light Red
//Loop through the Allocation lines
operation.grid.dataCellIterator(‘Allocation_Dept’,’Line1′,’Line2′,’Line3′).each{
def Line1 = it.crossDimCell(‘Line1’).data
def Line2 = it.crossDimCell(‘Line2’).data
def Line3 = it.crossDimCell(‘Line3’).data
if(Line1!=0){
if(Line1==Line2)
it.addValidationError(BackErrColor, “Cannot choose same Operation more than once.”, false)
if(Line1==Line3)
it.addValidationError(BackErrColor, “Cannot choose same Operation more than once.”, false)
}
if(Line2!=0){
if(Line2==Line1)
it.addValidationError(BackErrColor, “Cannot choose same Operation more than once.”, false)
if(Line2==Line3)
it.addValidationError(BackErrColor, “Cannot choose same Operation more than once.”, false)
}
if(Line3!=0){
if(Line3==Line1)
it.addValidationError(BackErrColor, “Cannot choose same Operation more than once.”, false)
if(Line3==Line2)
it.addValidationError(BackErrColor, “Cannot choose same Operation more than once.”, false)
}
}
Thanks a ton for sharing with the community. There are a lot of ways to do the same thing. Sometimes wrong, some right, but most of the time performance is really negotiable. The only thing I could think of, outside of putting these in OR statements inside an if is to maybe put the 3 values in an array and check for duplicates. Maybe something like the following.
Iterate through rows rather than cells (assuming line 1 are columns, which may not be the case – just an example). If they are rows/columns, change the filter in the iterator to only include Line1.
List validateAllocation = [it.crossDimCell(‘Line1’).data,it.crossDimCell(‘Line2’).data,it.crossDimCell(‘Line3’).data]
if(validateAllocation != validationAllocation.unique()){
add your validation
}
If your line1 to 3 is variable and can be line 1 through 10, or 1 through n, you would have to do a little more work in the list to dynamically add all cells that start with “Line”, for example.
Anyway, thanks Brett!
Hi Kyle,
Have you tried the validations lately?
I have many validations on forms that will not change color if the cell is an edited cell. The data is not saved, as it should not, however the background color remains yellow.
It seems the background color for edited cells is taking precedence over the validation error color I have chosen.
Thanks for all your great posts! I LOVE groovy!
Silvana
You aren’t the only one with the issue. This is a bug introduced a few months ago. Could you open a ticket and put pressure to fix? I am disappointed it is t already updated.
Hi Kyle,
Yes we opened an SR and are asking them to fix this. They claim it is working as intended to have the edited cell color have a higher priority over the validation rules background color setting. Hopefully they will address it.
I thought I was going crazy since it was working 2 months ago!
Thanks,
Silvana