### 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

__minus__

*TotalCharged*__. I'd like to include in this calculation the__

*TotalPaid*__from the previous semester, if this is non-zero and the family has an entry in this previous semester. I have set up a__

*TotalDue*__as a calculation field that is just equal to__

*PreviousSemesterID*__- 1. Then a table occurrence between__

*SemesterID***Family**and

**FamilyPreviousSemester**maintains equality between

__in each and corresponding__

*FamilyID*__and__

*SemesterID*__. This works as expected in that I can place the__

*PreviousSemesterID*__from__

*TotalDue***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**:

__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__

*TotalDue*
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.

Recursive calculations can be used, but only if you have an if, case or other function that will terminate the recursive references when the end of the chain is reached

Wouldn't it make more sense to compute a total due over all semesters for a given family? That can be done without using a recursive function. A summary field or a sum function can be used to compute that total by using an added relationship to a new occurrence of an existing table where you match fields only by FamilyID instead of by FamilyID and SemesterID.