AnsweredAssumed Answered

Calculation to get the value of a field based on Max of another field

Question asked by dpwrussell on Mar 28, 2016
Latest reply on Mar 29, 2016 by dtcgnet

This would take me about 2 seconds in SQL, but I can't seem to figure out how to do this in Filemaker.


Basic example:


Table 1 has:

id (number)

details (text)


Table 2 has:

t1_number (number) - Relationship to Table1:id (1-M)

recorded (data)

value (text)


Each Table1 record has several Table2 records.


The idea is to add a calculation field to table 1 that displays the most recent contents of the field value in table 2.


I know how to make a calculation on Table 1 which gets the max value of the recorded field: Max ( Table2::recorded ), but what I want to do is find the record in Table 2 that has the most recent recorded date, then return the value field for that record.


In SQL I would just do it with ORDER BY. something like:


SELECT table_name2.value from table_name2 JOIN table_name1 ON WHERE ORDER BY table_name2.recorded DESC LIMIT 1;