"Inner Join" to filter an interactive table using the Unique Values Aggregation

Lets say I have two tables like below-- Products and Orders. And I want to show an interactive table of work orders… but only for one product family at a time. You can use the “Unique Values” aggregation to create an array for filtering an interactive table.

image

  1. Create a query for selecting your subset of product IDs based on a Product Family filter. This can be a static input or app input.
  2. Create a unique values aggregation for Product IDs which will pull a list of IDs matching your Product Family filter.
  3. Inside of your app, store this aggregation into a variable. In my app, I am doing this whenever I click “Snacks” or “Cookies” so that the filter changes.
  4. Add an Is In filter to your interactive table based on this variable

1,2image 3 image

4 image

In this case, it is important to keep in mind that it would have been much more practical to just place the Product Family as an extra column in the Orders table. However, sometimes apps are not as straightforward as this. This is just an example of a trick to keep in your back pocket. And the final demo:

Untitled_ Dec 14, 2021 5_19 PM

Full disclaimer-- I know this is not really an inner join, but we are using this trick to filter the same way that an inner join does. I couldn’t think of a better way to describe it.

Please don’t hesitate to reach out if you have any questions. And reply if anyone knows an easier way to do this!

4 Likes

Heck yeah, this is the kind of content I love to see.
Nice work