What kind of field is CurrentOwnerId?
CurrentOwnerID is an ordinary text field entered manually in the Lots table. Not calculation, etc.
I think I want to omit records that don't have related records. I did a manual find for blanks in the CurrentOwnerID field in the portal but got "No Records Match ..." so I need another way to omit Previous owners whose OwnerID's don't appear in CurrentOwnerID in any of the Lot records.
CurrentOwnerID is an ordinary text field entered manually in the Lots table.
Ouch! that leaves your system very vulnerable to user data entry errors. I'd use a script to update that field not manual data entry.
But with your current setup, if you are getting records that shouldn't be there, you've got either an error in how you set up your relationships or in how you designed your layout.
Yes. Ouch Indeed. Did it manually just to test the approach.
A script is needed to set the value in one field based on a comparison of values in another field of related records. I'm thinking about how to do this.
The CurrentOwnerID is in Lots. Lots and Owners are linked with a join table, Transactions. Transactions have foreign match fields LotID and OwnerID. Transactions also has TransactionDate. If there are multiple records in Transactions with the same LotID, the one with the most recent date is the CurrentOwner.
The script should capture the OwnerID of the most recent transaction for each lot and set the value of Lots::CurrentOwnerID with that value.
Not sure how to do that. Portion of relationship graph is attached if my explanation above isn't clear.
But your original problem was that you were seeing records in your portal that shouldn't be there. Is that still an issue?
The relationships look correct, so I'd suspect how your layout is designed is the most likely cause of this issue if you still have not figured that out.
No. That was not the problem. (But I admit that I struggled with my explanation yesterday!)
The portal was displaying the correct data but as I cycled through the Owner records, I was seeing Owner records with no values in the portal. In other words, the Owners had no related records in the Lots table -- matching Owners:OwnerID = Lots::CurrentOwnerID because they were Previous Owners. Someone bought the same Lot later.
I figured out how to fix this problem but have not figured out how to script the process of entering the correct OwnerID into Lots::CurrentOwnerID based on the TransactionDate in table Transactions.
You can use the OnObjectSave trigger on the transaction date field to update the current owner ID field in Lots.
But you will also want to limit deleting a transaction record to performing a script to delete the record. That way, your script can delete the record and then perform the same script to update this field just in case the record you deleted was the most recently dated transaction record.
I don't want to delete the transaction record.
I want to replace the value in Lots::CurrentOwnerID with the most recent OwnerID for the Lot in the Transaction table.
When a Lot is purchased, we create a new Transaction record - entering LotID, OwnerID (of new Owner) and the Transaction Date. Since the Transaction Date is more recent, it accurately identifies the Current Owner.
EXAMPLE: Assume the first buyer of Lot 19 in Transaction table was OwnerID #1 who bought the lot in Jan 1, /2012. To identify this person as the current owner, we set Lot:CurrentOwnerID to 1 for Lot #19.
Someone else buys lot #19 two years later on Jan 1, 2014. Their OwnerID is #57. Once the new Transaction record is committed, I want to replace OwnerID 1 in Lot:CurrentOwnerID with 57 for Lot 19 in the Lot table.
Of course you don't want to delete a transaction record, but there may be times when you have to in order to correct a data entry error and you don't want that unlikely event to leave the current owner id field with incorrect data if you have to do that.
The idea is for your script to "replace the value in Lots::CurrentOwnerID with the most recent OwnerID for the Lot in the Transaction table." any time that data in the transaction table changes in a way that may require that this update take place.
The ways that this can happen is:
- A new transaction record is added.
- The Owner ID field is updated.
- The transaction date field in transactions is changed and this makes a different transaction record the "most recent" transaction for a given lot
- The most recently dated transaction record is deleted.
Events 3 and 4 are rare and usually would only happen as a way to correct a mistake, but they can happen so you should allow for that possibility in your system design.
. Events 1 and 3 be handled by the OnObjectSave trigger on the transaction date field if you always manually enter that date. The same script can be performed via OnObjectSave on the Owner ID field to cover the point where you either select or change the owner ID.
And Event 4 requires that delete record script mentioned earlier.
For the first 3 events, your script might look like this:
If [ Not IsEmpty ( Transactions::_fkOwnerID) and Not Isempty ( Transactions::Transactiondate ) ]
Set Field [ Lots::_fkCurrentOwnerID ; Transactions::_fkOwnerID ]
For event 4, you'd leave out the IF step and just update the field.
Thank you. Very helpful.