Find and delete orphan records

[John May]

I have a DB that keeps records of various kind at an elementary school (I work for a school district). The "students" are in one file and the "activities" are in another. (There are a couple other files, but they aren't affected by this issue) They are tied via a student ID number. Because of the way schools work, I delete all the records in the "student" file and replace them each year in the fall. This of course, leaves some orphan records in the "activities" file. Is there a way to identify those orphaned records after I do the "student" reload and get rid of them so the "activities" file doesn't keep growing?

------------------

[esteshk]

Clone the activities file. Import your student ID#'s from the Student file into the new activities file. Create a relationship between the old and new activities files based on the ID. Set the fields in the new file to lookup from the same fields in the old file. Only records for the current students will be in the new file.

[LiveOak]

If you will set up the relationship between Students and Actvities to "when deleting records in this file, also delete related records", the activities records will automatically be deleted. -bd

[esteshk]

John indicated that he deletes all of the student records in the student file at the end of the year and reloads the next years students. This would be a mix of returning students and new students. In order to maintain data in the activities files for returning students, deleting related records would not be an option as they would all be deleted.

[LiveOak]

Depending upon what you use as an index into the activities file, you may orphan all the records anyway. If you use a serial number, you'll lose the relationship. The only hope is that the district has a consistent student number (many don't) that is available at import. -bd

[John May]

I am using a unique Student ID number at import and also to tie the files together.

Is the "cloning" solution my best bet?

[Moon]

Assuming the student ID number is unique and does not change from year to year, you can set a calculation field in the activities database that tests for a valid relationship to the students.

1. In Activities, create a relationship to students matching student ID to student ID. I will call it "StudentIDRelationship" for this example.

2. Create a calculation field with a number result in Activities called "Valid" as follows"

isvalid(StudentIDRelationship::StudentID)

Search in the "Valid" field for "0" after you have deleted your old students and entered the new list. Those found are the activities no longer related to the current students and can be deleted.

[John May]

GREAT!

This appears to be a perfect solution.

I was struggling with the cloning solution until this popped up. I was thinking the problem with the cloning solution is that I would have to create a new clone each year and also keep the previous files as part of the database.

Thanks so much. I am so grateful for the help from you guys!