CODEWITHSUNDEEP
rshiny
Lcsballer1
Lcsballer1

Reputation: 99

Download Button Shiny App - How to fix error "Cannot Find Function"

I'm attempting to create a download button in my shiny app that downloads out an excel file with multiple tabs, once for each table in my shiny app. I believe I've put the code together correctly but I'm continuing to get the same error. Warning: Error in table9: could not find function "table9" [No stack trace available] I think it has something to do with the fact that I'm referencing an observe event or even a reactive statement. Any ideas on how to get this working? Code is below:

rm(list=ls())
library(shiny)
library(RODBC)
library(DT)
library(openxlsx)
ui <- bootstrapPage(  tags$head(
  tags$style(HTML("
body{
    background-color: #FBFAFA;
}

h2 {
    box-sizing: border-box;
    font: Roboto;
    font-weight: bold;
    font-size: 40px;
    border-bottom: 4px solid #b00027;
    color: firebrick;
    height: 32px;
    width: 1140px;
    column-rule-color: rgb(51, 51, 51);
    perspective-origin: 570px 16px;
    transform-origin: 570px 16px;
    border: 0px none rgb(51, 51, 51);
    margin: 5px 5px 20px;
    outline: rgb(51, 51, 51) none 0px;
}

.nav-tabs>li>a {
    box-sizing: border-box;
    color: darkgray;
    float: left;
    font-size: 18px;
    column-rule-color: rgb(34, 34, 34);
    perspective-origin: 569px 51.5px;
    background: white;
    border-top: 0px none rgb(34, 34, 34);
    border-right: 0px none rgb(34, 34, 34);
    border-bottom: 1px solid rgb(217, 217, 217);
    border-left: 0px none rgb(34, 34, 34);
    outline: rgb(34, 34, 34) none 0px;
    margin: 5px 3px 3px;
    font-weight: bold;
}

table.dataTable thead th, table.dataTable thead td {
    padding: 10px 18px;
    border-bottom: 1px solid #111;
    font-size: medium;
    text-align: center;
}

table.dataTable tbody th, table.dataTable tbody td {
    padding: 8px 10px;
    font-size: medium;
    text-align: center;
}

.form-group.shiny-input-container {
  display: inline-block;
  font-size: 20px;
  padding: 1%;
  margin: 0px;
}

caption {
    padding-top: 8px;
    padding-bottom: 8px;
    font-weight: bold;
    color: firebrick;
    margin: 10px 5px 5px;
    font-size: 30px;
    text-decoration: underline;
    text-decoration-color: gray;
}

table.dataTable{
    clear: both;
    margin-top: 6px !important;
    margin-bottom: 6px !important;
    max-width: none !important;
    border-collapse: separate !important;
    border-spacing: 0;
}

.tab-content {
    width: 90%;
    padding: 2.5%;
    background: white;
    margin-left: auto;
    margin-right: auto;
    border-radius: 50px;
}"
))
),
 titlePanel("Financial Planning Client Discovery Form"),
                                    
  
  tabsetPanel( id = "Main Panel", tabPanel("Client Selection",
  
  textInput(inputId = "First_Name",
              label = "First Name",
              value = "",
              width = NULL,
              placeholder = "Enter Client's First Name"),
  textInput(inputId = "Last_Name",
            label = "Last Name",
            value = "",
            width = NULL,
            placeholder = "Enter Client's Last Name"),
  textInput(inputId = "SSN",
            label = "Social Security Number",
            value = "",
            width = NULL,
            placeholder = "Enter Client's SSN (XXXXXXXXX)"),
  
  DT::dataTableOutput("MyTable")
  
), tabPanel("Client Discovery Form Information",downloadButton("downloadData","Download"), DT::dataTableOutput("CDFInfo"),DT::dataTableOutput("WealthSummary"), DT::dataTableOutput("Deposit"),DT::dataTableOutput("Investment"),DT::dataTableOutput("Loan"),
             DT::dataTableOutput("Other"))))

