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 table_name2.t1=table_name1.id WHERE table_name1.id=1 ORDER BY table_name2.recorded DESC LIMIT 1;

 

Thanks

Outcomes