Dynamically Listing Only Next Milestone for Each Project

Hi. I’m a new Airtable user and this is my first post. I apologize if this repeats a solution provided elsewhere, but after a lot of searching I can’t find quite the advice I’m seeking.

My Base:
I have several projects. Each project has one or more milestones. Each milestone is associated with only one project.

In a Projects table I have a field {Project Name} and other fields for entering info about the project not relevant to this post.
In a Milestones table I have the fields {Milestone Name}, {Milestone Deadline}, and {Project Name}
So the tables are linked through the {Project Name} field.

My objective: On the Project table I’m trying add a column that automatically shows the name of the next milestone for each project. (I’d like to allow for the possibility that for any project the next day on while a milestone occurs might be a day when actually multiple milestones are scheduled for that project).

So, if I enter the following into the Milestone Table:

{Milestone Name} || {Milestone Deadline} || {Project Name}
Milestone-1 || 1/1/22 || Project-1
Milestone-2 || 1/1/22 || Project-1
Milestone-3 || 6/1/22 || Project -1
Milestone-4 || 5/1/22 || Project-2
Milestone-5 || 4/1/22 || Project-2
Milestone-6 || 3/1/01 || Project-2

In the Project Table, on today’s date, I would ideally like Airtable to automatically populate two new fields - {Next Milestone Date} and {Next Milestone(s)}:

{Project Name} || {Next Milestone Date} || {Next Milestone(s)
Project-1 || 1/1/22 || Milestone-1 Milestone-2
Project-2 || 4/1/22 || Milestone-5

Has anyone else solved this problem, or know how to solve this problem?

Thank you for considering!

This topic was solved and automatically closed 15 days after the last reply. New replies are no longer allowed.