server <- function(input, output, session) {
  
  BDW <- odbcConnect("BDW", uid="", pwd="", believeNRows = FALSE)
  BOSCDB <- odbcConnect("BOSCDB", uid="", pwd="", believeNRows = FALSE)
  
  table1 <- reactive({sqlQuery(BDW,paste0("
                      SELECT DISTINCT a.PartyID, FullName as 'Name',TaxID as 'Social Security Number', AddressLine1+' '+City+' '+StateCode+' '+PostalCode as 'Address'
  FROM [EnterpriseCustomer].[dbo].[Party]a
  LEFT JOIN [EnterpriseCustomer].[dbo].[PartytoAddressRelationship]b ON b.PartyID = a.PartyID
  LEFT JOIN [EnterpriseCustomer].[dbo].[Address]c ON c.AddressID = b.AddressID
  WHERE FullName like '",input$First_Name,"%'
  and FullName like '%",input$Last_Name,"'
  and TaxID like '%",input$SSN,"%'
  and a.ActiveRecordIndicator = 1
  and b.ActiveRecordIndicator = 1
  and c.ActiveRecordIndicator = 1"), as.is = TRUE)})
  
  
  output$MyTable = DT::renderDataTable({table1()}, caption = 'Client Selection',options = list(searching = FALSE, pageLength = 15,list(className = 'dt-left')), selection = 'single', rownames = FALSE)
  
  observeEvent(input$MyTable_rows_selected, {
    row <- input$MyTable_rows_selected
    table2 <- reactive({sqlQuery(BDW,paste0("SELECT DISTINCT FullName as 'Name',DATEDIFF(hour,DateofBirthorIncorporation,GETDATE())/8766 as 'Age',CONVERT(varchar(10),DateofBirthorIncorporation, 120) as 'Date of Birth',TaxID as 'SSN',GenderCode as 'Gender', OccupationCodeDescription as 'Occupation',DigitalAddress as 'Email', AddressLine1+' '+City+' '+StateCode+' '+PostalCode as 'Address'
  FROM [EnterpriseCustomer].[dbo].[Party]a
  LEFT JOIN [EnterpriseCustomer].[dbo].[PartytoAddressRelationship]b ON b.PartyID = a.PartyID
  LEFT JOIN [EnterpriseCustomer].[dbo].[Address]c ON c.AddressID = b.AddressID
  LEFT JOIN [EnterpriseCustomer].[dbo].[PartytoDigitalAddressRelationship]d ON d.PartyID = a.PartyID
  LEFT JOIN [EnterpriseCustomer].[dbo].[DigitalAddress]e ON e.DigitalAddressID = d.DigitalAddressID
  LEFT JOIN [BDW].[EnterpriseCustomer].[PartyOccupationCodes]g ON a.OccupationCode = g.OccupationCode
  WHERE
  a.PartyID =",table1()[row,"PartyID"],"
  and a.ActiveRecordIndicator = 1 and b.ActiveRecordIndicator = 1"), as.is = TRUE)})
  
  table8 <- reactive({sqlQuery(BOSCDB,paste0("SELECT DISTINCT TaxID, ltrim(rtrim(MinimumAnnualIncomeAmount))+' - '+ltrim(rtrim(MaximumAnnualIncomeAmount)) as 'Annual Income Band',ltrim(rtrim(MinimumNetWorthAmount))+' - '+ltrim(rtrim(MaximumNetWorthAmount)) as 'Net Worth Band',ltrim(rtrim(MinLiquidNetWorthAmt))+' - '+ltrim(rtrim(MaxLiquidNetWorthAmt)) as 'Liquid Net Worth Band'
  FROM [ExternalData_Stage].[dbo].[tblPrsCustomerAccount_G]a
  LEFT JOIN [ExternalData_Stage].[dbo].[tblPrsCustomerAccount_H]b ON b.AccountNumber = a.AccountNumber
  WHERE TaxID = '",table1()[row,"Social Security Number"],"'
  and a.DataDate = (SELECT MAX(DataDate) FROM [ExternalData_Stage].[dbo].[tblPrsCustomerAccount_G] WHERE TaxID = '",table1()[row,"Social Security Number"],"')"),as.is = TRUE)})
    
  table9 <- reactive({merge(table2(),table8(), by.x = "SSN", by.y = "TaxID", all.x = TRUE)})
    
  output$CDFInfo <- DT:: renderDataTable({table9()},options = list(dom='t'), caption = 'Client Information', rownames = FALSE)
    updateTabsetPanel(session, "Main Panel", selected = "Client Discovery Form Information")
  })
  
  observeEvent(input$MyTable_rows_selected, {
    row <- input$MyTable_rows_selected
    table7 <- reactive({sqlQuery(BDW,paste0("SELECT DISTINCT FullName as 'Name',FORMAT(SUM(CASE WHEN ProductCategory = 'Deposit' THEN Value ELSE 0 END),'C', 'en-US') as 'Total Deposits Balance',FORMAT(SUM(CASE WHEN ProductCategory = 'Loan' THEN Value ELSE 0 END),'C','en-US') as 'Total Loans Balance',FORMAT(SUM(CASE WHEN ProductCategory = 'Investment' THEN Value ELSE 0 END),'C','en-us') as 'Total Investments Balance',FORMAT(SUM(CASE WHEN ProductCategory is null THEN Value ELSE 0 END),'C','en-US') as 'Total Other Balance'
  FROM (SELECT DISTINCT PartyID, FinancialAccountID
  FROM [EnterpriseCustomer].[dbo].[PartytoFinancialAccountRelationship]
  WHERE ActiveRecordIndicator = 1)a
  LEFT JOIN (SELECT *
  FROM [EnterpriseCustomer].[dbo].[FinancialAccount]
  WHERE ActiveRecordIndicator = 1)b ON b.FinancialAccountID = a.FinancialAccountID
  LEFT JOIN [BDW].[EnterpriseCustomer].[FinancialAccountStatusCodes]c ON b.StatusCode = c.StatusCode
  LEFT JOIN [BDW].[EnterpriseCustomer].[FinancialAccountProductCodes]d ON  b.ProductCode = d.ProductCode AND b.SourceSystemCode = d.SourceSystemCode
  LEFT JOIN [EnterpriseCustomer].[dbo].[FinancialAccountValueSnapshot]e ON b.FinancialAccountID = e.FinancialAccountID
  LEFT JOIN (SELECT *
  FROM [EnterpriseCustomer].[dbo].[Party]
  WHERE ActiveRecordIndicator = 1)f ON a.PartyID = f.PartyID
  WHERE a.PartyID =",table1()[row,"PartyID"],"GROUP BY FullName"), as.is = TRUE)})
    output$WealthSummary <- DT:: renderDataTable({table7()},options = list(dom='t'), caption = 'Client Wealth Management Summary', rownames = FALSE)
    updateTabsetPanel(session, "Main Panel", selected = "Client Discovery Form Information")
  })
  
  observeEvent(input$MyTable_rows_selected, {
    row <- input$MyTable_rows_selected
    table3 <- reactive({sqlQuery(BDW,paste0("SELECT DISTINCT FinancialAccountNumber as 'Account Number',FinancialAccountName as 'Account Name',OpenDate as 'Account Open Date', ProductType as 'Account Description', FORMAT(Value,'C','en-US') as 'Account Balance'
  FROM [EnterpriseCustomer].[dbo].[PartytoFinancialAccountRelationship]a
  LEFT JOIN [EnterpriseCustomer].[dbo].[FinancialAccount]b ON b.FinancialAccountID = a.FinancialAccountID
  LEFT JOIN [BDW].[EnterpriseCustomer].[FinancialAccountStatusCodes]c ON b.StatusCode = c.StatusCode
  LEFT JOIN [BDW].[EnterpriseCustomer].[FinancialAccountProductCodes]d ON b.ProductCode = d.ProductCode AND b.SourceSystemCode = d.SourceSystemCode
  LEFT JOIN [EnterpriseCustomer].[dbo].[FinancialAccountValueSnapshot]e ON b.FinancialAccountID = e.FinancialAccountID
  LEFT JOIN [EnterpriseCustomer].[dbo].[Party]f ON a.PartyID = f.PartyID
  WHERE a.PartyID =",table1()[row,"PartyID"],"AND ProductCategory = 'Deposit' AND StatusCategory = 'Open'
  AND b.ActiveRecordIndicator = 1"), as.is = TRUE)})
    output$Deposit <- DT:: renderDataTable({table3()},options = list(dom='t'), caption = 'Deposit Accounts', rownames = FALSE)
    updateTabsetPanel(session, "Main Panel", selected = "Client Discovery Form Information")
  })
  
  observeEvent(input$MyTable_rows_selected, {
    row <- input$MyTable_rows_selected
    table4 <- reactive({sqlQuery(BDW,paste0("SELECT DISTINCT FinancialAccountNumber as 'Account Number',FinancialAccountName as 'Account Name',OpenDate as 'Account Open Date', ProductType as 'Account Description', FORMAT(Value,'C','en-US') as 'Account Balance'
  FROM [EnterpriseCustomer].[dbo].[PartytoFinancialAccountRelationship]a
  LEFT JOIN [EnterpriseCustomer].[dbo].[FinancialAccount]b ON b.FinancialAccountID = a.FinancialAccountID
  LEFT JOIN [BDW].[EnterpriseCustomer].[FinancialAccountStatusCodes]c ON b.StatusCode = c.StatusCode
  LEFT JOIN [BDW].[EnterpriseCustomer].[FinancialAccountProductCodes]d ON b.ProductCode = d.ProductCode AND b.SourceSystemCode = d.SourceSystemCode
  LEFT JOIN [EnterpriseCustomer].[dbo].[FinancialAccountValueSnapshot]e ON b.FinancialAccountID = e.FinancialAccountID
  LEFT JOIN [EnterpriseCustomer].[dbo].[Party]f ON a.PartyID = f.PartyID
  WHERE a.PartyID =",table1()[row,"PartyID"],"AND ProductCategory = 'Investment' AND StatusCategory = 'Open'
  AND b.ActiveRecordIndicator = 1"), as.is = TRUE)})
    output$Investment <- DT:: renderDataTable({table4()},options = list(dom='t'), caption = 'Investment Accounts', rownames = FALSE)
    updateTabsetPanel(session, "Main Panel", selected = "Client Discovery Form Information")
  })
  
  observeEvent(input$MyTable_rows_selected, {
    row <- input$MyTable_rows_selected
    table5 <- reactive({sqlQuery(BDW,paste0("SELECT DISTINCT FinancialAccountNumber as 'Account Number',FinancialAccountName as 'Account Name',OpenDate as 'Account Open Date', ProductType as 'Account Description',ValueType as 'Balance Description', FORMAT(Value,'C','en-US') as 'Account Balance'
  FROM [EnterpriseCustomer].[dbo].[PartytoFinancialAccountRelationship]a
  LEFT JOIN [EnterpriseCustomer].[dbo].[FinancialAccount]b ON b.FinancialAccountID = a.FinancialAccountID
  LEFT JOIN [BDW].[EnterpriseCustomer].[FinancialAccountStatusCodes]c ON b.StatusCode = c.StatusCode
  LEFT JOIN [BDW].[EnterpriseCustomer].[FinancialAccountProductCodes]d ON b.ProductCode = d.ProductCode AND b.SourceSystemCode = d.SourceSystemCode
  LEFT JOIN [EnterpriseCustomer].[dbo].[FinancialAccountValueSnapshot]e ON b.FinancialAccountID = e.FinancialAccountID
  LEFT JOIN [EnterpriseCustomer].[dbo].[Party]f ON a.PartyID = f.PartyID
  WHERE a.PartyID =",table1()[row,"PartyID"],"AND ProductCategory = 'Loan' AND StatusCategory = 'Open'
  AND b.ActiveRecordIndicator = 1"), as.is = TRUE)})
    output$Loan <- DT:: renderDataTable({table5()},options = list(dom='t'), caption = 'Loan Accounts', rownames = FALSE)
    updateTabsetPanel(session, "Main Panel", selected = "Client Discovery Form Information")
  })
  
  observeEvent(input$MyTable_rows_selected, {
    row <- input$MyTable_rows_selected
    table6 <- reactive({sqlQuery(BDW,paste0("SELECT DISTINCT FinancialAccountNumber as 'Account Number',FinancialAccountName as 'Account Name',OpenDate as 'Account Open Date', ProductType as 'Account Description', FORMAT(Value,'C','en-US') as 'Account Balance'
  FROM [EnterpriseCustomer].[dbo].[PartytoFinancialAccountRelationship]a
  LEFT JOIN [EnterpriseCustomer].[dbo].[FinancialAccount]b ON b.FinancialAccountID = a.FinancialAccountID
  LEFT JOIN [BDW].[EnterpriseCustomer].[FinancialAccountStatusCodes]c ON b.StatusCode = c.StatusCode
  LEFT JOIN [BDW].[EnterpriseCustomer].[FinancialAccountProductCodes]d ON b.ProductCode = d.ProductCode AND b.SourceSystemCode = d.SourceSystemCode
  LEFT JOIN [EnterpriseCustomer].[dbo].[FinancialAccountValueSnapshot]e ON b.FinancialAccountID = e.FinancialAccountID
  LEFT JOIN [EnterpriseCustomer].[dbo].[Party]f ON a.PartyID = f.PartyID
  WHERE a.PartyID =",table1()[row,"PartyID"],"AND ProductCategory is NULL AND StatusCategory = 'Open'
  AND b.ActiveRecordIndicator = 1"), as.is = TRUE)})
    output$Other <- DT:: renderDataTable({table6()},options = list(dom='t'), caption = 'Other Accounts', rownames = FALSE)
    updateTabsetPanel(session, "Main Panel", selected = "Client Discovery Form Information")
  })

  output$downloadData <-
    downloadHandler(
    filename = function() {
      "Financial_Planning_CDF_Data.xlsx"
    },
    content = function(file) {
      write.xlsx(list("Client Information" = table9(),"Wealth Summary" = table7(),"Deposit Accounts" = table3(), "Investment Accounts" = table4(), "Loan Accounts" = table5(),"Other Accounts" = table6()), file, row.names = FALSE)
    }
  ) 
  
}
shinyApp(ui, server)

Upvotes: 0

Views: 408

Answers (2)

YBS
YBS

Reputation: 21287

As suggested by Tom, you can define reactive statements outside the event handlers or use reactiveValues objects as

server <- function(input, output, session) {
   rv <- reactiveValues()
   
   observeEvent(input$MyTable_rows_selected, {
      row <- input$MyTable_rows_selected
      rv$table3 <- reactive({sqlQuery(BDW,...)})
   })

   output$downloadData <-
      downloadHandler(
         filename = function() {
            "Financial_Planning_CDF_Data.xlsx"
         },
         content = function(file) {
            write.xlsx(list("Deposit Accounts" = rv$table3...), file, row.names = FALSE)
         }
      )
}

Upvotes: 1

Tom
Tom

Reputation: 592

It should be enough to restructure your code a bit. So for instance table7 part, you would rewrite it like this to make it available throughout your server function (including the downloadHandler):

table7 <- reactive({
    row <- input$MyTable_rows_selected
    sqlQuery(BDW,paste0("SELECT DISTINCT FullName as 'Name',FORMAT(SUM(CASE WHEN ProductCategory = 'Deposit' THEN Value ELSE 0 END),'C', 'en-US') as 'Total Deposits Balance',FORMAT(SUM(CASE WHEN ProductCategory = 'Loan' THEN Value ELSE 0 END),'C','en-US') as 'Total Loans Balance',FORMAT(SUM(CASE WHEN ProductCategory = 'Investment' THEN Value ELSE 0 END),'C','en-us') as 'Total Investments Balance',FORMAT(SUM(CASE WHEN ProductCategory is null THEN Value ELSE 0 END),'C','en-US') as 'Total Other Balance'
      FROM (SELECT DISTINCT PartyID, FinancialAccountID
      FROM [EnterpriseCustomer].[dbo].[PartytoFinancialAccountRelationship]
      WHERE ActiveRecordIndicator = 1)a
      LEFT JOIN (SELECT *
      FROM [EnterpriseCustomer].[dbo].[FinancialAccount]
      WHERE ActiveRecordIndicator = 1)b ON b.FinancialAccountID = a.FinancialAccountID
      LEFT JOIN [BDW].[EnterpriseCustomer].[FinancialAccountStatusCodes]c ON b.StatusCode = c.StatusCode
      LEFT JOIN [BDW].[EnterpriseCustomer].[FinancialAccountProductCodes]d ON  b.ProductCode = d.ProductCode AND b.SourceSystemCode = d.SourceSystemCode
      LEFT JOIN [EnterpriseCustomer].[dbo].[FinancialAccountValueSnapshot]e ON b.FinancialAccountID = e.FinancialAccountID
      LEFT JOIN (SELECT *
      FROM [EnterpriseCustomer].[dbo].[Party]
      WHERE ActiveRecordIndicator = 1)f ON a.PartyID = f.PartyID
      WHERE a.PartyID =",table1()[row,"PartyID"],"GROUP BY FullName"), as.is = TRUE)
})

observeEvent(input$MyTable_rows_selected, {
    output$WealthSummary <- DT:: renderDataTable(table7(),options = list(dom='t'), caption = 'Client Wealth Management Summary', rownames = FALSE)
    updateTabsetPanel(session, "Main Panel", selected = "Client Discovery Form Information")
  })

Upvotes: 1

Related Questions