Need help with deleting 3rd child records while deleting the parent

[markbwillard]

Hello:

I was able to delete the 2nd child record that matches with the parent but I was not able to delete the information that matches the product table.

With the delete button I would like to delete all record associated with the ID.

Thanks.
Mark

[johnkoh]

Re: Need help with deleting 3rd child records while deleting the parent

Mark,

On your button code, you did not command to delete 3rd table. you did only "sub" table.
your key Id were set as "N" type, I think you should use "C" type. I will not write the code for you, but the direction what to do.

parentform.commit()
tbl_src= table.current(1)
if tbl_src.pid = pid.value
tbl_dest = table.get("sub") '<== you only said the 1 table. did not point the 3rd table.
tbl_dest.delete_range("Pid = "+Pid.value)
end if
tbl_src.delete_record()
parentform.Resynch()


'I do this way
'Find the parent Id value
vParId = parentform:pId.value
'or table method

dim tSub as p
tSub = table.get("sub") 'or table.current(2)
'Find the matching records
'How to? -- See help to know detail
'If found the records then
'<tbl>.delete_range() 'will delete all
'see <tbl>.delete or <tbl>.delete_range

dim tPrice as p
tPrice = table.get("price") 'or table.current(3)
'same thing as above

'Refresh form and

end

[johnkoh]

Re: Need help with deleting 3rd child records while deleting the parent

Mark,

also, Study about "Primary key and Foreign key".

[Stan Mathews]

Re: Need help with deleting 3rd child records while deleting the parent

This solution is probably not what most would use.

parentform.commit()
prc_list = table.external_record_content_get("sub","alltrim(str(id))","id","pid = "+pid.value)
tbl_src= table.current(1)
tbl_prc = table.get("price")
for each prc_id in prc_list
tbl_prc.delete_range("id = "+prc_id.value)
next
tbl_dest = table.get("sub")
tbl_dest.delete_range("Pid = "+Pid.value)
tbl_src.delete_record()
parentform.Resynch()

[Ray in Capetown]

Re: Need help with deleting 3rd child records while deleting the parent

John
Your method is made simpler by leaving the primary delete for last
then all you need is

dim tbl as P
dim a_tbl as P
dim b_tbl as P

a_tbl = table.current(2)
a_tbl.delete_range()
b_tbl = table.current(3)
b_tbl.delete_range()

tbl = table.current()
tbl.change_begin()
tbl.delete()
tbl.change_end(.T.)

[johnkoh]

Re: Need help with deleting 3rd child records while deleting the parent

Ray,

Thank you.
Beginner is required the step by step. I am a beginner and prefer the easy way to understand.

[Tom Cone Jr]

Re: Need help with deleting 3rd child records while deleting the parent

Stan, I like your approach.

I've taken the liberty of adding explanatory comments so folks can get the benefit of your work
without having to study the example data set presented in this thread.

Please let me know if I've got anything cross-wise.

'Stan Mathews example deleting linked child and grandchild table records

'This runs from a button on the form

'Main is primary table
'    Sub is one to many child, linked on Pid fields
'        Price is one to one grandchild (linked to child) linked on Id fields


'save any pending edits
parentform.commit()

' build crlf delimited list of "Id" field values from the Sub table, ordered by ID, but
'      only include Id's if they are in records that contain a Pid that matches
'      the Pid in the Main table record displayed in the form.  
prc_list = table.external_record_content_get("sub","alltrim(str(id))","id","pid = "+pid.value)


tbl_src= table.current(1)    'get pointer to the primary table in the set supporting the form

tbl_prc = table.get("price") 'get pointer to the grandchild table


'delete Price table records by looping through the list of Id's
'   and for each Id in the list delete any records that contain an ID that matches the current id in the list
'   NOTE: in the next line the term "prc_id" is an arbitrary placeholder variable that
'          refers to each item in the list, one at a time.  To get the "value" of that
'          item you use the "value" property of the placeholder variable.  i.e.  "prc_id.value"
for each prc_id in prc_list  'Loop through the list of id's
tbl_prc.delete_range("id = "+prc_id.value)
next


