Script to change values in Found Set
My question is related to a join table (Transactions_JOin) that is part of a system to manage various aspects of a neighborhood Homeowner Association.
This question involves the interaction between three tables. Owners. Transactions_JOin. Lots.
Transactions_JOin is a join table linking Lots to Owners. Current Owners are identified in the Transactions_JOin table with a Status Field. The status field for every record in Transactions can be "Current" or "Previous." It must be one or the other.
The Status field can't be blank. There must be one "Current" for each Lot. e.g. all transactions with the same Lot_ID cannot have a status of "Previous." There must be one (only one) Status = "Current"
For each group of identical Lot_ID's, the one with the most recent transaction date should be set to "Current." All other records with the same Lot_ID should be set to "Previous."
How would I do this via a script?