Topic: parent child record relationship doesn't work

Hello community,

Recently I was learning about parent child relationship with tables it seems like it works only one time after that its not working

Example

From first table to another table I was able to create child record after that in third table it stopped working

When I save it gives me sql error I did same thing to connect tabone(parent) to tabtwo(child) it works but when I tried to connect tabtwo(sub_parent) to tabthree(child) it stopped working. and if i do provide extra relationship field to tabthree than adding child record totally gets messed up.

Help Please. sad

Post's attachments

Attachment icon parent-child record.zip 339 kb, 487 downloads since 2017-05-14 

Re: parent child record relationship doesn't work

Hi Kim,
The sql error is to do with Form3 - you have a mixture of fields from tabone, tabtwo and tabthree on this form but your 'save' button is defined to save them all to tabthree.
It would be far easier just to use different forms - one form to maintain tabone fields, one form to maintain tabtwo fields and one form to maintain tabthree fields.
Derek.

Post's attachments

Attachment icon kim.jpg 51 kb, 291 downloads since 2017-05-15 

3 (edited by kim143 2017-05-15 14:01:03)

Re: parent child record relationship doesn't work

thanks @derek I think I was not able to clarify what I was trying to say is in "form2" there is "relation" and "type" and i am able to save them also it shows whats inside those other table field. Both field are part of relationship so they store " id " or number i think so they can relate(preserving relationship). so tabone is related to tabtwo and tabtwo is related to tabthree. So I should be able to get data from tabone and relate to three right??

so pulling out data and preserve relationship to make tabthree child of tabtwo and main parent will be tabone what should i do (just like i did with tabtwo)??

what i am trying to do is:

tabone ----------> tabtwo(also connected to type table) ----------> tabthree
                                   
text1 (is field of tabone)   ----------->tabone.text1----->tabtwo.tabone.text1  (not sure how its done)
                              globe1   ---------->tabtwo.globe1
From type table --->type.type --------->tabtwo.type.type

I just want to preserve the relationship not want to store data. Please show me how its done.

Re: parent child record relationship doesn't work

can anyone please help me with my issue??

Re: parent child record relationship doesn't work

Kim,

Derek is right. Typically through normal MVD actions, you would have a separate update form for each table (tablegrid) and usually they are on their own chain of forms. Having all the tablegrids on one form is fine, but it requires just a little bit of minor scripting to keep them in sync.


The update forms are only to update the table record that the tablegrid is focused on. Usually in a child form there is a combobox to maintain the relationship to the parent, which you correctly have defined. On a child form, If you want to bring in fields from a parent or grandparent table besides the parent combobox, this usually needs a little bit of scripting. You would define these fields either as labels or disabled input fields and then bring in the data from the parent/grandparent tablegrid or directly from the parent/grand parent table through SQLExecute. I revised your project just a little bit to show you. You were actually on the right track. You just can't update more than the record from the tablegrid. Now having said this, with scripting you can do almost anything. So if you wanted to update other tables simultaneously, it would require scripting to accommodate.

Post's attachments

Attachment icon parent-child record 2.zip 585.54 kb, 518 downloads since 2017-05-15 

6 (edited by kim143 2017-05-16 05:52:03)

Re: parent child record relationship doesn't work

Thanks @ehwagner

Guys Correct Me if I am Wrong

1) When we use relationship field it stores data of parent item id (integer) :?

In My case If I do call for parent table then it will be something like this

For tabtwo relationship field are tabtwo.id_type and tabtwo.id_tabone.

2) when I use tabtwo.id_type  i will be able to call "type" field from "type" table and similar with tabtwo.id_tabone I will be able to call all the fields from tabone which are in my case "text1" and "_date".

3)Here is my issue I don't want to store data in other table than tabthree only. neither i want to store in separate table (tried that solution).

Is there any way to do something like this tabthree contail relationship field "tabthree.id_tabtwo" so i can call all the fields from "tabtwo" table but MVD not showing relationship field from it. if somehow i can call "tabtwo.id_tabone" and "tabtwo.id_type" using just "tabthree.id_tabtwo" than I think problem gets solved.

using "tabthree.id_tabtwo"  calling -------> tabtwo all fields including relationships("globe1","_date","globe2","tabtwo.id_tabone","tabtwo.id_type")
so if I select "tabthree.id_tabtwo" then ------> select "tabtwo.id_tabone" and finally calling "text1" field from tabone (preserving relationship)

Is there any way to accomplish that. :?

Re: parent child record relationship doesn't work

I think I am not explaining good enough so other can understand I will come back with image lol smile

Re: parent child record relationship doesn't work

Kim,
I see that nobody has responded yet on your issues with parent-child relationships. I don't know if you still need help, but thought I would share some info on this topic using your project and hopefully I'm understanding your dilemma. I welcome others to provide input as well. I hope I don't confuse you. I apologize in advance if I do. I also apologize for the length of this post. Parent-child concept is not always the easiest no matter what the development environment is. I'll start by saying that Parent-Child record relationship does work in MVD. It's just a matter of how you want it to specifically work. Just a word of caution, it is not all automatic as one may think. Some things are automatic and others are not. Most of the time it will probably require some scripting to do exactly what you want, but not a lot and mainly minor in nature. In general terms using your parent-child scenario, you can place Table 1 and Table 2 fields on the Table 3 form, but you can only update Table 3 fields on that form using the normal MVD Save action. The other fields can only be displayed (disabled fields or set as label captions). Even if you do not disable the fields, they won't update upon save. Here are the answers to your questions.


Question 1 - Yes the parent id is stored on the child table as an integer.


Question 2 - Yes and no. I'm not exactly sure what you mean by "call all the fields from Tabone". The tabtwo.id_tabone field is only a link field with the parent id. It is typically used in a combobox on a child form and it retrieves a single text field from the parent table (in your case from Tabone). That's all it does. It does not do anything with bringing in the date field from tabone. But you can still display the date field on a child form. In your case, If you want to display the date from Table 1 onto Table 3 form, it will require a line of script.


Question 3 - Again I'm not exactly sure what you mean by "call all the fields". Probably the best way to explain how to display Table 1 and Table 2 fields on Table 3 form is to illustrate it with the following image. It is different between "ShowRecord" action and "NewRecord" action and some fields are not automatic.


The following image is a result of "ShowRecord" action from Tablegrid4 edit on Form1. No script was used for Tabthree form. Fields from Table 3 and Table 2 are shown automatically, but not Table 1 fields and the Type table text field. It requires script to retrieve that data.


https://s10.postimg.org/tpd06zfs9/Show_Record_Tab3_Fields.jpgimg host


The following image is a result of "NewRecord" action from Tablegrid4 add on Form1. Again this image shows Tabthree form with no script. MVD clears all fields, including Table 2 and Table 1 fields. Therefore a script needs to be applied to bring in those fields.


https://s13.postimg.org/n989m8p8n/New_Record_Tab3_Fields.jpg


The attached project mimmicks the images above except that fields are filled where necessary with script. I hope this helps. Again, if anyone else has anything to offer to help Kim in understanding this within MVD, please do so.

Post's attachments

Attachment icon parent-child record3.zip 587.45 kb, 550 downloads since 2017-05-18 

Re: parent child record relationship doesn't work

Thank you vary much @ehwagner,

that was very detailed explanation I liked it. What I was thinking was not clearly right way to do it basically what I thought was I will call tabtwo all columns using tabthree foreign key(relationship field found out later lol...)  and while tabtwo already contain foreign key for tabone, I might able to call columns from tabone just using tabthree foreign key clearly I was wrong. calling one foreign key with another