4 Replies Latest reply on Sep 7, 2010 3:26 PM by LaRetta_1

    Calcuation based on child records



      Calcuation based on child records


      I would like to have a field that is a calcuation based on the related child records.  It seems a little tricky since it is not a numerical calculation.  The set up is this:

      Medical records with a parent table for the patient and a child table listing all the laboratory values, 

      Child table has attributes: timepoint (preop, postop) and result (high or low) and value (number)  among others. 

      My goal is to create a field in the parent table that will automatically calculate "yes" or "no" based on the presence of high lab value at preop.  For example, a field that says "Does the patient have endocrine deficiencies"  I know it seems redundant but it is easier for the doctors to see that way.


        • 1. Re: Calcuation based on child records

          If you sort your portal records by result in ascending order, "high" portal rows will be listed at the top of the portal where they will be most visible. Also if you specify this sort at the Relationship level, a calculation in a field or conditional format expression that refers to the related records can check to see if the first such related record has a "high" result and you can then use this result to make the fact more visible to the user.

          • 2. Re: Calcuation based on child records

            The second idea is a possible option for a calcuation for that one field.   Thanks.  The problem is that the goal is to have a second "summary" field that would answer whether any of the lab values are low. (The child table actually has three options high, low, and normal, across various timepoints and types of labs)

            • 3. Re: Calcuation based on child records

              You can set up multiple relationships to the same table of Child records and specify different parameters for each. You might even use calculated "filter" fields that match to different sub sets of the data (works in filemaker 10 and older) or you can set up filtered portals that display just the summary field and which include this same logic in the filter expression. (This works for filemaker 11 only, but keeps your relationship graph a lot simpler in structure.)

              • 4. Re: Calcuation based on child records

                You can try this calculation (result is number) in your parent table:

                not IsEmpty ( FilterValues ( List ( child::LabValue ) ; "Low" ) )

                ... only your primary relationship between Parent and Child is required.  This will produce a 1 if there is a child with a low value.  From layout level, you can then format this field as number boolean and simply type a Yes into the 'true' side of the boolean.  Then place text next to the field called Low Lab Results?

                This can also be handled using only layout-level variables and conditional formatting which saves having to create the calculation at all.  Every field in your field definitions makes it harder to traverse when working in them and every calculation uses resources which will slow your solution down (at various rates).

                UPDATE:  I dislike mentioning a technique without providing at least a link or the process in achieving it so here is the link:


                It is a tremendous technique I picked up from Mr_Vodka (John) and Comment (Michael).