2 Replies Latest reply on Apr 3, 2013 9:01 PM by JWalls

    Retrieving the "nth" record from a calculated UNSTORED field

    JWalls

      Title

      Retrieving the "nth" record from a calculated UNSTORED field

      Post

           I am using a modified version of the Lending Library template (from v11 updated to v12).  After an item has been checked out, I have a layout that displays the "Item", the "Customer" (Patron in our case), and the "Due Date".  The Patron is obtained from the "History Asset ID::Contact Name" field; however, the layout is always displaying the first patron to borrow an item not the "most recent" person to borrow.

           I've looked through the Functions Manual and not identified a function that would help me retrieve the last record associated with the History Asset ID:: Contact Name field.  Any suggestions would be appreciated.

           I've attached a screen shot of our database design.  I am looking for clues on how to enable this capability so that we can see the following:  (a) one layout displays the "checked out" item along with the current person to have it checked out (b) another layout displays the ongoing history of what each person has checked out (c) the item layout displays the ongoing history of who checked out each item.

            

            

            

      DatabaseTables.png

        • 1. Re: Retrieving the "nth" record from a calculated UNSTORED field
          philmodjunk

               It would have helped to know on which of these table occurrences you based your layout, but there is indeed a function that returns the value from the last related record. It's called Last(). But it has one key limitation: it returns the value from the last related record where the value in the field is not blank so this is a detail to keep in mind when you make use of it.

          • 2. Re: Retrieving the "nth" record from a calculated UNSTORED field
            JWalls

                 PhilModjunk,  I regret that I did not provide all the necessary information; however, your response using the Last(field) function was exactly what I needed.  I was able to modify the Assets table to add a Last Patron field.  When I added this field to the report layout, I created a very simple script that called the Last(table name::field).  After testing repeatedly, this appears to solve all of my problems.

                 Thank you for your quick assistance.