2 Replies Latest reply on Mar 5, 2013 10:47 PM by DanielPackman

    Field dependency/recursion via table occurrence



      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.

        • 1. Re: Field dependency/recursion via table occurrence

               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.

          • 2. Re: Field dependency/recursion via table occurrence

                 Many thanks. I set up a table occurrence of FamilyUpToCurrent which requires the familyID to be equal but the semesterID is less than or equal to the Family's semesterID. Then in this table occurence I found sum( TotalCharged) - sum( TotalPaid) and this seems to do the trick for the TotalDue. I left the variable of PreviousTotal by itself since it is useful to display, even if I can't easily do computations with it.