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).

Illustration:

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!