5 Replies Latest reply on Jan 2, 2016 9:28 AM by rmittelman

    Maximum ID from Related Record


      I have a job-search database.  One table, Agencies, has a one-to-many relationship with another table, Jobs.

      I would like to list the agencies in descending order of activity.  So the agency with the newest job entered would be first in the list.

      This seems easy, but I'm blanking on how to accomplish it.  I think I need a field in each agency record containing the highest job ID of its related records.  I just can't remember how to do this.  Any help is appreciated.

        • 1. Re: Maximum ID from Related Record

          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 ).




          • 2. Re: Maximum ID from Related Record

            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

            • 3. Re: Maximum ID from Related Record

              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.

              • 4. Re: Maximum ID from Related Record

                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.

                • 5. Re: Maximum ID from Related Record

                  Thanks coherentkris.  That worked like a charm.