Interactive Dashboards - What's really required to make them work?

So I get that if I have a simple Ref relationship between two tables, create views for those tables and stick them together in an Interactive Dashboard magic happens and when I select a row from one of those tables, associated rows appear in the other table.

To go a little deeper, does this really work because of the “Related” lists that are automatically created for us when we setup the Ref column?

And if so, what happens when we start slicing the data?

As an example, I have Technicians who are carved out of a list of Employees using a slice. Work Orders have a Ref column for Technicians based on that Slice. But, I also divide Work Orders into Estimates and Service Orders with Slices.

When I attempt to create an Interactive Dashboard, using a view based on the Estimates Slice and another view based on the Technicians Slice, my hope was that the Estimates would be filtered by the chosen Technician. Alas, it doesn’t work (sigh). My thought is that maybe the Estimates can’t be properly filtered due to all of the slicing of the data.

So, again, at a deeper level, what is required between the two tables to get the filtering to properly work? Can I create some additional thing manually to make it function?

2 Likes

I know this is not the answer you are looking for what I do for any of these complicated operations is I create a python function(s) that will get the data, apply some kind of complex filtering/summing/counting/combining function then write it back to the sheet so that AppSheet can display the results on the dashboard.

The way that I handle this is with a table for ENUM. And any of those drop-downs and phases and categories are actually referenced back to that ENUM table. Then all you have to do is simply include a slice of that enough table for the categories that you want to look at on your dashboard and include the table of those items as well as the other two tables you want. Then when you select technicians and estimates and you know the rest will react

2 Likes

One more thing, you would need to put the filter and conditions for the enum in the data validity area of that drop down so that it’s not showing the whole enum table just items you want…

Thanks for the suggestion! Its nice to know of other options. I don’t think I’m quite ready to add this additional layer to the technology stack just yet but it is nice to know it is an option I can fall back on if/when needed.

@Grant_Stead

I’m intrigued by your idea. Are you saying that you create this ENUM table solely for the purpose of Interactivity within the Dashaboard?

And, for the sake of discussion, if I wanted to react to a choosing of Technician, I would include a list of Technicians in this ENUM table. Next I would Ref this ENUM list in each of the other tables/slices I wanted to show in the Dashboard. Then add these other table/slice views to the Dashboard along with a table of my Technician list from the ENUM table. Is that basically it in a nushell?

Check out my video

1 Like

Your video was helpful. The Interactive Dashboard is performing as I expect.

I essentially had the same construct as you but where I differed was in the Ref column definition Source Table. I had selected the Technicians Slice. Once I changed that to the base Table of Employees and then added the Data Validity using the Technicians Slice, it worked!!

So it seems that in order to get Interactive Dashboards to function properly we need to reference the Raw tables as the Source Table and not use Slices in these instances.

I think I might need to perform a little re-work in Data Validity sections due to changing from the Slice to the Table but it doesn’t seem to be a big deal.

I’ll have an awesome Interactive Dashboards once I’m done!!

Thanks Grant!!!

1 Like

You’re welcome, glad I could help!

1 Like

A related side question, do you happen to know if the Interactive Dashboard filtering can be applied to the Secondary layer of a map?

I have a Map that shows the current location of Technicians. Unfortunately due to a bug in the Map Secondary layer which does not show LatLong info, Technicians MUST be the Primary layer. As a Secondary layer I am showing Today’s Work Locations. It would be cool if I could get the map to filter these work locations based on a selected Technician.

No, I’m sorry. I’ve yet to leverage the secondary map layer.

@Grant_Stead Could you please send the URL to your Video? It doesn’t load in Chrome and Edge.

@Fabian Strange that’s not working. When I put the link in, it automatically embeds it… I don’t know how to stop it from doing that other than this…
Does it work when I do it like this?

https://www.loom.com/share/f83f6c31bb674b32ae1efb68107c8574

1 Like

The link did work when you first posted it. I watched the video then. Today it shows a message that Loom.com is refusing the connection.

The link you just posted does work for me.

UPDATE: I just right-clicked on the video link in the original post and selected “Reload” from the menu options, that seems to refresh the video link.