Help

Upcoming database upgrades. Airtable functionality will be reduced for ~15 minutes at 06:00 UTC on Feb. 4 / 10:00 pm PT on Feb. 3. Learn more here

Filter based on latest 3 values of a linked field

Topic Labels: Base design
Solved
Jump to Solution
695 4
cancel
Showing results for 
Search instead for 
Did you mean: 

I have a table that keeps track of calls being made by volunteers. The base has 3 main tables: callers, people being called, and a call log where volunteers submit a form detailing their calls (the tables are all linked). The call log form has a field where callers put “yes” if they reached the person and “no” if they did not reach the person. I want to create a view of the “people being called” table where there are only the people who have not been reached for all of the lates 3 calls. How can I do this? I know I could do rollup fields for the calls, and I have used this to keep track of when the last call was for each person, how many calls total, etc. But I am not sure how to keep track of the characteristics of the 3 latest calls.

1 Solution

Accepted Solutions

It isn’t a perfect solution, but you could use a Lookup field in the [People being Called] table to get the yes/no field values from the [Call Log] table. Then you could use a formula like this one: IF(RIGHT({Lookup Field}&"",6)="nonono", "✓", ""), then you could filter a view where only records with the checkmark are shown.

Screen Shot 2020-05-26 at 9.22.48 PM

The issue with this is that lookup and rollup field values aren’t necessarily sorted how you would like them to be, they’re based on the order Linked records appear in the Link to Another Record field. This might not be an issue for you though as you/your team are probably adding new call log records in the order they occur anyway.

Let me know if this works as a solution!

See Solution in Thread

4 Replies 4

It isn’t a perfect solution, but you could use a Lookup field in the [People being Called] table to get the yes/no field values from the [Call Log] table. Then you could use a formula like this one: IF(RIGHT({Lookup Field}&"",6)="nonono", "✓", ""), then you could filter a view where only records with the checkmark are shown.

Screen Shot 2020-05-26 at 9.22.48 PM

The issue with this is that lookup and rollup field values aren’t necessarily sorted how you would like them to be, they’re based on the order Linked records appear in the Link to Another Record field. This might not be an issue for you though as you/your team are probably adding new call log records in the order they occur anyway.

Let me know if this works as a solution!

Welcome to the community, @Danya_Levy!

That’s exactly what the Records List Block does. It will show you your records based on a specific view that you have already pre-filtered (in you case, the “no” field), and then you can limit the view to the latest 3 records. You can also sort the records in whatever order you want as well.

This worked! I had to modify it a little bit but it seems to be working great. Thanks so much for the help and the quick response!

Thanks so much! I ended up doing this the way Kamille suggested, but this is super helpful to know about too.