Help

Filter records that meet certain conditions for Linked Record (ID) not lookup

Topic Labels: Base design
403 2
cancel
Showing results for 
Search instead for 
Did you mean: 
karolinapajak
5 - Automation Enthusiast
5 - Automation Enthusiast

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)

Screenshot 2023-10-05 at 13.07.48.png

This is Sentiment Table, with View filtered by given Event (Sep 6th)

Screenshot 2023-10-05 at 13.08.34.pngScreenshot 2023-10-05 at 12.47.15.png

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.

Screenshot 2023-10-05 at 13.14.46.png

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 😆

Screenshot 2023-10-05 at 13.28.00.png

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. 

Screenshot 2023-10-05 at 13.29.25.png

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.

Screenshot 2023-10-05 at 13.31.13.png

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.

Screenshot 2023-10-05 at 13.37.45.pngScreenshot 2023-10-05 at 13.39.05.png

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. 

Screenshot 2023-10-05 at 13.43.33.pngScreenshot 2023-10-05 at 13.43.43.png

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.

Screenshot 2023-10-05 at 13.43.50.png

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

2 Replies 2
Josh_Colina
6 - Interface Innovator
6 - Interface Innovator

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!

Screen Shot 2023-10-05 at 10.34.17 AM.png

Screen Shot 2023-10-05 at 10.34.38 AM.png

Hello Josh! Great to hear from you so quickly! I really appreciate you read my example.

Regarding your proposal, it doesn't address all my need, since I would like to take the average and put in on the plot (using Airtable extension).

Therefore I need average, count etc. to have in some kind of "field" I can point the chart to.