Filter linked records by submission date in other table

Michelle_Eier · ‎Oct 14, 2019

I am working on an NPS data sheet in Airtable and I’m left with a big question.

All our input is coming into the table: Feedback. We have another table called Themes and Insights. These are basically the topics discussed within the feedback and where we have the feedback records linked to.

Within the Feedback sheet there is a submission date. What I would like to do is to filter the feedback submission date/time so it only shows the discussed themes in the other table during that period.

So I would like to filter on the month of september in table one and only seeing the themes for september in the other table together with the record count of that month.

How do I do this?

Greg_Robinson · ‎Oct 29, 2019

Hi - I’m trying to do something similar. I have a list of active projects, I have a related list of actions/activity. On my primary Projects list the recent actions is including every action taken on those projects and I’d like to show only the most recent action - I have an action taken date in the actions list.

Were you able to find a resolution to your question?

Justin_Barrett · ‎Oct 29, 2019

Welcome to the community, @Michelle_Eier! :grinning_face_with_big_eyes:

There currently isn’t a way for a filter on one table to drive the filter on another table, even if there are records linking the two. You would have to create a filter on each table, and manually adjust the filter settings so that both tables show (for example) records from September.

However, I can think of a way to get a third table involved, and use a single record that third table to affect the filters on the other two. It’s definitely be a hack, but depending on your needs, it might work. Let me know if you want the details and I’ll break it down.

Actually, from your description, what you’re looking to do is very different. If I understand you correctly, you’ve got a [Projects] table and an [Actions] table, with action records linked to different projects. On the [Projects] table, you’re currently seeing all linked action records, but you only want to see the most recent one based on its date. Is that correct? If so, that can be set up as follows.

In your [Projects] table, add a rollup field pulling in the dates from the linked action records, using MAX(values) as your aggregation function. This will give you the date of the most recent action for each project.

In [Actions], add a rollup field that pulls in this most recent date field based on the linked project for each action. Next add a formula field that compares this rolled up most-recent date against the action date. If the two match, the formula will return the action name. The end result of this is that only the most recent action will have its name echoed in this formula field. All other actions for that project will be blank for this formula field.

Finally, roll up this formula field back in the [Project] table, using ARRAYCOMPACT(values) as the aggregation function. This will show the most recent action name for each project.

Greg_Robinson · ‎Oct 29, 2019

Thank you! That worked perfectly for my needs.

Michelle_Eier · ‎Nov 01, 2019

Hey Justin_Barrett That would be great! Could you explain me how a third table hack would work?

Thanks!

Justin_Barrett · ‎Nov 01, 2019

First there’s a bit of setup involved on the other two tables. Starting with the [Feedback] table, add a new formula field named {Month}, using the following formula (change {Submission Date} to point to your actual submission date field):

IF({Submission Date}, DATETIME_FORMAT({Submission Date}, "MMMM"))

This will generate plain text listings of the month associated with each feedback submission. Here’s the rough example I built.

You’ll also notice a {Theme} link field, which I’m assuming is already in place in your base to link each feedback item to a specific record from {Themes and Insights}. You can use this to get a count of feedback records tied to each theme by adding a {Feedback Count} field to your [Themes and Insights] table:

I’m assuming you already have a field on [Themes and Insights] that ties each theme/insight to a given month, and that it’s probably a Single Select field:

Now it’s time to add the third table, which I’m going to call [Control]. This table only needs one record, with the {Name} set to a single period, and the only other field (for now) being {Filter Month}, which is also a Single Select field.

The remaining steps need to be done in both the [Feedback] and [Themes and Insights] tables, but I’ll only list the steps once.

First add a field named {Link to Control}. I’m guessing you already have a lot of records already in place, so I’ll show a shortcut for auto-populating this field. Make it a formula field to begin with, with the formula being this:

"."

Just a period inside quotes, nothing more. That will put the same content into all records. Now change the field type to a Link field pointing to the [Control] table, and Airtable will auto-build links to that lone record:

Next add a Lookup field named {Filter Month}, using the link in {Link to Control} to pull the {Filter Month} value from the [Control] table:

Next add a formula field named {Match} using the following formula:

IF(Month = {Filter Month}, "X")

Finally, make a new view named “Month Filter”, and add a filter to only show records where {Match} is not empty.

Repeat these steps in the [Themes and Insights] table, then hide the {Link to Control}, {Filter Month}, and {Match} in both of your main tables. Now you can control records shown on both tables by changing the {Filter Month} option on the [Control] table.

As far as maintenance goes, you just need to make sure that all new records in both of your main tables are linked to that lone control record. I find the easiest way to do that is to group by that linked field. When adding a new record, Airtable will automatically make that link. If new records are added via a form, though, it won’t do this, so you’ll have to drag-fill the link from an existing linked record.

Eventually you’ll get to a point where you need to also filter by year, so that you don’t see records from September 2019 and September 2020 for example. To do that, add a year filter field to [Control] (probably a Single Select), year breakout fields to both tables, and modify the match formula so that both the year and month have to match.