2 Replies Latest reply on Dec 22, 2015 4:22 PM by macwombat

    Automatic updating an autocalculation field




      I am using Filemaker pro 14, I feel that there may be a simple solution.

      It is a medical database. I have a parent table named "patientID" and a child table named "procedure". It is a one-to-many relationship.

      I need to get the latest date from a particular patient's set of procedures and put it into a field on the patientID field so that I can calculate future follow-up dates. Ultimately, I would like to be able to input a date range and see which patients are due for follow-up, but this is not the scope of this question.


      Right now, my problem is in getting the auto-calculation field to update when new data has been input.


      My current less than ideal solution is a loop to "refresh" all values" and it takes way too much computation. I'm sure there is a simpler method, and I was hoping you could help me:


      Show All Records

      Go to Record/Request/Page [First]


      Set variable [mrn$ ; value: patientID::mrn]

      Perform Find [restore] (I find all procedure records that match the MRN)

      Set variable [ $lastprocedure ; Value: Max ( Procedure::dateofprocedure ) ]

      Perform Find [restore] (I find all matching records in the patientID table, which should only be one record)

      Set Field [patientID::latestproceduredate; $lastprocedure]

      Go to Record/Request/Page [Next; Exit after last]

      End Loop


      The idea is that I go through each patient, find the latest procedure date, then copy that into a field in my patientID field.

      Ideally, is there a way for the fields to auto-update? They don't seem to update with me unselecting the field or clicking elsewhere.

        • 1. Re: Automatic updating an autocalculation field

          You could use a simple calc field using Max() of the related dates. If your need is for that date to change every time a new procedure is entered in the related table, then you don't really need an auto-enter, but if you need to store or index this date for some other reason then you could use the same calc in an auto-enter.

          • 2. Re: Automatic updating an autocalculation field

            1.  Create a new Table Occurrence (TO) for the child table (call it LastProcedure).

            2.  Link the parent table to the new TO with the same relationship as your current one.  On the child side of the relationship sort the relationship by your dateofprocedure - make sure the sort is in Descending order.

            3.  On the patient layout simply place the field "LastProcedure::dateofprocedure" on the layout and it will always display the date from the last procedure.

            How does it work?  The field on the layout will show data from the 1st record it finds across that relationship.  Because you have sorted that relationship on dateofprocedure in Descending order - the first record it finds is the most recent one.

            Hope this helps.  Chris