### Title

Field dependency/recursion via table occurrence

### Post

Hi,

I have several tables that all have an index corresponding to a "semester" with sequential semesters having number that are one greater. The tables corresponding to a given family are connected via a relationship having their familyID and their semesterID equal. This works well and I can examine a given semester by limiting to a given semesterID. One field in this table is a calculation of *TotalDue* which is equal to *TotalCharged* minus *TotalPaid*. I'd like to include in this calculation the *TotalDue* from the previous semester, if this is non-zero and the family has an entry in this previous semester. I have set up a *PreviousSemesterID* as a calculation field that is just equal to *SemesterID* - 1. Then a table occurrence between **Family** and **FamilyPreviousSemester** maintains equality between *FamilyID* in each and corresponding *SemesterID* and *PreviousSemesterID*. This works as expected in that I can place the *TotalDue* from **FamilyPreviousSemester** on a layout for **Family** and it shows the previous amount due, as expected.

But now I want to include this previous balance in the calculation for *TotalDue*. If I change the calculation for this field to just include **FamilyPreviousSemester**:*TotalDue* I get the dreaded ? indicated that something is amiss in the calculation. Is this recursive definition disallowed? I can imagine some unpleasant workarounds involving triggered scripts, but perhaps there is a better way? I thought this was the same issue as cumulative totals as discussed here: http://forums.filemaker.com/posts/83e1e75361?page=2

but I defined a new field *PreviousTotal* = if ( isEmpty (FamiliesPrevious::TotalDue) : 0 : FamiliesPrevious::TotalDue)

all is well with this field until I try to add it to the calculation for __TotalDue __and then the question marks return.