1 Reply Latest reply on Oct 30, 2009 2:45 PM by philmodjunk

    Select record with highest value in certain field and transfer value of other fields to other table

    boismaison

      Title

      Select record with highest value in certain field and transfer value of other fields to other table

      Post

      In my FM10-database I need to select the record with the highest value in a certain field (say field "MaxValue") and next I want to put the value of other field (say "productname" in the selected record in a field of another table. I know I can use the function "MAX" to select the highest value, however I am not able to figure out how to transfer values of other fields to other tables.

        • 1. Re: Select record with highest value in certain field and transfer value of other fields to other table
          philmodjunk
            

          The easiest way to select the record with the maximum value (instead of just finding the maximum value) is to sort the records in descending order on that field. You can either sort the records in a found set or in a portal of sorted records.

           

          Either way, the first record will then hold the maximum value and you can use Go to Record [first] (sorted found set) or Go to Portal Row [First] (Sorted portal) to find the record with the maximum value.

           

          To move values to records in other tables, one generally uses either Set Variable and/or Set Field to move the data.

           

          It might look like this:

          Go To Layout ["YourLayout" (YourTable)]

          Show All Records

          Sort Records [No dialog, Restore] //sort in descending order so max value record is first

          Go to Record [first]

          Set Variable [$Name; value: YourTable::NameField]

          Go To layout [Some other layout (Some other table)]

          Perform Find [restore] // do something here to find the record you want to receive this data

          Set Field [Yourothertable::NameField; $Name]

           

          This is very general and there are a lot of variations possible.