"Recursive" relationships

[xochi]

Suppose I have a table with a self-relation such that each record is related to the prior record (e.g. the one with a serial number one lower), call this relationship : "SelfPriorRecord"

 

What happens if I make a calculated field which is defined as

cCalcField =   SelfPriorRecord::cCalcField + 1

 

To evaluate this, filemaker would have to figure out the result of the prior record's value, which means it has to look at the prior record's prior record's value, which requires the record that is prior to the prior to the prior record, etc.

 

Does FileMaker have any rules about how many layers deep it will search in such a calculation, if at all?

 

 

[IdealData]

Technical specifications of FileMaker Pro 12 and FileMaker Pro 12 Advanced | FileMaker

 

Maybe there's a clue (at the very bottom of the page), although 'recursive' calculation via relationship is not explicitly stated.

[xochi]

I ran a test, and if I set up the calculated field as a calculation, then I just get "?" as the result.   Somehow FileMaker just "knows" that it's not going to work.

 

However, if I set it up as an auto-enter field that is triggered and indexed, as described here: http://fmforums.com/forum/topic/38872-triggered-stored-auto-enter-calcs-technique/  then it works fine, as expected.

 

How do it know?

[IdealData]

Because the calculation is based on a relationship then it cannot be stored, which will make the relationship fail as relationships require indexed fields on the child side, and indexed fields must be stored.

Catch 2::2

[xochi]

Thanks, I believe that makes sense (the lack of an index on the child-side of the relationship).  What's odd is that FM 11 doesn't warn about this - I know that in some cases when you create an invalid relationship, FM will say "This relationship can't work because ..." but not in this case.

[IdealData]

If you create your relationship firstly with valid fields, but subsequently change the child-sde field to unstored you won't get a warning about relationships that will fail either.

Is there any mileage with ExecuteSQL here? You can define relationships 'on the fly' and are not restricted by FMP.