Dynamic Dashboard with indirectly related views

[Matt_Myers]

Hi I have these tables:

1. TABLE: Activities with FIELDS: ActivityID (PK), PlaceID
2. TABLE:PlacePhotos with FIELDS:PlacePhotoID(PK), PlaceID

I want to build a dashboard with two views:

1. A Detail view of an individual record from the Activities table
2. A Gallery view sourced from the PlacePhotos table where Activities.PlaceID = PlacePhotos.PlaceID. PlacePhotos can contain ZERO, ONE, or MANY records where PlaceID matches Activities.PlaceID.

I’m launching this Dashboard view when a record is selected in a separate Deck view. Right now I’ve got the Dashboard “working” using a separate “filter” table that is updated with a PlaceID value when the record is selected in the Deck view. Unfortunately this arrangement (at least the way I’ve implemented it) is very slow. I click on the Deck view, the Filter Table eventually gets updated, and the Dashboard view eventually refreshes to show the appropriate data…

There’s gotta be a better or more optimal way to do this. Any suggestions? Thanks!

[Suvrutt_Gurjar]

Something like this?

In general it could be achieved by interactive dashboards, if the two tables are referenced to each other.

[Matt_Myers]

Thanks @Suvrutt_Gurjar … I thought about that but don’t “referneced” tables have to point to a Primary Key in the ‘other’ table? I have this table structure:
TABLE: Activities FIELDS: ActivityID (PK) and PlaceID
TABLE: PlacePhotos FIELDS: PlacePhotoID (PK) and PlaceID

The “PlaceID” is the ‘join’ component but it’s not a PK in either table. Can I still do a reference in this scenario?

[Suvrutt_Gurjar]

Thank you @Matt_Myers for the updates.

1. Is there a seperate table for Place ID?. How the app user ensures she/ he enters the same place ID as a join when they add records both in Activities and PlacePhotos tables?

2. Can there be many place IDs for a single activity? And in turn, can each Place ID have multiple PlacePhot IDs? How the three IDs are related?

[Matt_Myers]

@Suvrutt_Gurjar thanks for the continued engagement! regarding your questions:

Suvrutt_Gurjar:

Is there a seperate table for Place ID?.

YES - There is a Places table where the PK is Place ID

Suvrutt_Gurjar:

• How the app user ensures she/ he enters the same place ID as a join when they add records both in Activities and PlacePhotos tables?

This app is much more of a “content consumption” app than a data maintenance app. All this data and the relationships will be maintained manually behind the scenes directly in the data source. The people that use the actual app are pretty much just consumers of the data.

Suvrutt_Gurjar:

Can there be many place IDs for a single activity?

No. Only ONE Place ID ever for any given Activity record.

Suvrutt_Gurjar:

nd in turn, can each Place ID have multiple PlacePhot IDs? How the three IDs are related?

YES - Any give Place ID can have ZERO, ONE, or MANY Place Photo ID’s associated with it.

The three tables would be related like this:

1. Places: PK = Place ID
2. Activities: PK = Activity ID and there is an FK column to Places
3. Place Photos: PK = Place Photo ID and there is an FK column to Places

Maybe we need a new relationship type table called Activity Photos that ties the photos Directly to an Activity? I had to do that because it would be a little duplicative…

Your continued thoughts are appreciated!!

[Suvrutt_Gurjar]

Thank you @Matt_Myers for all the details.

Please explore below

1. Please create a VC called say [Related Photos] in the Activities Table with an expression something like

[Place ID].[Related PlacePhotos]

[ Place ID] is the FK in the Activities table of the Places table

2. In the dashboard, please have the following views

A) Deck View of Activities table
B) Detail view of the Activities table that has the {Related Photos] column included.

3. Please define the view “Place Photos_Inline” system-generated on the Place Photos table as a gallery view.

With the above configuration, the dashboard will look like below

[Matt_Myers]

Hey Suvrutt, just wanted to say thank you for time you spent. The discussion and examples really sent me down a good path! – Matt

[Suvrutt_Gurjar]

Thank you @Matt_Myers for the update. You are welcome.