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

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.

Outcomes