Help

Dashboard combining multiple tables but filtered by linked record

Solved
Jump to Solution
2125 4
cancel
Showing results for 
Search instead for 
Did you mean: 
jethroc
4 - Data Explorer
4 - Data Explorer

Hello,

I have a base for our sales CRM. In the CRM I have 3 key tables:

  1. Deals
  2. Tasks
  3. Reports

Each Tasks and Report is linked to a Deal. In Deals we have a field "Owner" which is a User field. In both Tasks and Reports we have a linked field "Owner (from Deals)".

I want to create a dashboard for our weekly meeting with one filter to select one or more users from the "Owner" field and then based on that I have 3 grids one each for Deals, Tasks and Reports display records based on the "Owner" filter. (the next step after I solve this would be then to filter records based on a date field, but I believe this isn't going to be a problem).

I can't find any way to cascade the filtering from the Deal Owner to the Tasks and Reports.

The only way I can mentally come up with a model that would work is if I had a table "Owners" and linked the Deals to an Owner. Then I should be able to use a record picker to select the Owner from the Owners table and cascade the filtering down to Tasks and Reports as well.

But then I wouldn't be using the in-built user field and I would have to manually add users to the owner table.

I've created an example base and interface to illustrate what I'm trying to do, see

Base: https://airtable.com/shrOksYRJJpn0oDhM

Interface: https://airtable.com/invite/l?inviteId=invWROPhInsSYXkb1&inviteToken=0edd82be4e48996eb6a9177e3a3dfeb...

Does anyone have any suggestions?

1 Solution

Accepted Solutions
Tyler_Thorson
6 - Interface Innovator
6 - Interface Innovator

I think your intuition is correct:


The only way I can mentally come up with a model that would work is if I had a table "Owners" and linked the Deals to an Owner. Then I should be able to use a record picker to select the Owner from the Owners table and cascade the filtering down to Tasks and Reports as well.

But then I wouldn't be using the in-built user field and I would have to manually add users to the owner table.

Except that you don't need to manually add the users. Simply add an automation that triggers when that field is updated, and adds the user to the owners table for you.

Hope that helps.

See Solution in Thread

4 Replies 4
Tyler_Thorson
6 - Interface Innovator
6 - Interface Innovator

I think your intuition is correct:


The only way I can mentally come up with a model that would work is if I had a table "Owners" and linked the Deals to an Owner. Then I should be able to use a record picker to select the Owner from the Owners table and cascade the filtering down to Tasks and Reports as well.

But then I wouldn't be using the in-built user field and I would have to manually add users to the owner table.

Except that you don't need to manually add the users. Simply add an automation that triggers when that field is updated, and adds the user to the owners table for you.

Hope that helps.

Thanks.

TXGeneral64
4 - Data Explorer
4 - Data Explorer

I would also like to see a little more robustness in the filter hierarchy on interfaces (particularly dashboards) where you want to pull together views of data from multiple related tables. If there is a common field of multiple tables derived from the records in a common table, it would be great if setting that common value as a filter would propagate to other elements where that field is a "filterable" field as well.

I agree with @TXGeneral64 , if I have a linked record as a "Rep" or "Owner" that exists in both grids and their respective sources, why can it not filter on that single field and impact both grids? Currently, the work around is a duplicated filter on the linked field twice impacting each source individually, this seems redundant and surprising that it wont let me filter multiple grids off one.