Can you define "most recent" in more detail? Most recent for a specific rental item? Most recent rental transaction of all transactions in the table? or ???
Sorry. Most recent for the specific item.
I'm worried by the title as it says "across tables" as this all seems to need to take place in one table?
In many cases, a "location field" would not be in the transactions table, but in the rental invoice table to which multiple transactions are listed to list all items rented by a specific customer at a specific time, but maybe this is a different "location".
If your transaction fields have a number field with an auto-entered serial number, you can set up a self join relationship that matches by ItemID for all records with a serial number less than the current record's serial number. Then you can specify a sort order for the relationship that sorts by serial number in descending order and the first related record will then be your "most recent" transaction record.
Transactions::_fkItemID = Transactions|Prior::_fkItemID AND
Transactions::__pkTransactionID > Transactions|Prior::__pkTransactionID
Depending on your knowledge and experience, that may be "clear as mud" so please don't hesitate to post back with questions about any part of this that you don't understand.
Still learning FMP so I'm not super clear on your suggestion. I'm not sure if we're on the same page.
I'll clarify what I'm doing to see if that helps us get on the same page.
The value for "Moved to" in the newest record of the transaction log directly for that specific item should autopopulate to "Current location"
The end goal is being able to create layouts that both show where an individual piece of gear is and a layout to show all gear that is in a specific location.
EDIT: PS thank you Phil you are ALL over this forum and are super helpful. Thanks.
Ok, that's much simpler--if I am correct that you have only one record in Equipment for any given item.
Delete CurrentLocation from the Equipment table. You don't need it.
DoubleClick the relationship line between Equipment and EquipTransactionLog. In the dialog that opens, specify a sort order for EquipTransactionLog that will sort the records such that the newest record is the first record. This can be done by sorting an auto-entered serial number field in descending order or you could have an auto-entered timestamp record that enters the creation timestamp and sort it in descending order.
Now, on your Equipment layout, you can place the MovedTo field from EquipTransactionLog on your layout and it will show the location in the most recently created record for that piece of equipment.
Note: this specified sort order in the relationship could adversely affect other parts of your database. If so, you can create a new Tutorial: What are Table Occurrences? of EquipTransactionLog, link it by the same match fields and specify the sort order in this new relationship. Then you'd add the field from this new table occurrence to your layout and the rest of your database will not be affected by this specified sort order.
Perfect! Thank you so much Phil. I'm still wrapping my head around the power that FMP offers.
Maybe not quite perfect--how can I search by current location of a piece of Equipment then?
Can you provide an example of what you need to happen with that search? I can make a guess, but would prefer not to--might guess wrong after all!
A way to find every piece of Equipment where the most recent matched record in the EquipTransactionLog table matches a particular value.
ie: all of my gear that is currently in location "REPAIR"
The simplest approach would be to define a calculation field in Equipment that just copies the related text in MovedTo. Then you can do a search on this field. It may be a bit of a slow search, however.
If you find that it is too slow, you can do some scripting.
Set up an OnObjectSave trigger on the MovedTo field in your portal that works like this:
Set field [ Equipment::CurrentLocation ; EquipTransactionLog::MovedTo ]
This requires careful design as you don't want to allow any methods for changing the location that don't trigger this update so that you can keep it in synch with the location specified in the latest transaction log record--so I wouldn't do this unless the wait time on your finds become too long to be acceptable.