Dashboard with list of indirectly referenced records

Ask for Help
1

I have a set of routes from a start location to a destination location passing through other locations. Each leg of the route, between two neighboring locations, may use a different type of transport (walk, bike, car, bus, …).

I want to build a dashboard which shows, for a selected location, the full routes it is part of. Ideally, the individual routes are visually separated into their own blocks.

I have a table of the locations. Due to the differing lengths of routes, a simple route table seems impractical. Instead, I thought of having a table of waypoints (route id, location, route stop order, transport to leave by).

But I can’t get my head around how to connect the two for the dashboard.

Simply adding a waypoint table widget gives me only the waypoints of the selected location but not the others of the route.

If I introduce an intermediary route table I get all waypoints - but only for the first route. For others, I have to manually select them in the routes table.

The only workaround I found is to introduce a formula column in the waypoints table which builds a list of all waypoints with the same route id. Kind of works but it feels wrong to me to store the resolved list in the database instead of calculating it at runtime.

Any pointers to possible solutions appreciated.

2

Hmm, this sounds a bit tricky but not impossible.

I’m curious about this, though. Would you be able to share a demo doc of what you’re experiencing? That would help work towards a solution.

4

Thanks, this is helpful.

For Case 3, unless you’re using a trigger formula, the formula should update automatically on record changes. Are you seeing something else?

There’s also using two layers of linked tables like this. More clicking, but may be what you’re looking for.

5

For case 3, you are correct.The formula updates - strangely enough, in my other document, I had a data column instead. :frowning:

The solution with the two layers of linked tables is basically the same as case 2. And unfortunately, it does not work as I require to display all relevant routes at once.

Thanks for the taking the time, though.