Wanted to clarify the above.
Currently the Time field looks at the Timepoint field and determins a time based on the previous time and a set time between these two points.
As long as there is something in the Timepoint field, the calculations will continue down for each Timepoint.
What I'm trying to do:
If Timepoint 6 in the top example is blank, the Time field should jump to the Next Depart Time. There a calculation will add predertimed minutes to the last Timepoint Time (Timepoint 5 in this example). If Timepoint 6 is not empty, continue with the calculation.
As in the second example, Timepoint 8 is empty, so the Time field from 7 should jump to the Next Depart Time and and to that calculation.
Basically, if the next field is empty go and do the calcualtion in the Next Depart field. If the next field is not empty, continue with that calculation. And so on.
I hope this Clarifies what I am trying to do.
Whenever we see several 'like' fields (in your case multiple Timepoints), it indicates a structural issue. You will continue to have issues working with this type of flat file setup. Are you early enough in your design to consider a different approach? If those Timepoints are RECORDS instead, everything you do with FileMaker will get easier.
This is how I currently have it set up:
Fields in the Timepoints table (All manually entered):
TimepointCode (Code for timepoint)
TimepointDescription (Description of timepoint)
Fields in the Travel Times table:
FromeTimepoint (From Timepoints:TimepointCode)
ToTimepoint (From Timepoints:TimepointCode)
Time (Manually enter minutes)
Fields in the Build Route table:
RouteCode (Calculated based on RouteNumber & RouteDirection, for example 45-O)
RouteNumber (Manually entered, for example 45)
RouteDescription (Manually entered)
RouteDirection (Manually entered, for example Outbound)
Timepoint01 (From Timepoints:TimepointCode)
Timepoint02 (From Timepoints:TimepointCode) (and up to 15)
Minutes from Timepoint01 to Timepoint02 (Manually entered)
Minutes from Timepoint02 to Timepoint03 (Manually entered)(and up to 15)
Fields from Build Schedule Table:
RouteCode (Dropdown from (BuildRoute:RouteCode
BlockNumber (Manually entered)
Timepoint01 (From BuildRoute:Timepoint01) (Same for up to Timepoint15)
Timepoint01 StartTime (Manually entered)
Timepoint02 Time (Calculated by adding Timepoint01 & TravelTimes:Time) (Same up to Timepoint15)
NextDepartTime (Calculated by adding so many minutes to the last Timepoint that is not blank)
All 15 fields may not be used.
I am open to a better way. I can send you the file if it help.