tbl_dest = table.get("sub") 'get pointer to the Sub table 

' now delete all the records in the Sub table that have the same Pid field
'     value as appears in the Main table record currently displayed in the form
tbl_dest.delete_range("Pid = "+Pid.value)

' and finally, using the pointer to the primary table in the set, delete
'    the current Main table record
tbl_src.delete_record()

' resynch the form as the script ends
parentform.Resynch()

[Stan Mathews]

Re: Need help with deleting 3rd child records while deleting the parent

Tom,

Nice explanation. The only thing I can think to add is in this line

tbl_prc.delete_range("id = "+prc_id.value)

prc_id.value refers to the value property of the prc_id pointer variable established in the for each header line. Since prc_id is not a form or browse field it should be clear that prc_id is not a form or browse object but it won't hurt to explain the derivation.

Ray,

Another good approach but don't you need to reverse the sequence?

a_tbl = table.current(2)
a_tbl.delete_range()
b_tbl = table.current(3)
b_tbl.delete_range()

If you delete the child records first I don't see how the delete range on the grandchild table will know which records are referenced as they have been orphaned. It may be that you have tested your script and it does work but it seems counterintuitive to me.

[Ray in Capetown]

Re: Need help with deleting 3rd child records while deleting the parent

A complete oversight on my part Stan.
Absolutely, thanks for checking.
I had tested it with table(2) and then just repeated the incantation.

[Ray in Capetown]

Re: Need help with deleting 3rd child records while deleting the parent

Errata.
In case anyone tries my sample code and misses Stan's valid point, do it in the following order.

dim tbl as P
dim a_tbl as P
dim b_tbl as P

b_tbl = table.current(3)
b_tbl.delete_range()
a_tbl = table.current(2)
a_tbl.delete_range()

tbl = table.current()
tbl.change_begin()
tbl.delete()
tbl.change_end(.T.)

[Tom Cone Jr]

Re: Need help with deleting 3rd child records while deleting the parent

Thanks, Stan,

I've tweaked the explanation a bit and agree that its now more complete.

-- tom

[johnkoh]

Re: Need help with deleting 3rd child records while deleting the parent

If you delete the child records first I don't see how the delete range on the grandchild table will know which records

If the set is
Parent
===>Child
=======>Grandchild


(1) Get the Key Id from Parent table
vKeyId = parentform:KeyId.value

(2) Find the matching records from child table
tChild = table.current(2)
'find the matching records and get the Child Pk before delete it
'loop the records
vChildId = tchild.Child_id.value

(3) Find the matching records from Grand child table.
tGrand = table.current(3)
'Find the matching records with vChildId
tGrand.delete_range()

(4) Delete the child table here
tChild.change_begin()
tChild.delete()
tChild.change_end(.t.)
End Loop

(5) I do not want to delete the parent records.

[Stan Mathews]

Re: Need help with deleting 3rd child records while deleting the parent

Correct, but that's not what Ray's script as posted originally did. I agree that deleting the grandchild records first is mandatory.

[markbwillard]

Re: Need help with deleting 3rd child records while deleting the parent

I found a glitch on this.

When I press the "Delete" button it deletes records correctly BUT being on the same form the PID (auto increment filed) does not show auto incremeted value.

parentform.commit()

prc_list = table.external_record_content_get("sub","alltrim(str(id))","id","pid = "+pid.value)
tbl_src= table.current(1)    'get pointer to the primary table in the set supporting the form
tbl_prc = table.get("price") 'get pointer to the grandchild table
for each prc_id in prc_list  'Loop through the list of id's
tbl_prc.delete_range("id = "+prc_id.value)
next
tbl_dest = table.get("sub") 'get pointer to the Sub table 
tbl_dest.delete_range("Pid = "+Pid.value)
tbl_src.delete_record()
parentform.Resynch()

Stan, what do you mean by we need to add "tbl_prc.delete_range("id = "+prc_id.value)". where?

Thanks all. I was busy with other things.

[Stan Mathews]

Re: Need help with deleting 3rd child records while deleting the parent

My comment to Tom concerning the remarks he added.

The only thing I can think to add is in this line ........ (an explanation).