Do you have this relationship?
Maintable::ID = LogTable::ID
If so, the last function will return the most recent related LogTable record provided no sort order was specified for the relationship.
You can also set up a sorted relationship to the log table that sorts the related records so that the most recently created record is listed first. Then you can simply refer directly to the related fields in the LogTable and the relationship will refer to the most recent related record. Establishing this sort order would require sorting on a field with an auto-entered value such as sorting by a creation date field or a serial number field in descending order.
I actually only have one table, so I just have one table with thousands of records that have the three fields: ID, Status, Timestamp.
Since this table is a read-only view from a SQL database I can only add calculated fields to the table.
Is there not a way to write a calculation that evaluates each record to determine if it is the most recent for the ID based on the timestamp?
You can still add a second occurrence of this table and relate the two occurrences by ID. If you want, you can sort the related records by the TimeStamp field in descending order. This will work as long as you don't have two records created with the same ID an timestamp (Might happen if to different users generate a log record at the same time.)
To add a second occurrence, select the existing table in Manage | Database | Relationships and then click the button with two green plus signs.
You can also perform a find on your existing table for a specific ID, then sort by the timestamp field in descending order to make the most recent record the first one listed.
Ok, thanks - I have set-up the second occurrence in the table but I think I am still missing something.
What do I need to do to get to a table/view that will always list every ID and only their most recent status?
Thanks again for your help, you have become my personal FileMaker lifeline....
You can set up a summary report on a list view layout where you replace the body layout part with a sub summary part "when sorted by" ID.
You can place the ID, This calculation field and any other fields from this table inside the sub summary part.
If you then sort your records by ID, you'll get one row for each ID value in the log table.
You could also import the data from this table in to a FileMaker table with Unique values specified for ID. That table will give you one record for each ID and you could use it in a similar relationship with your calcualtion field defined in this table instead of the log table.