I still don't think you need the field.
I would do the following in order to link directly from the current record of Lots to the current owner:
Owners|Current::__pkOwnerID = Lots::_fkcCurrentOwnerID
Owners|Current is a new Tutorial: What are Table Occurrences? of teh Owners table.
Define _fkcCurrentOwnerID as a calculation field with this expression:
As long as the relationship between Lots and Transactions is sorted by Transaction Date in descending order as described in your previous thread, this field will return the ID of the current owner of any given lot.
It IS an unstored calculation, but as long as you use it from the context of Lots to refer to Owners|Current, it will work.
This is still not working as a sorted relationship. I *think* I followed your instructions. The attached image shows the steps I took and the result. Basically, the sorted relationship is not always showing the current owner based on the transaction date. The image combines 7 smaller images that I'm sure will be self-explanatory to you.
If I wanted to use the Status approach (!) what would the script look like that I asked about in my last post?
I ran some tests.
It worked for me, but I had to use a script to update the layout with: Refresh Window [Flush cached Join results]
to get the layout to update to show the right "current owner" data.
Did not work for me. See attached.
Added the script you described. Ran it manually and attached to a script trigger when the layout, Current Owner, loads. Lot # 19 in Current Owners should be Leenan - not Cobb. See Transactions layout, lot #19. Leenan transaction is more recent than Cobb.
Why do I get the impression you're not keen on the Status = Current approach ? :) I thought that was a GREAT idea!
Just kidding of course. I appreciate your help and I know I'm learning something in this process.
It's an extra field that will result in errors if you fail to keep them correctly updated and seems a needless complication.
Of course I'm finding what I am recommending as an alternative is not as simple as I'd like either...
Your _fkcCurrentOwnerID calculation was not correctly defined to evaluate from the context of ow_Lots_Current and it did not reference the Ow | Current::__pkOwnerID field.
Your settings will work, however, but only if you open the Lots to transactions relationship and specify the needed sort order on the transaction date field.
Eureka - but not without some drama.
I tried making the changes outlined in the first paragraph of your reply above and that "broke" the database -- spinning beachball. FileMaker Not Responding, etc. Fortunately, having learned my lesson yesterday, I had a backup so I tried the suggestion in the 2nd paragraph and it worked.
Honestly, I can't say yet that I understand WHY it works. I understood "the Why" of the Status = Current approach but I don't understand your method yet. I'm not asking you to explain it either! For now, I'm just going to accept that it works and hope to understand why later.
Thanks very much for your help and patience.
Thanks, too to Fenton Jones
The breakthrough yesterday created a list of Lots and their current owners but I wasn't able to create a list of Owners and their Lots. I have no idea why. I assumed this would be simple but it wasn't. I tested the idea of identifying the Current Owner for each lot by adding a new field (CurrentOwnerID) to either Lots or Transactions and both approaches seemed to work. The new field is populated with the OwnerID of the current owner. Unfortunately, this is a manual - error prone - process.
I outlined a script to automate this but could use help in making it a real script. Here's my outline:
SCRIPT TO IDENTITY THE CURRENT OWNER OF EACH LOT
# Create a list of each of each LotID based on values from the Lots table.
# Go to a layout showing all Transactions
Loop through the LotID's starting with the first LotID number
Perform a find for each LotID value
If the found count is NOT greater than 1 (only1 transaction)
# Set field Transactions:Current Owner with the Owner ID of the found record
# Go to the next Lot #
If the Found Count is more than 1 …
# Sort by Transaction Date in Descending Order. (most recent on top)
# Get the Owner ID of the first record. (Most recent transaction date)
# Set field Transactions:Current Owner with the Owner ID of the first record
For the remaining records in this found set, set the Current Owner field to blank
# Omit the first record
# For the remaining records, Set field Transactions: Current Owner to blank ""
Go to the next LotID and repeat the process.
# Stop after the last LotID
The changes I specified in my previous post should not have locked up your file. I think you should run a recover on your current copy just to be on the safe side.
the _fkcCurrentOwnereID field is an unstored calculation and has to be due to it's "copying" data from a related record in the transactions table. That works from Lots to Owner, but not from Owner to Lots--which is probably why you are having trouble getting a list of owners and their lots. But keep in mind that each owner can own more than one lot. Such a list would require that you list the same owner more than once--which can't be done from the owner layout anyway. You'd need a portal to transactions that filters out old transaction records for lots that the owner previously but does not currently own. That can be done by adding in more table occurrences or I am beginning to think that a stored, indexed field in Lots that stores the current owner's ID might be the simpler option after all.
Scripts linked to triggers and buttons that delete transaction records (such as to correct an error) can be used to update such a field.