Display last record field from portal in another field

[Karlos Fandango]

My database has only three tables...

ITEMS - Which is an inventory of tools for the company.

    • BOOKED - Which records an audit of each time each item is booked in or out of the stores

    • TESTED - Which records an audit of each time an electrical tool is PAT tested [safe to use]

The individual Items are displayed in a list layout called 'Item List' which is basically the 'Home Page'.

The BOOKED events are displayed in a portal in a 'form layout' called 'Item Booking'. I've set the sort order in the portal to show the last 'booking' at the top. The easiest way to this portal is to click the small blue "go" icon in record on the "Item Bookings" layout.

What I have tried to do is display the last "booking location" from the portal [at the top] in the 'Item List] so that at we can see the item's current location without having to view the portal each and every time. I managed to display the latest Booking Primary Key - [Max ( Booked::Book_Pk )] - but couldn't get it to reveal where it was last booked too.

I shall spare you the list of things I have tried but before giving up completely thought to seek help here where I received helped before. Personally, I thought an auto entry by calculation would have been best but I just couldn't figure it out.

Please find the file attached.

Any help would greatly be appreciated.

I have FileMaker 13 on my Mac at home but V15 elsewhere, if you alter the file please export in V13 to spare me having to leave the house to open it up on the other computer [V15].

 

Test-23.fmp12

[bruceR]

1. There is no such thing as a FileMaker 15 format.

[comment]

If you define the relationship (not the portal) between ITEMS and BOOKED to sort the records from BOOKED by Date, descending, you can then place the Location (or any other) field from BOOKED directly onto the layout of ITEMS, and it will show data from the first related (which will be the latest) record in BOOKED.

Alternatively, you could use a one-row portal sorted the same way as your existing portal and place the field/s there.

Or use a calculation field (defined in the ITEMS table) = Last ( BOOKED::Location ). This is assuming records in BOOKED are entered in chronological order or that the relationship is so sorted.

Edited December 16, 2016 by comment

[bruceR]

2. If you sort the RELATIONSHIP instead of the portal; then you can just display the related data directly.

3. There is no need for the extra field.

Test-23MOD.fmp12.zip

[bruceR]

Mod 2, set up as Comment suggests (change calc to Last( Booked::Booked To )

Test-23Mod2.fmp12.zip