Field dependency/recursion via table occurrence
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.