Dynamically Listing Only Next Milestone for Each Project

Topic Labels: Formulas
473 0
Showing results for 
Search instead for 
Did you mean: 
4 - Data Explorer
4 - Data Explorer

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!

0 Replies 0