I currently have a dynamic app in R using Shiny that displays a data table on another tab based on the user selection in the first tab. The code for this is below
rm(list=ls())
library(shiny)
library(RODBC)
library(DT)
library(openxlsx)
ui <- bootstrapPage(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","Export Data"), 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 = 'multiple', rownames = FALSE)
table2 <- reactive({
row <- input$MyTable_rows_selected
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 (AddressLine1+' '+City+' '+StateCode+' '+PostalCode) like '%",table1()[row,"Address"],"%'
and a.ActiveRecordIndicator = 1 and b.ActiveRecordIndicator = 1"), as.is = TRUE)})
table8 <- reactive({
row <- input$MyTable_rows_selected
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)})
observeEvent(input$MyTable_rows_selected, {
output$CDFInfo <- DT:: renderDataTable({table9()},options = list(dom='t'), caption = 'Client Information', rownames = FALSE)
updateTabsetPanel(session, "Main Panel", selected = "Client Discovery Form Information")
})
Up to this point the code has only supported single selection; however, I'm wanting to change it over to multiple selection. I changed the main selection table (MyTable) over to multiple selection which is working, but table 9 is still just showing only one selection worth of data. It will only show which individual I select first on the first tab. I'm wanting to show a data table of both individuals combined in a data table. Thanks for all of the help ahead of time!
