10 Replies Latest reply on Jun 24, 2016 12:08 PM by tomperr

# Are in table related record calculations possible?

Within the same table and a found set, is FM capable of looking at the contents of a field in a separate record and returning results through a calculated field?

EX:  Dates within records will change, but the found set will return 1 of each color, so no conflicts will exist.

Fields-     Colors       Date                    Calc

Red           1/1/16             if( Color =  Red ; Date ; if (color = white ; date ; if (color = blue ; date ;  "")))

White         2/1/16                    "

Blue          3/1/16                    "

• ###### 1. Re: Are in table related record calculations possible?

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.

HTH

Mike

• ###### 2. Re: Are in table related record calculations possible?

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.

Thanks again.

• ###### 3. Re: Are in table related record calculations possible?

What exactly are you trying to extract? Which date?

• ###### 4. Re: Are in table related record calculations possible?

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.

• ###### 5. Re: Are in table related record calculations possible?

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.

• ###### 6. Re: Are in table related record calculations possible?

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.

• ###### 7. Re: Are in table related record calculations possible?

Script it. Don't drive yourself nuts trying to get the calculation engine to do this.

• ###### 8. Re: Are in table related record calculations possible?

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.

• ###### 9. Re: Are in table related record calculations possible?

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?

• ###### 10. Re: Are in table related record calculations possible?

Sorry for the frustration, but I finally got it.

the calc for start is:

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.