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

    Combining latest fields from multiple related records



      Combining latest fields from multiple related records


      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:


      1John Doe
      2Jane Doe



      12/1/2001 65 
      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):

      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

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

          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.