Skip to main content
Solved

Selecting earliest date among all related tasks

  • December 17, 2024
  • 6 replies
  • 0 views

Forum|alt.badge.img+3
  • New Participant
  • 3 replies

I have a Projects grid and a Tasks grid.

The Projects grid has fields for Project name, start date, and end date. 

The Tasks grid has fields for Task name, start date, end date, and reference to the Project name.  

For each Project, I would like the start date to be the earliest Task start date among all the Tasks that reference that Project.

Similarly, I would like the end date of each Project to be the latest Task end date among all the Tasks that reference that Project. 

Thank you in advance for any help!!

  

Best answer by TheTimeSavingCo

Does this look right?

Link to base

'Start date' in Projects is a rollup field that grabs from the 'Start date' field in Tasks, same for end date.  You can duplicate the base to your own workspace to see how it's set up too

View original
Did this topic help you find an answer to your question?

6 replies

ScottWorld
Forum|alt.badge.img+33
  • Brainy
  • 8763 replies
  • December 17, 2024

You can do that with 2 rollup fields in your Projects table.

Each rollup field would point to a date field in the Tasks table, and you would use one of these formulas:

MIN(values)
or
MAX(values)

- ScottWorld, Expert Airtable Consultant


Forum|alt.badge.img+3
  • Author
  • New Participant
  • 3 replies
  • December 17, 2024
ScottWorld wrote:

You can do that with 2 rollup fields in your Projects table.

Each rollup field would point to a date field in the Tasks table, and you would use one of these formulas:

MIN(values)
or
MAX(values)

- ScottWorld, Expert Airtable Consultant


Thank you for the response! I tried creating a "rollup" date field in the Projects grid, and then did rollup for all Start Dates in the Tasks grid. The trouble I ran into is that when I went to set a condition for which dates to rollup, I only saw a way to select a specific Project as the condition. In this case I have multiple Projects and need the rollup to be variable to each Project name. Am I missing a feature or is there a workaround? 


TheTimeSavingCo
Forum|alt.badge.img+28

Does this look right?

Link to base

'Start date' in Projects is a rollup field that grabs from the 'Start date' field in Tasks, same for end date.  You can duplicate the base to your own workspace to see how it's set up too


Forum|alt.badge.img+3
  • Author
  • New Participant
  • 3 replies
  • December 17, 2024
TheTimeSavingCo wrote:

Does this look right?

Link to base

'Start date' in Projects is a rollup field that grabs from the 'Start date' field in Tasks, same for end date.  You can duplicate the base to your own workspace to see how it's set up too


This looks correct! I was able to click into the base, but when I click the field to see how it's setup I can only "copy field URL". 


TheTimeSavingCo
Forum|alt.badge.img+28
tylerc wrote:

This looks correct! I was able to click into the base, but when I click the field to see how it's setup I can only "copy field URL". 


Yeap you'll need to duplicate the base to your own workspace!  To do that, click on the base name, followed by the three dots, then 'Duplicate base':


Forum|alt.badge.img+3
  • Author
  • New Participant
  • 3 replies
  • December 17, 2024
TheTimeSavingCo wrote:

Yeap you'll need to duplicate the base to your own workspace!  To do that, click on the base name, followed by the three dots, then 'Duplicate base':


Thank you for all the help!


Reply