Finding orphaned records

[Jay Talbott]

What is the best way to find orphaned records?

I have a database where the user deleted parent records (from the Invoice header table) without deleting lines from the invoice (from the Invoice lines table).

Now they want to delete the orphaned records (yes, I know the records take up very little space, that's not the issue).

If I write a script to do this, do I use Keyexist, or is there a better function?

Thanks for your help.

Jay Talbott

[Stephen Williams]

RE: Finding orphaned records

delete_range() is pretty swift.
How do you know the linking value if the parent is gone?
You would not want any confusion!

[CALocklin]

RE: Finding orphaned records

Create a reversed set. Link "line_items> to "header> and then do a query for all records where the linking field in the child table is blank. You should be able to use either "isblank( ""Field_name" ) or simply Field_name = "" if it's a character field.

(Field_name may need to have the form: Child_table->Field_name )

[Ohlen Cartmell]

RE: Finding orphaned records

Here's a simple way to do it, but it's not very fancy.
I open the a table and step through a record at a time and get the value of the linking field. Then use the exist expression to see if a linked record exists in the other table index. If there is no record, I write to the trace window. When all is done, I open the trace window and read the record number from there. You can search children to see if parent records exist or search parents to see if children exist. Someday I'll get fancier and actually have my computer print out a report; but for now, this works for me.


OPTION NOBREAK
dim choice as n
dim chg as p
dim pay as p
dim trn as p
dim tran as c
dim total as n
dim count as n
choice=ui_msg_box("RELAX!","This may take a while.",65)
if choice=2
end
else
end if
trace.clear()
statusbar.clear()
trace.write("Unmatched Charges")
chg=table.open("charges.dbf")
total=chg.records_get()
chg.index_primary_put("tranno")
chg.fetch_first()
count=0
while .not. chg.fetch_eof()
tran=chg.tranno
count=count+1
if .not. exist(tran,"transctn.dbf","tranno")
trace.write(tran)
else
end if
statusbar.percent(count,total)
chg.fetch_next()
end while
chg.close()
statusbar.clear()

[Jay Talbott]

RE: Finding orphaned records

Cal,

How will there be a child record (when I reverse the set)if the parent has already been deleted?

JaY

[Jeff Moses]

RE: Finding orphaned records

James, If you write a script that:
1. opens the parent and child tables.
2. fetches though the child records one at a time.
2. between each fetch_next() in the child table do a query on the linking field value in the parent and then do a numrecs=records_get()
3. if numrecs returns 0 then delte the child record.
If you use this with a WHILE statement its pretty easy to write.

We have a script just for this and it works fine. You may have to wait a minute or two if you have 100,000 in the child table but it will get the job done. I'm sure there is a quicker way but this works for us as we only run it about once year.
Jeff

[Stephen Williams]

RE: Finding orphaned records

Why not
if .not. exist(tran,"transctn.dbf","tranno")
trace.write(tran)
chg.mark()

[Tom Cone Jr]

RE: Finding orphaned records

JaY, that's Cal's point. The one to one link will not create new records. Just step through the new parent table, if a child table record exists great, otherwise, delete the current parent table record.

You could also do it in a global script, without creating an inverted set.
Just have your script step through the original child table, and after each record is fetched, use the exist() function to determine if a record with the current child table's key_value is present in the parent table. If it's present, the child is not orphaned, so leave it alone. If it's not present, then the current record is orphaned and can be deleted.

Ok?

-- tom

[Jay Talbott]

RE: Finding orphaned records

Thanks for all of your suggestions. My friend Barry Rochford suggested the following, and here is what I did.

I first created an extra field in my child table, called it Orphan (C, 5). I next filled all the records with "XXXXX" in that field.

I then ran a posting operation and posted the order number (not co-incidentally C,5) into the child table from the parent table. For 13,000+ records, this took less than 7 seconds. Then I ran a query by table and filtered for all records with "XXXXX" in the Orphan field. I got 166 records and checked a few. Sure enough, they were orphans. I can now delete them.

Thanks again.

Jay Talbott

[French]

RE: Finding orphaned records

more than one way to skin a cat.