1 Reply Latest reply on Feb 4, 2012 8:10 AM by philmodjunk

    How do I 'get' the 'highest of' record from another table?



      How do I 'get' the 'highest of' record from another table?


       Hi all,

      I need to be able to 'get' the most recent data record from one table into another, but I can't for the life of me, work out how to configure a function or script which will achieve this.

      Any pointers anyone?


        • 1. Re: How do I 'get' the 'highest of' record from another table?

          What identifies the record as "most recent"? Is there a relationship established between the two tables?

          If "most recent" means the record that is most recently created in the table, the last function can return the value of a field in that related table, provided that the field it references contains data. (If it's empy, it's my understanding that it returns data from the last record to contain data in the specified field...)

          Another approach is to use sorting to sort the most recent record to be the first record.

          If each record has an auto-entered serial number sorting in ascending order on this field will make the record with the largest serial number first. Sorting on date or timestamp fields is also used in some cases. If the tables are not related, you can use a script to change to a layout based on that table, sort the records and then go to either the first or last record before copying data into variables and returning to original layout where set field can be used to copy the data from the variables into fields.

          If the tables are related (and the X operator can be used to define a relationship that matches to all records), a sort order can be specified in the relationshipto put the "most recent" record first. Then references to fields in this related table will all refer to the most recent record.