Numerical validation of a cell using rhandsontable R package

Question

I'd like to create a validation scheme when a table based on "rhandsontable" is updated. I have two columns: Min and Max and I'd like that when a cell in the column Max is updated, only it's allowed updating with a value greater than the previous column Min in the same row or vice-versa. Maybe it's possible using hot_cols renderer and validating by means of subtraction greater than zero, etc. but I'm not familiar with "rhandsontable" or "hansontable" in general although I think this is possible, but I don't know how. Thanks.

Toy example:

library(shiny)
library(rhandsontable)

if (interactive()) {

ui <- fluidPage(
   rHandsontableOutput('table'),
   tableOutput('table1')
)

server <- function(input, output,session) {
values <- reactiveValues(df=data.frame(Parameter=c('A','B','C'),Min=c(10,20,30),Max=c(20,30,40)))
observe({
  if(!is.null(input$table)){
    values$df <- hot_to_r(input$table)
    output$table1<-renderTable(values$df)
    }
})
output$table<-renderRHandsontable({ 
  rhandsontable(values$df)%>%
  hot_col("Parameter", readOnly = TRUE)%>%
  hot_validate_numeric(col='Min', min = 1, max = 50,allowInvalid = FALSE)%>%
  hot_validate_numeric(col='Max', min = 1, max = 50,allowInvalid = FALSE)
})
}
shinyApp(ui, server)
}

Have you considered to do these type of fine grained validations on the server side? You can get on the server whatever has been entered, analysing it, then decide whether OK or not. The client side is good for general data validations (i.e. ranges; num vs char etc.). The server case seems the right one for more challening ones like the ones you describe. — Enzo, Apr 21 '17 at 16:36
Hi Enzo, I'm using rhandsontable as a "form" with initial values that the user can modify at any time. I know I could use some another widget to enter data, e.g. textInput, but when you have many parameters and these are generated "on the fly", I thought that an option would be to use to this kind of table to enter values in an easy way but in rhandsontable you only can control the format by columns (e.g. min-max by column) but I don't how to include conditions to relate two columns for example. — Uli, Apr 24 '17 at 11:47
If you enter min 20 and max 10 is clearly an error. So, by using some javascript o some function I need to validate these values and then to continue or to show an error message. Always thinking on the server side. I've seen that is possible to use a renderer function to change the colour of a cell when is less or greater than a fixed value, etc. but I don't know how to extend this behaviour to two columns. This does make sense to you? — Uli, Apr 24 '17 at 11:47

score 2 · Accepted Answer · answered Apr 24 '17 at 21:55

This is what I was referring to in my comment. You may find it complex, but this is what server side quality control would look like with rhandsontable.

At this stage you are at liberty to come up with the most complex functions you need.

As a more complex (but possibly more efficient) way to do this you could have different merge statement that can work like SQL join, or use dplyr or data.table non-equi or primary / secondary joins.

library(shiny)
library(rhandsontable)

ui <- fluidPage(column(6, 
   rHandsontableOutput('table')),
   column(6, tableOutput('table1'))
)

server <- function(input, output,session) {

df <- eventReactive( input$table,  {
  if (is.null(input$table))  {

        dfOld <<- df <- data.frame(Parameter=c('A','B','C'),Min=c(10,20,30),Max=c(20,30,40))

    } else {
        df <- hot_to_r(input$table)
        #  Quality control 
        # Rule 1:  
        if( any(df$Max > 9) & any(df$Min < 3) ) {
          df$Max <- dfOld$Max
          df$Min <- dfOld$Min
        } }
    dfOld <<- df
    df
  }, ignoreNULL = F)

output$table<-renderRHandsontable({ 

  if (is.null(df())) return()
  rhandsontable(df())%>%
  hot_col("Parameter", readOnly = TRUE)%>%
  hot_validate_numeric(col='Min', min = 1, max = 50,allowInvalid = FALSE)%>%
  hot_validate_numeric(col='Max', min = 1, max = 50,allowInvalid = FALSE)
})
}

shinyApp(ui, server)

Please let me know if useful.

Hi Enzo, thanks for your tip. This works !!! As you indicated, I created a intermediate stage "a<-hot_to_r(input$table)" and then I applied the condition "if (any(a$Min > a$Max)){ a message or an alert}". Thanks. This was a good idea because I was thinking to apply some condition but at the level of "renderRHandsontable" but you were right, it's better to do it when you update the table using hot_to_r. Cheers. — Uli, Apr 25 '17 at 18:11

Numerical validation of a cell using rhandsontable R package

1 Answers1