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!

 
4 replies
  1. Jay says:

    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?

     
    Reply
    • Kyle Goodfriend says:

      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

       
      Reply
  2. Gaurav says:

    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?

     
    Reply

Leave a Reply

Want to join the discussion?
Feel free to contribute!

Leave a Reply

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.