I think that you can, in a loop, by utilizing the functions TableNames, Max, and Evaluate.
set variable = ExecuteSQL ( "SELECT MAX ( " & ts_fieldname & " ) FROM " & tablename ; "" ; "" )
something like that
Looks like an elegant solution but when I use "ExecuteSQL" function in a calculated result the script editor's syntax checker says that "the specified table (my fieldname) cannot be found." It only accepts the syntax when I choose the "Execute SQL" script step, and then I don't know how to get the value out of it.
Actually I'm not entirely sure how to get the value (a timestamp stored in a date field) out using the function either. When I try to display the contents of the ModDate field using the ExecuteSQL function I cannot get the message to display as a date and time, even though I can display the contents directly by simply specifying the table and field.
It would be
ExecuteSQL ( "SELECT MAX ( ModDate ) FROM " & Quote($TableName) ; "" ; "" )
But the result is formatted as SQL standard YYYY-MM-DD, so you need parsing it as your system date format.
These are just a couple of references to FM SQL.. you should read them all
With Show All Records and Sort on each table, your solution will have performance issues, especially over a WAN.
Let's say you have 10 tables in each file. You could make a new Table Occurrence for each table. Relate each table to a Globals table with Cartesian joins ("X" as the relation). Sort each of those table occurrences in descending order based on the ModDate. Your script could then just cycle through each TO, selecting the first related value. The sorts are performed on server, and it would be very fast.
Go to Globals Layout
Set Variable $$LatestContact = ( Table1::ModDate )
Set Variable $$LatestAppointment = ( Table2::ModDate )
Set Variable $$LatestInvoice = ( Table3::ModDate )
Because the relationships would be sorted, the first related record for each table would automatically contain the most recent modification date.
Ok! This works for me. Now the only thing I'm missing is the time of day porting of the field contents. I'm seeing the YYYY-MM-DD but no time information. How do I access that portion of the ModDate field? - or is there a way to access the record ID field of the record that the SELECT MAX field is pointing to? Then I could just go to that record and copy the ModDate field directly to the destination without parsing.
If the ModDate field have time information, it is timestamp, not date. Then you get YYYY-MM-DD HH:MM:SS.
Do you want to get MAX(another time field) in found set of max date?
I don't need another time field but another field of the same record would be useful. The field is called RecNum.
I think there can be two or more records having same max modDate.
Thanks for your help. I've found an embarrassing programmer error: my ModDate field was a Date field rather than a Timestamp field. The field I was comparing against was in fact a Timestamp field which is why one test message worked and the other one didn't. (Doh!) Anyway, I've changed it to a genuine Timestamp now, and although the test message using SELECT MAX is now displaying only a "?" I may not need this check after all. Each time I commit a record I not only update the ModDate timestamp field with the current timestamp, but I also update a Global LastModDate Timestamp field with the current timestamp. Since I'm exporting all modified records between a past date and the present, and importing them into another database, all I really need to do is check the exporting database's LastModDate field. I don't need to check every table I'm importing. Thanks again.