1 (edited by papafrankc 2022-04-27 03:20:04)

Topic: Parent/Child relationships

Hi All,
I just discovered a significant problem I'm having in the current program I'm working on.
.
When setting a relationship from a Child table to a Parent table , I thought that MVD would automatically delete the records in the chid table when I deleted the corresponding record in the parent table.
.
It's not happening for me so I assume I'm doing something wrong.  I've attached a short version of my program so you can take a look.
.
This sample program can have one Vessel and many Owners. So when I  delete the Vessel, I would like MVD to remove All of the owners.  I thought that's what the Cascade Delete option was supposed to do?
.
Thanks for checking this out for me.  As I said I must be missing something.
.
Sorry I forgot to add the zip file before, here it is. smile
.
Frank

Post's attachments

Attachment icon Parent-Child.zip 336.7 kb, 162 downloads since 2022-04-27 

2 (edited by sibprogsistem 2022-04-27 05:57:11)

Re: Parent/Child relationships

connection not established
   
   
http://myvisualdatabase.com/forum/misc.php?action=pun_attachment&item=8602&download=0

Post's attachments

Attachment icon Без имени.png 34 kb, 72 downloads since 2022-04-27 

Re: Parent/Child relationships

sibprogsistem
Thanks for your reply.
I checked the Owner table and I did not have the Cascade block checked.  For all my other trials I did check the box. So I must have missed it this last time.
.
Anyway, I checked the box and re-ran the program again.  But I still got the same result.
.
I'm afraid I don't know how to link the 2 tables.  I thought when I checked the Cascade Box in the Owner table that it would link the 2 tables automatically?  I wonder why that didn't happen?
.
After checking that box, I looked the at data in sqLiteStudio and it matches what is in your screenshot. 
.
Any thoughts on how to enable that link would be appreciated.
.
Thanks, Frank

Re: Parent/Child relationships

watch this video example
https://zen.yandex.ru/video/watch/62690 … 6607e9dcc7

Re: Parent/Child relationships

Ahoy Frank!
Your understanding of how cascade-delete works is correct providing the cascade-delete option is ticked in your database schema (there is no separate link between the tables that needs to be maintained for the cascade-delete option - it's all done through the specified relationship between the tables).
The problem lies with the processing flow between your 'start' form and your 'frmmainmenu' form.
1.  Having created a vessel on the 'start' form, you move to the 'frmmainmenu' form using a 'show form' action rather than a 'create record' (or 'show record') action. 
2.  This means that you are not passing the vessel-id through to the form where you then add an owner.  Consequently, when you add an owner, it isn't related to any vessel. 
3.  You can see this in frmmainmenu.tblgridowner by adding the vessel to the grid - the vessel_name column is blank. 
So when you do a 'cascade-delete' of the vessel, the program deletes the vessel but doesn't have any RELATED owners to also remove (you can confirm this in your actual project by looking at the raw data using something like SQLiteStudio or create a temporary form to dump all the data on so you can check it).
You could simply add a combobox to frmmainmenu to select a vessel and then remind the user if no vessel has been assigned to the user if it's left blank (see attached) although it's probably not the way that I'd choose (but neither do I know the details of your project).
Derek.

Post's attachments

Attachment icon Parent-Child.zip 338.32 kb, 184 downloads since 2022-04-27 

Re: Parent/Child relationships

sibprogsistem
.
Thank you for putting together that video for me.  Unfortunately I'm having trouble viewing it.  The resolution of the video is blurry so that I'm unable to read any of the text.  Also it keeps freezing from time to time.
I've tried 2 different browsers and get the same results.
I'll try again later today as it may be an internet issue?
.
I see that Derek has replied also so I'll check out his suggestions later today.
Thanks, Frank

Re: Parent/Child relationships

Derek,
I took a quick look and changed my to Main Menu button from Show Form to Show Record and it looks like that may have fixed my problem.
.
I'll do some more experimenting later today to make sure I'm doing things correctly.
.
I did not know the significance between Show Form and Show Record before now.  I must have been lucky with some of my earlier work.
.
When I get to watch the video from sibprogsistem later today, I'm guessing his example will probably show me the same thing.
.
Thanks for your help
Frank

8 (edited by papafrankc 2022-05-02 02:47:09)

Re: Parent/Child relationships

Derek,
I just got back to my program and I'm still stuck.  I thought your example was working but I can't seem to adapt it to my app.
.
I tried Create Record andShow Record in place of Show Form and things still don't work.
Can you further explain how to use these to enable the cascade delete to work?
.
Also, when I open the program, there is nothing in the Start table Grid, until I add something else.  Then it shows the records from previous entries.
I'd like to be able to start the program and see what is already there so I don't have to add a new Vessel to make the other entries show up.
.
I'm frustrated with myself because I feel I'm missing something basic and can't seem to figure it out.
.
PS: I took a look at the video from sibprogsistem but since much of it is in Russian? I can't seem to follow the steps that he is using. 
.
Thanks again for your patiece.
Frank

Re: Parent/Child relationships

Hi Frank,

I tried Create Record and Show Record in place of Show Form and things still don't work.
Can you further explain how to use these to enable the cascade delete to work?

I'm only guessing here but are you trying it  with data created AFTER you made the appropriate changes or BEFORE?  If it's data from before, then 'cascade/delete' still won't work because the relationships still aren't in place that enable MVD to 'cascade' down through the data (changing the program isn't going to retro-fix any old data).

Also, when I open the program, there is nothing in the Start table Grid, until I add something else.  Then it shows the records from previous entries.
I'd like to be able to start the program and see what is already there so I don't have to add a new Vessel to make the other entries show up.

Again, a guess;  this sounds like
1.  the start tablegrid isn't set to 'show all records from the table' (see screenshot)
or
2.  you've got a 'search' button that you need to click (either manually or by script) when the program first runs.
If you attach your program, I could say with more certainty - as it is, it's more a case of 'it sounds like'!
Derek.

Post's attachments

Attachment icon screenshot.png 56.82 kb, 77 downloads since 2022-05-02 

Re: Parent/Child relationships

Hi Derek, hi Frank.


I tried to slightly simplify your task and solution. Added additional features.
This may be a bit of a difficult decision, but I hope it helps you.
If there are comments, we will try to fix it. See example.

Post's attachments

Attachment icon Ex.zip 335.94 kb, 154 downloads since 2022-05-02 

Re: Parent/Child relationships

Derek, Sparrow
Thank you for the info.
.
Derek,
Yes, the Start tblGrid was not set to show all records.  I hadn't noticed that setting.  I changed it and all is OK.
.
As far as for the cascade delete troubles, yes I have learned that old data needs to be removed before any recent fixes will work. 
.
Sparrow,
Thanks for the example program.  It gives me some new things to check out.  I always learn a lot from the example programs I see from you folks.  I'm going to see what features I may be able to use in my programs.
.
Thanks
Frank