Adventures in Groovy – Part 17: Force Cell Comments
Challenge Accepted
When I asked visitors to try to come up with a situation that Groovy Calculation might be able to solve, this was a good one. One visitor asked if they could require a cell comment if certain parameters were not met. It is actually relatively easy.
The following requirement exist in this example. If any month holds more than 30% of the full year, that cell requires the user to enter a cell comment. If no comment exists, the user won’t be able to save the form.
The User Experience
If any month is more than 30% of the full year, and the user doesn’t add a comment to a cell, the form will not save. The following shows what happens when the above fails, and what happens after the user enters a comment into the cell.
The Code
def backErrColor = 16755370 //Red
def caseTotal = 0
def accountName = ""
// Loop through the months
operation.grid.dataCellIterator('Jan','Feb','Mar','Apr','May','Jun','Jul','Aug','Sep','Oct','Nov','Dec').each {
// Get a total for all 12 months every time the row changes
if(it.getAccountName() != accountName) {
accountName = it.getAccountName();
caseTotal = it.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
}
// If the value is greater than 30% of the total and the cell does NOT have a cell comment, interrupt the form save
if(it.data > 0 && it.data / caseTotal > 0.3 && !it.hasCellNote() ) {
it.addValidationError(backErrColor, "Cases for a single month can't be more than 30% of the total year without an assumption.", false)
}
}
Conclusion
Challenge accepted. This one goes in the win column for Groovy Calculations!
Hi Kyle, great stuff! Question for you, instead of looping through months, can I have the rule focus on a (Variance) formula column and if the variance is above 10% from one budget version to another, prompt the user to enter a comment?
You can reference any cell in a form. The issue with a variance calc is that the variance won’t have changed on pre-save. You will have to reference the cells the variance does and do the math
Hi Kyle,
Question regarding a specific use case we are looking for – is there anyway we can make certain cells in a data form read-only based on certain conditions through groovy scripts?
You inspired me to post on this topic. Yes, this is very possible and EXTREMELY easy to do. Check out Part 45 of Adventures in Groovy!