1 Reply Latest reply on Apr 14, 2015 12:32 PM by philmodjunk

    Combining latest fields from multiple related records

    AlexChan

      Title

      Combining latest fields from multiple related records

      Post

      I'm just starting out with Filemaker, so excuse my lack of knowledge.

      I've got a database that is storing information regarding subjects and various measurements of each of the subjects.  It is structured so that there is a table called subject with subject names and a unique subject_id.  This is related to a table or measurements using subject_id, and in the measurements are a list of 30 possible measurements like "height", "weight", "body fat", etc as well as the date of measurement.  Each subject may multiple related measurement records, and each record may only be partially filled-out.

      I'm trying to find a way to generate another "summary" table that automatically lists each of the subjects in the subject table and shows the latest measurements as of a particular date for each of the measurement fields.  I want to be able to use this to "backtrack" in time and see what the latest measurements were on a given date.  I've looked into making various calculation fields but couldn't find a way to do exactly what I want.

      For example:

      subjects:

                                                                                                                                                                                   
      subject_idname
      1John Doe
      2Jane Doe

       

      measurements:

                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                     
      subject_iddateheightweightbody_fat
      11/1/20001706012
      12/1/2001 65 
      22/4/20011555818
      13/10/2010  15
      24/15/2012156 20

       

      I want to be able to generate a table with the latest measurements as of some date (5/5/2005 in this example):

                                                                                                                                                                                                                                                                                                                                   
      subject_idnameheightweightbody_fat
      1John Doe1706512
      2Jane Doe1565818

       

      What's the best way to go about doing this?  For some reason I have a feeling I might need to use a SQL statement, but I really have no idea where to begin.

        • 1. Re: Combining latest fields from multiple related records
          philmodjunk

          I suggest changing the structure of your table of measurements to have these fields:

          SubjectID
          MeasurementType (stores text such as "Height, Weight, body fat,....")
          Measurement (number)
          Date (auto-enter creation date)

          This by design, will eliminate the empty fields found in your current design.

          One row filtered portals can then show the most recent measurements for each type in columns. Calculation fields using ExecuteSQL could be used in place of the one row portals.