5 Replies Latest reply on Jun 17, 2011 12:16 AM by matt.rowe

    Most recent value from related table

    matt.rowe

      Title

      Most recent value from related table

      Post

      Hi

      Slightly muddled FM newbie.

      I have a table called Investments related to table called Valuations (on-to-many).  I want to populate Investments::Current Price with the most recent Valuations::Unit Price, based on Valuations::Valuation Date.  If I need to use this value in later calculations does it need to be stored? The Investments and Valuations tables are represented in a single Layout with a portal showing the Valuations (portal is sorted in descending order).

      Any help would be much appreciated.

        • 1. Re: Most recent value from related table
          philmodjunk

          Option 1:

          The last function will return the last related record where the field you refer to is not empty.

          Option 2:

          If you define a sort order for the relationship that orders the related records such that the most recently created record sorts out as the first record, you can just refer directly to the related record's fields and you will be accessing the most recently added related record.

          • 2. Re: Most recent value from related table
            matt.rowe

            Thanks for your help, much easier than fiddling around with Calculations/Scripts etc.

            • 3. Re: Most recent value from related table
              matt.rowe

              It's worth noting that:

              Option 2 does not work if I apply a sort to a relationship based on a new TO of the table i.e. I have to apply the sort to the original relationship between the two original tables in the Relationships Graph.  This seems like odd behaviour to me, as I thought that the whole point of creating a new TO, is to be able to relate/sort tables based on the requirements of my Layouts, which could easily be different from the original ER type graph.  Am I missing something?

              Also Option 1 is Ok providing a user doesn't edit a Valuations::Valuation Date entry afterwards, as this function just returns the last record regardless of "Valuation Date".

              • 4. Re: Most recent value from related table
                philmodjunk

                If you use a new TO, then to access the data in the most recent record, you'll need to refer to the fields of that record by using that new TO as your table name.

                Example:

                IF you add this relationship:

                MainTable::PrimaryKey = SortedRecords::ForeignKey

                With the needed sort order specified for this relationship.

                Then a calculation that refers to SortedRecorsd::DataField will return the value of data field for the most recent record. If you want to display this data on a layout, you can add fields from SortedRecords to your layout and you'll see data from the most recent records. (Though in this case a sorted portal becomes an option when all you need to do is display the data.)

                The Last function only works if the order of the related records puts the desired record last. If there is no sort order specified for the relationship, the "last" related record will be the the most recently created record. If creation order of your records doesn't put the "most recent" record last, then you'd need a sort order for this relationship as well.

                • 5. Re: Most recent value from related table
                  matt.rowe

                  I should have known better and fully checked my work before posting.

                  Of course you are absolutely correct, I had forgotten to re-reference the TO in my calculation fields.  Basic error and thanks for your help once again.