Calculating Dates from all Related Records

[3guk]

Hey Guys,

 

I have two tables "Projects" "Sub-Projects", each master projet has several subprojects attached to it linked by ID. Every one of these subprojects has a start and end date.

 

What I'd like a is two calculation fields in the projects table, "overall start date" and "overall end date"

 

-start date which looks through all of the start dates in the related subprojects and finds the earliest.

-end date which looks through all of the end dates in the related subprojets and finds the latest.

 

Any ideas how I would go about doing this ?

 

Ideally the dates are stored, but updated fairly regularly as subproject dates change quite regularly in the database.

 

Thanks in advance

 

James

[doughemi]

Projects::overall start date = Min(Subprojects::start date)

Projects::overall end date = Max(Subprojects::end date)

 

Edit:  You may not want to display an overall end date until all sub projects are completed:

 

Projects::overall end date =

If(Count(Subprojects::id) = Count(Subprojects::end date); Max(Subprojects::end date; "")

[Lee Smith]

Please update your profile by showing the Version of FileMaker, OS and Platform you are using. Here is a quick like My Profile to do this.

[3guk]

Projects::overall start date = Min(Subprojects::start date)

Projects::overall end date = Max(Subprojects::end date)

 

Edit:  You may not want to display an overall end date until all sub projects are completed:

 

Projects::overall end date =

If(Count(Subprojects::id) = Count(Subprojects::end date); Max(Subprojects::end date; "")

 

Thanks for the quick response !

 

James

[3guk]

Just returning to this one:

 

I'd like to find the minimum date of all related records which have projects::status = "done"

 

Is there an easy way to do this ?

 

James