I would use a self-joining relationship for this. Join the table to itself (another TO of the same table) and base the relationship on the Color field. Then, all the records in the "related" table will match the current record's color. Extract the date you need via sorting the relationship or looping over a List of them.
Thanks Mike, I figured it was a self join. I am struggling with the next step. Can you provide a simple example of looping over? Not sure how that works.
What exactly are you trying to extract? Which date?
I am trying to extract the list of dates in order to compose a schedule. I cant seem to have the calculation find the contents of a field ie: color and use the color's date to provide the dependent color's date, if that makes sense.
In order for the self-join to work, you're going to have to have a way to tell FileMaker which "Red" is the parent of the "White" and "Blue" (assuming you have multiple "Red" records, and assuming the "Red" record is the parent). You can use some sort of key field that's the same for every record (which would work fine) in combination with a field that calculates out to the correct color as the join criteria.
However, I'm still not clear on what you're trying to do here. Your calculation above makes no sense. It's just going to return the date of the current record. Which date from what record comprises a "Schedule"? If all you want is a list of dates for the found set, then just use a ListOf Summary field on that date. But right now, I don't know what you mean by "dependent" color.
There are lots of ways to extract a specific date from a found set (self-joins, looping, ExecuteSQL). But I need to know exactly what your logic is before we can figure out a good approach. What exactly do these records represent? Why is one dependent on another? Your data structure may be wrong, depending on what it is you're trying to accomplish.
What I am trying to do is create a project schedule but have had to simplify it to it's most basic form. I am trying to create a schedule based on events. 1 event cant occur without another being complete. Each event lasts a certain amount of time before the next event can start. But with every project, one needs the versatility of change. When 1 event date during the process is changed, all subsequent event dates change.
Thank you so much for feedback.
Script it. Don't drive yourself nuts trying to get the calculation engine to do this.
The script is essentially asking to do the same as the calculation. Running into the same issue. The issue is borne and lives in the dependency. If I can get 3 records to work, the foundation will have been established. Even in self join, results do not work.
I have no idea what you're talking about. What does "borne and lives in the dependency" mean? You still haven't told me what you're trying to do. What is the end result you're after? You have Red, White, and Blue. What depends on what? How do you want to create this schedule? Do these records have an established order? What are the criteria for the time delays? How does the system know which record depends on which?
More information is needed before I can help you.
Sorry for the frustration, but I finally got it.
If (not IsEmpty ( iRevStart ) ; iRevStart
; If ( PhaseIdentity = "Phase01" ; Project::Sched Start
; joinSched::cEnd Phase + Phases::DependDays))
calc for end date is:
If ( not IsEmpty ( iRevStart ) ; iRevStart
; cStart Phase + Phases::DUC)
colors represent phases of project. iRevStart = new start date.
Everything else flows.
Thank you for suggestions of join table.