There can be some gotcha's with sorting by related records, depending on if your list contains only Agencies that have Jobs or not.
You can sort by a related field ( primary key or timestamp in Jobs table ) and it will resolve.
Gotcha: if there are Agencies with no jobs the related fields will return blank values for the sort order and sorting descending on serial number or timestamp will put those atop first.
You could create an unstored calculation field in Jobs with formula Get ( RecordID ) and sort by that, Descending. This will give you Jobs in reverse order of record creation and properly resolve the Agencies. The Unstored Calc will return 0 where no child records are present.
Those Agencies without Jobs will sort in their own record creation order. ( has nothing to do with the calc field ).
Assuming that the primary key field of the jobs table is an auto increment value ....If you put a calculation in the Agencies table that uses Max ( primary key from the jobs) you will be able to use the Max field for sorting purposes
Max Id if the child table in the parent table will work.
I'm wondering however if Get ( RecordID ) would be less of a performance penalty, especially on WAN.
I will do some performance testing when time permits.
This idea originates from Daniel Wood's post on HyperlistV2 at http://www.modularfilemaker.org/module/hyperlist/
Here's a part of his post:
"Now, if you tried to obtain the stored version of the calculation then it acts just like any piece of stored data on the record – and FileMaker Server will send you the whole record. BUT, if you grab the unstored version of the calculation, FileMaker Server is somehow designed in such a way that it can send you just the record ID without sending you the entire record itself (maybe through index or some other means). And it’s FAST, VERY FAST!"
Maybe someone in the forum already has the answer.
Thanks, electon. That article was very informative. In my case, I'm dealing with a small data set, so not too worried about performance. I will definitely check out hyperlist though.
Thanks coherentkris. That worked like a charm.