Creating universal filters for multiple reactive DFs in Shiny App

Question

I am building a dashboard that needs to display multiple tables with a tab for each table. The data are all related and I would like to have universal filters that when applied will filter all of the tables. As a simplified example, lets say I have the following tables for a car dealership with information about customers, their vehicle(s), and the services performed on the vehicles:

Customer	Zip Code	Age
Mason	14350	44
Dole	14352	25
Rogers	14358	60

Vehicle ID	Model	Year	Customer
355	Impreza	2015	Mason
1324	Forester	2020	Dole
121	Camry	2018	Rogers

Service ID	Service	Vehicle ID
1	Oil Change	355
2	Brakes	355
3	sensor replacement	355
4	Oil Change	1324
5	Brakes	121

My goal is to present each of these tables as a data table in its own tab in a Shiny App. I then want universal filters that filter all tables based on relationships. For example, if I filtered the services table to oil changes, I would expect the services table to filter to oil changed only, but also expect the vehicle and customer tables to filter based on if the owner/vehicle meets the oil change criteria.

What would be the best way to accomplish this? I attempted to dynamically loop through these data sets and create a reactive data frame for each set along with an output table, but am struggling with how to apply filters to all of the data frames at the same time. Would creating one large data frame be a better option where the views are created by selecting specific columns for each view/frame?

I would create a [module](https://shiny.rstudio.com/articles/modules.html) to handle the filtering of the data tables and implement each data tab as an instance of the module. The module would listen for changes in your global filter inputs and react accordingly. My answer [here](https://stackoverflow.com/questions/67416620/how-can-i-create-reactive-datasets-dynamically-via-a-loop-in-the-server-section/67422105#67422105) shows how to use the same module to display different data frames. Though you will obviously have to adapt the functionality for your situation. — Limey, Jun 01 '21 at 16:25

jpdugo17 · Answer 1 · 2022-01-13T00:29:52.977

We can join the three tables together, filter them with selectizeGroup-module and then split them back again. Some duplicate rows can result from doing the joins and then splitting, but using dplyr::distinct solves it.

app:

library(tidyverse)
library(shiny)
library(shinyWidgets)
library(gt)

customer <-
  tribble(
    ~Customer, ~Zip_Code, ~Age,
    "Mason", 14350, 44,
    "Dole", 14352, 25,
    "Rogers", 14358, 60
  )

vehicle <-
  tribble(
    ~Vehicle_ID, ~Model, ~Year, ~Customer,
    355, "Impreza", 2015, "Mason",
    1324, "Forester", 2020, "Dole",
    121, "Camry", 2018, "Rogers"
  )
service <-
  tribble(
    ~Service_ID, ~Service, ~Vehicle_ID,
    1, "Oil Change", 355,
    2, "Brakes", 355,
    3, "sensor replacement", 355,
    4, "Oil Change", 1324,
    5, "Brakes", 121
  )

df_joined <- inner_join(customer, vehicle, by = "Customer")
df_joined <- left_join(df_joined, service, by = ("Vehicle_ID"))

nms <- names(df_joined)

params <- nms %>%
  map(~ list(inputId = ., title = str_to_title(.))) %>%
  set_names(nms)

ui <- fluidPage(
  fluidRow(selectizeGroupUI(id = "my_filters", params = params)),
  tabsetPanel(
    tabPanel("Service", gt_output({
      "service_table"
    })),
    tabPanel("Vehicle", gt_output({
      "vehicle_table"
    })),
    tabPanel("Customer", gt_output({
      "customer_table"
    }))
  )
)

server <- function(input, output, session) {
  res_mod <- callModule(
    module = selectizeGroupServer,
    id = "my_filters",
    data = df_joined,
    vars = nms
  )



  df_splitted <- reactive({
    map(
      map(list(customer, vehicle, service), names),
      ~ select(res_mod(), all_of(.x))
    ) %>%
      map(distinct) %>%
      set_names(c("customer", "vehicle", "service"))
  })

  observeEvent(df_splitted(), { # the order of the table names matter
    walk2(c("customer_table", "vehicle_table", "service_table"), df_splitted(), ~ {
      output[[.x]] <<- render_gt({
        .y
      })
    })
  })
}

shinyApp(ui, server)

This is a great start, thanks for sharing! How would you handle additional filters that need to work in a bi-directional way in terms of being applied universally to all tables? For example, your code makes it possible to filter all tables with the field Service. However, what if we also want to filter all tables by vehicle model? — ncmc100, Jun 02 '21 at 18:41
Only filter for vehicle model, or apply both filters ( Service AND vehicle model) simultaneously? — jpdugo17, Jun 02 '21 at 18:58
@ncmc100 did you find the solution to your question? I have a similar question — Luis Miguel, Oct 22 '21 at 01:38
@LuisMiguel I edited my answer, perhaps this solution works for you. — jpdugo17, Jan 13 '22 at 00:30

Creating universal filters for multiple reactive DFs in Shiny App

1 Answers1