1 Reply Latest reply on Dec 22, 2016 11:03 AM by philmodjunk

    Create a calculation field based on data pulled from another table


      Hello, all.


      I'm fairly new to Filemaker and need to create a field that calculates the deposit amount for an individual student based on net tuition amount charged to that individual student for the previous year.  The calculated deposit field will be in the student's current year table and will pull from the student tuition history table.  The two tables are related by student ID and by academic year.  I'm not sure how to do this or if can even work as a calculated field.  Perhaps I need to write a script to do the calculation and then populate the field?  Thoughts?

        • 1. Re: Create a calculation field based on data pulled from another table

          If your relationship matches to one and only one record, you can simply refer to the field from the related table in your calculation such as:


          Do some calculations here + StudentTuitionHistory::TuitionAmount


          In a field of type calculation, this will produce an unstored calculation field so you may need to consider the consequences of that.


          A number field with an auto-enter calculation could also use this expression, but it would not automatically update should the value from the related table be later modified so you get a stored, indexed value, but with limitations.


          Some developers find that scripts performed from script triggers work better if they need a value that is always up to date and that is a stored, indexed value. This takes much more effort on the part of the developer so I don't advise tis option unless you have a specific reason that precludes other options.


          If your relationship does not match to the correct single record, you'll need to either set up a different relationship that does or use one of several other means that exist for calculating the needed value.