Find / Delete Orphaned Records

[Ray in Capetown]

Re: Find / Delete Orphaned Records

My thoughts too Stan,
I have played a bit and there seems no reason not to use it that way. Good catch.
In fact while experimenting I found that you can eliminate opening the the header table and converting the string to a collection, can be done directly with

dim id_coll as U 
id_coll.from_table("header", "id","")
tbl_dtls = table.open("dtls")
tbl_dtls.delete_range(".not. id_coll.exist(id)")
tbl_dtls.pack()
tbl_dtls.close()

[Stan Mathews]

Re: Find / Delete Orphaned Records

You can do that, but.......

an undocumented feature of the method .from_table() is that it does not process/include marked records. I've switched to getting the string and processing that for safety.

[Ray in Capetown]

Re: Find / Delete Orphaned Records

So Stan
You got me started with collections.
I just applied it to (probably its main intention) an application that must constantly apply currency exchange and duty rates throughout.
All it needs is a short in-line .get() to revalue. Instead of linked tables or external record .. really efficiently running thousands of records.
So thanks for that.

[Stan Mathews]

Re: Find / Delete Orphaned Records

really efficiently running thousands of records

Absolutely. I cut one process I have from 380 seconds to 28 seconds.

[Tom Cone Jr]

Re: Find / Delete Orphaned Records

Ray,

If time permits why not write up an explanation and post a sample database (with code) in the code archive. This is something most of us know almost nothing about.

Thanks.

-- tom

[Stan Mathews]

Re: Find / Delete Orphaned Records

Don't know if this will help.

Suppose you need the customer lasname somewhere in Alphasports where the customer_id is available but the customer table is not open. You can easily do a lookup() to obtain the last name

cidvar = "00000028"
? lookup("customer","customer_id = "+quote(cidvar),"lastname")
= "Wong                "

or

cidvar = "00000028"
? lookupc("F",cidvar,"lastname","customer","Customer_0")
= "Wong

The later of course being faster but each time you call the lookup disk activity is required and if the customer table is being used exclusively elsewhere................

To use a collection we populate it with keys and values from the customer table, opening the table only once. Where to populate the collection would depend on when and how often its use is required. Might be in the autoexec, might be in a form oninit. If marked records are used in the application for a purpose and they should not be unmarked in the customer table then the collection can be populated with .initialize().

cidvar = "00000028"
dim global cust_last as U
t = table.open("customer")
t.unmark_range(".T.")
nm = t.name_get()
t.close()
cust_last.from_table("customer","customer_id","lastname")
? cust_last.get(cidvar)
= "Wong"

A side benefit is the .exist() method of the collection. If one needs to know whether a particular customer_id exists in the customer table

is_customer = "00000199"
? cust_last.exist(is_customer)
= .F.

is_customer = "00000033"
? cust_last.exist(is_customer)
= .T.

[Tom Cone Jr]

Re: Find / Delete Orphaned Records

Thanks, Stan.

[Ray in Capetown]

Re: Find / Delete Orphaned Records

Try an example Tom
Open the form, to make it easy press button Currency - change some currency
Change Manfr PRICE, change per qty etc
Press "recalculate".
currency U variable only needs to update with a currency change somewhere else
The currency table is not related to the price table.

The code is on the recalc button change
These are lookups only Stan, so no worry about marked records.

[Stan Mathews]

Re: Find / Delete Orphaned Records

Ray,

I don't think you understood my point. Marked records in the table used in .from_table() are ignored.

dim shared tbValue as U
tbValue.from_table("imprtcurr", "country", "randcost")
? tbValue.exist("EU")
= .T.

?tbValue.get("EU")
= 11

delete tbValue
'now mark the EU record in imprtcurr

dim shared tbValue as U
tbValue.from_table("imprtcurr", "country", "randcost")
? tbValue.exist("EU")
= .F.
? tbValue.get("EU")
= <No data returned>


You'll never have any records marked in imprtcurr? Want to swear it'll never happen? That quirk cost me several hours of investigation and two days of recovery work. Unmarking all is a small price to pay, comparatively.

[Ray in Capetown]

Re: Find / Delete Orphaned Records

Point well made.
Your last sentence, know that too well.
Thanks. also for taking the trouble to convince me.

[Raymond Lyons]

Re: Find / Delete Orphaned Records

Thanks, G.

I eschew the use of referential integrity. ....

-- tom

Why? Just curious.

Ray Lyons

[Al Buchholz]

Re: Find / Delete Orphaned Records

Why? Just curious.

Ray Lyons

For one there is a 256 record limit. not to mention the record locking..

works in some cases, but not in many..

[Tom Cone Jr]

Re: Find / Delete Orphaned Records

HI, Ray.

... what Al said ...

Do I get bonus points for elevating the verbiage to new heights with "eschew" ???

[Al Buchholz]

Re: Find / Delete Orphaned Records

HI, Ray.

... what Al said ...

Do I get bonus points for elevating the verbiage to new heights with "eschew" ???

Triple holiday points for using a word with chew in it during Thanksgiving..