Are you suggesting that there are multiple records in the People table with the same "unique PersonID"?
Thank you for your post.
David/uLearnIT brings up a good question. However, from your post, it appears you do have multiple records with the same PersonID. Therefore, you may want to create a script that identifies the current record, marks it "active", and mark all multiples as "archive".
For this example, let's assume the following fields. Change it to fit your needs.
Create a new script with the following steps:
Go to Layout ["Layout #1"]
Enter Browse Mode 
Show All Records
Sort Records [Restore; no dialog] (Sort by PersonID Ascending, EntryDate Descending)
Go to Record/Request/Page [First]
Set Variable [$ID; Value: ""]
If [PersonID = $ID]
Set Field [Status; "Archive"]
Set Field [Status; "Active"]
Set Variable [$ID; Value: PersonID]
Go to Record/Request/Page [Next; Exit after last]
This script goes to the appropriate layout, enters Browse mode and shows all records. From there, we sort the records by PersonID and then by reverse EntryDate order, ensuring the first record in each PersonID group is the most current/active record. Then, we go to the first record in the database.
We will use a variable $ID to keep track of the PersonID, and initialize it to a blank string.
Next, we enter the loop. Inside the loop, we evaluate the PersonID. If it equals the previous record ID, then we mark the current record as "Archive". If it is different, then we know it is the first occurrence of that PersonID and set the Status to "Active". Then, we assign the variable $ID to the current PersonID.
Then, we go to the next record. If we reach the end of the file and all records are processed, we exit the loop.
I hope this helps. If you need clarification for any of the above steps, please let me know.
A given person may have multiple records, however, reflecting changes in position, organization etc over time.
This suggests that your structure is not working well for you. Perhaps you should have a join table between Organizations and People. You might call it Affiliations, and it would contain foreign keys for OganizationID and PeopleID, as well as fields for position, etc.
Thanks for the script suggestion - it works great!
There was one interesting minor issue I had with the Set Field step - it appears that there are two ways to specify the target field and value, but only one "worked". Method (A) uses the two buttons on the script editor window (Specify - to choose the target field; and Specify - to set the value in a calculation window). Method (B) uses the first Specify button to set the target field and then uses the Specify button that comes up on the sub-window where the field is chosen - that also goes to the calculation window. When I used the (A) method the field was filled out; when I used the (B) method, it wasn't (though I could tell that the records were being identified as "active" or "archive" correctly by putting in a custom dialog). The (B) method put extra square brackets around the value (it looked like: SetField[Table::Field["text"]]) , which tipped me off.
I'm a little confused with the two methods you mentioned.
I used Method (A). That is, I clicked the two buttons for "Specify target field" and "Calculated result".
If I understand your Method (B) correctly, I clicked on "Specify target field", I still had to select the field. I don't know how you were able to get the field to automatically display. Once I selected the first "Specify" button, inside that field, the only other "Specify" button was to determine the repetition (in case the field had repeating values). Is this what you are talking about? Instead of looking for a static 3rd or 4th repetition, you can create a calculation to get the desired repetition (last repetition, repetition with a certain date, etc.). I'm assuming this is what you are encountering. Does this make sense?
I think that in Method (B) I ended up putting the desired text value in the repititions field without realizing it. When I clicked the Specify button on the floating window (where I selected the target field) it brought up the Value window (looking just like it does in Method A where it is specifiying the field value), so I overlooked that the button was associated with the Repititions field and that window was for specifying the repitition value.
2 Fold additional related question:
Can a stale record be "locked" to protect it if it dates old than say 18 months? Still viewable, but data unchangeable/protected. (My example doesn't pertain to duplicate information, just any records older than MM/YYYY
If so, can it then be unlocked by password should it require access by management to change any data found to be in error in the future?
Yes, old records can be locked via the security privilege set. Presumably you have a date field that you could refer to in the expression? You need to investigate Privilege sets and look at the options for limiting editing of records.
My problem is related:
In my main table i have the following fiels:
Now i have a second table which is a price list.
It has the following fields: Mutation Date, Itemname, Price1, Price 2, Price 3 (Where the different prices depict different ammounts of the item)
My goal is to set the Item1Price1 field in het main table to the current price as set in the pricelist.
At first I related the two tables using the Date and Mutation date. This way, the price changes in the future are not used and records from the past are not changed with today's prices. I can't however figure out how to make a subselection of the related tables. At default FMP seems to take the first one. I would like to first cancel out all the other items and then pick the most current record. Setting multiple relationships won't do since i would like to retrieve prices for multiple items.