AnsweredAssumed Answered

Combining latest fields from multiple related records

Question asked by AlexChan on Apr 14, 2015
Latest reply on Apr 14, 2015 by philmodjunk


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.