How to adjust functionality for input data in shiny code

[JojoSouza]

The code below works, but I would like to make an adjustment. Note that I have two forms of input: Excel and Database. For this test, let's use the first option. Note that after choosing the Excel option, a fileInput will be loaded, and later you can load the file (You can download this file from the link: ex.xlsx - Google Sheets). After loading the file, an output table is generated, as figure below. But I have a doubt:

Note that in if (input$button ="Excel") inside the observe, I only insert output$fileInput. However, here it would not be necessary to insert data and data2 as well inside the observe? However, when I do that, then the app doesn't work. Could you try it out and help me solve this problem?

Executable code below

library(shiny)
library(dplyr)
library(shinythemes)

ui <- fluidPage(
  
  shiny::navbarPage(theme = shinytheme("flatly"), collapsible = TRUE,
                    br(),
                    tabPanel("PAGE1",
                             sidebarLayout(
                               sidebarPanel(
                                 radioButtons("button", 
                                              label = h3("Data source"),
                                              choices = list("Excel" = "Excel",
                                                             "Database" = "database"), 
                                              selected = "File"),
                                 br(),br(),
                                 uiOutput('fileInput'),
                                 uiOutput('daterange')
                                 
                               ),
                               mainPanel(
                                 dataTableOutput('table')
                               )))))



server <- function(input, output) {
  

  observe({
    if(is.null(input$button)) {
      
    }else if (input$button =="Excel"){
      
      output$fileInput <- renderUI({
        fileInput("file",h4("Import file"), multiple = T, accept = ".xlsx")
      })
      
      
    } else if(input$button=="database"){
      
      
      con <- DBI::dbConnect(odbc::odbc(),
                            Driver   = "[your driver's name]",
                            Server   = "[your server's path]",
                            Database = "[your database's name]",
                            UID      = rstudioapi::askForPassword("Database user"),
                            PWD      = rstudioapi::askForPassword("Database password"),
                            Port     = 1433)
      
      data <-tbl(con, in_schema("dbo", "date1")) %>%
        collect()
      
      data2 <- tbl(con, in_schema("dbo", "date2")) %>% 
        collect()
      
    } else {
      output$fileInput <- NULL
    }
  })

  data <- reactive({
    if (is.null(input$file)) {
      return(NULL)
    }
    
    else {
      df3 <- read_excel(input$file$datapath,sheetnames()[1])
      validate(need(all(c('date1', 'date2') %in% colnames(df3)), "Incorrect file"))
      df4 <- df3 %>% mutate_if(~inherits(., what = "POSIXct"), as.Date)
      return(df4)
    }
  })
  
  data2 <- reactive({
    req(input$file)
    df1 <- read_excel(input$file$datapath,sheetnames()[2])
    df1
  })
  
  sheetnames <- eventReactive(input$file, {
    available_sheets = openxlsx::getSheetNames(input$file$datapath)
  })  
 
  output$daterange <- renderUI({
    req(data())
    dateRangeInput("daterange1", "Period you want to see:",
                   start = min(data()$date2),
                   end   = max(data()$date2))
  })
  
  data_subset <- reactive({
    req(input$daterange1)
    days <- seq(input$daterange1[1], input$daterange1[2], by = 'day')
    subset(data(), date2 %in% days)
  })
  
  output$table <- renderDataTable({
    data_subset()
  })
  
}

shinyApp(ui = ui, server = server)

Generated table

[nirgrahamuk]

This sort of code is not recommended as it causes all sorts of problems.

  observe({
    if(is.null(input$button)) {
      
    }else if (input$button =="Excel"){
      
      output$fileInput <- renderUI({

don't define observables or reactives within the body of other observables or reactives.
You should be making reactive chains, rather than reactive nesting dolls.
if you want a renderUI function to trigger/recalculate when an input button is pressed you would use code of the form

    output$fileInput <- renderUI({
ib <-  req(input$button) 
if(ib=="Excel"){
# code that generates ui 
} else {
# code that generates alternative ui or null}
})

[JojoSouza]

Thanks for reply @nirgrahamuk ! Doing it this way works:

output$fileInput <- renderUI({
    ib <-  req(input$button) 
    if(ib=="Excel"){
    fileInput("file",h4("Import file"), multiple = T, accept = ".xlsx")
    } else {
        NULL
      }
  })

But, for me, data and data2 would have to be inside if(ib=="Excel"){..}, is not? Like this:

output$fileInput <- renderUI({
    ib <-  req(input$button) 
    if(ib=="Excel"){

    fileInput("file",h4("Import file"), multiple = T, accept = ".xlsx")
      data <- reactive({
        if (is.null(input$file)) {
          return(NULL)
        }
        else {
          df3 <- read_excel(input$file$datapath,sheetnames()[1])
          validate(need(all(c('date1', 'date2') %in% colnames(df3)), "Incorrect file"))
          df4 <- df3 %>% mutate_if(~inherits(., what = "POSIXct"), as.Date)
          return(df4)
        }
      })
      
      data2 <- reactive({
        req(input$file)
        df1 <- read_excel(input$file$datapath,sheetnames()[2])
        df1
      })
      
      sheetnames <- eventReactive(input$file, {
        available_sheets = openxlsx::getSheetNames(input$file$datapath)
      })
      
    } else {
        NULL
      }
    
  })

However, this second way does not work. What am I doing wrong?

[nirgrahamuk]

What purpose does nesting the data reactive serve ? If its only dependent on input$file it should be an independent definition which mentions input$file without nesting

[JojoSouza]

Thanks for reply @nirgrahamuk! I understand better now what you mean. I was thinking this other way, i.e. inserting data and data2 in input$button =="Excel" as I also have data and data2 for input$button=="database ". I want it to work for both Excel and database options. So I can leave data and data2 independently for that first case, and for input$button=="database", what would it look like?

[nirgrahamuk]

if data() reactive is defined by one source and data2() by another, at some point you want a single eitherdata() object to process either one similarly without care as to its origin, I would guess ? I would normally think of using an a manually populated reactiveVal for that use case, which depending on an observe trigger overwrites the content with one or the other source

eitherdata <- reactiveVal(NULL)
observeEvent(data(),  eitherdata(data()))
observeEvent(data2(),  eitherdata(data2()))

[JojoSouza]

Thank you very much @nirgrahamuk . You understand the idea well, which is to use data and data2, regardless of the origin, ie via Excel or database. I just can't test this code very well due to a little problem I have, but in order not to confuse this question, I asked a new question (Insert condition to show daterange in shiny code), if you can see it, I would appreciate it. Thanks again!