Filter records that meet certain conditions for Linked Record (ID) not lookup
Topic Labels:
Base design
0
518
2
Hello 👋
I took an effort and explored other related posts available. I came to a workaround I am not happy with, so I decided to make a new post and ask Experts for guidance 😇 I know almost everything is possible with Airtable, sometimes we struggle to find a way.
Problem Statement
How can I display linked records that meet certain criteria (not the lookup/rollup field), but exact records.
Context
I have 3 Tables (relevant for the issue). Chapter, Event, Sentiment.
The story looks like this: There are different Events. Chapter Members from multiple Chapters provide responses about given Event in a form of Sentiment.
Relations
(one) Chapter - (many) Events
(one) Chapter - (many) Sentiments
(one) Event - (many) Chapters
(one) Event) - (many) Sentiments
(one) Sentiment - (many) Events
(one) Sentiment - (many) Chapters
*looks like I have some many-many relations, and I don't have any additional table to store it.
Problem
(one) Chapter - (many) Sentiments
For a given Chapter A, I can see all Sentiments provided by this Chapter (across all Events)
For a given Chapter A, how can I see Sentiments provided by this Chapter ONLY for Event A?
Few screenshots and things I tried
This is Sentiment Table (main view)
This is Sentiment Table, with View filtered by given Event (Sep 6th)
This is Chapter Table (main view), here you can see additional operations I do.
COUNT(Sentiment) - to see how many responds I collected
AVERAGE (Supported) - to see what is the average score.
This is Chapter Table for Sept 6th Event. I try different things, to actually limit Column "Sentiment" to have less records. In other words:
Delivery Management Chapter contains 3 Sentiments: 34, 36, 37
34 and 36 comes from Event Sep 6th
37 comes from Event Sep 7th - and I want to get rid of it 😆
TRY 1
I tried to "Limit record selection to a view" for Sentiment. It didn't work. After my research wit other post, I know that the intention of this option is different.
TRY 2
I tried to set a condition for lookup field, to return only Sentiment IDs, which come from Event Sep 6th.
It returns exactly what I need, but as text, not the Sentiment record itself. Therefore I cannot use COUNT(Sentiment) or do the AVERAGE(Supported), since Supported values come from Sentiment.
TRY 3
I tried to filter out by Lookup(Event), but it doesn't help, since it filter out too many records. I cannot come up with a condition that will be helpful in my case. And I understand why this condition filtered out too many records. It's just not what I need.
TRY 4 - WORKAROUND (which works)
I tried to apply a condition to COUNT (No of responses) and SUPPORTED, to take only Sentiments that has Event Sep 6th.
How do I know it works? I can see that COUNT (No of responses) shows 2 instead o 3 responds. In addition I have 2 values for Supported (0.0 and 1.0) instead of 3.
Why it's not good enough for me? Because I need to manually apply this condition for every Chapter field.
In reality I have more than COUNT and SUPPORTED. I have 5 fields and 50 Chapters. Which gives me 250 times I need to edit the condition. It's not optimal.
Closure
I need a way to filter the main Sentiment record. To limit what it shows based on Event condition.
If you made it till the end, THANK YOU, and in advance THANK YOU for any additional guidance you might give me.
Cheers,
Karolina
Hi Karolina! I don't think there's a way to filter the linked record field unfortunately, but I'm wondering if you can achieve what you're fundamentally looking to do by using groupings to create this view in your Sentiments table instead of your Chapters table.
With this view, I think you could still see the individual Sentiments organized by chapter and event, and use Airtable's native field summaries to generate things like averages. Attached are my grouping rules and the subsequent view. You could also create filtered views for each of your chapters (Where chapter is [insert] ) to toggle through different sentiments about individual events. Hope this helps!
