dialog2 create tables

[GGandhi]

hello

i recently upgraded from 10.5 to version 11 and in process of converting many of my grids to dialog2 and also want to learn xdialog2 altogether.
my question is centered around one to many tables
for example if i have two tables
po_header with id, number, auto increment and rest of the fields
po_detail with id, number auto increment, po_header_id and rest of the fields linking via po_header_id
already defined, then build the dialog and bind to the controls, there is no problem i like that very much.

however, if i build the dialog and ask it to create two tables, it does a good job but the linking field is not correct. the dialog creates a po_header_id in addition to the id i have added, but po_header_id is not in the child table but id i have added is present. i think that should not be the case. what i think should be the id i have added to the dialog should be the primary key for the po_header and not po_header_id the dialog creates by itself. (ther is no way i can leave that field because i can not complete the creation without the linking field)

or i am not doing something i should have done in the steps. i went thru few times with the same end result.
will some one, who has done this, please tell me what steps i should be doing,
thanks in advance.

[Charles Hoens]

Re: Xdialog2 create tables

Not really sure what you are going for here i.e. creating tables. Why would you want to create new tables? In any event, what I think you are trying to do can be done with XBasic after you submit your dialog, not within the dialog itself.

Hope that makes sense and helps you.

Charlie

[GGandhi]

Re: Xdialog2 create tables

addendum:

i watched the videos again (d12) the dialog is designed that way.
that means i have to provide po_id instead of the auto increment field that i have been using to link the po_header and the detail.
my idea and their idea are not the same. probably they did it for a reason. i will have to find out eventually.
in the meantime thanks for reading.

[GGandhi]

Re: Xdialog2 create tables

follow up:

on further investigating, even in their own alphasports example they follow standard definition i.e using primary key of the parent as foreign key of the child in one to many relationship, that is how i learnt all along. in the xdialog2 (video d12) they have NOT followed that scheme. i think their logic is NOT correct. they should make the linking field, the primary key of the parent table then the building tables will be perfect. ( if i or another person enter the same number in the linking field the data will be corrupted, an auto increment field will prevent that, is that correct?)

[Charles Hoens]

Re: Xdialog2 create tables

Well we had a lot of discussion about autoincrement a while back. You can search the forum for that thread. The long and short of it all was that when you are using server (as opposed to desktop) then you should not use autoincrement in the field definitions. That's because you never can be certain that the record you are autoincrementing is the last record in the database. Someone else might slip in ahead of you. So you need to add the autoincrement by using the aftersubmit, calculating the key and then saving it to the record. Once that key is calculated you can use it in the child dbf.

If I find the thread I'll post it for you.

Charlie

[Steve Wood]

Re: Xdialog2 create tables

Why shouldn't he use auto-increment for the id field in po_header? The proper sequence to me is:

• Create the po_header record, using auto-increment for the ID field. Save that record so the ID is generated and record exists.
• Create one or more po_detail child records using link to set the po_header_id to match the parent ID field.

It doesn't matter if "Someone else might slip in ahead of you." as they would just have a different value for the ID field in the header. Auto-increment is not based on the last field, it is based on the highest value for the primary key, regardless of where that record is in the stack. This was changed around V8 or 9 where it used to be the value of the pk for the "last record".

[GGandhi]

Re: Xdialog2 create tables

i think what steve says is correct.

in general what i have learnt, in all the examples i have seen, the primary key of the parent table is the foreign key in the child table. that can be easily accomplished with an auto increment field in the parent table and get their value and add it to the child table as foreign key. one of the argument against auto increment field in linking in alphafive was ( or may be still is) that alphafive does not actually generate the auto increment field, it only shows the likely next number. so two people working on the same table adding new records will see the same number in the primary column and there is a possibility of mixing up two records. or seeing ghost records in networked environment. i think that is not the case when you save the record and get the primary key then add the child records, as it happens with xdialog2.

i still think as i said before the primary key, whether auto incremented or otherwise created by the programmer, should be the foreign key in the dependent child records in order to ensure proper referential integrity. if someone deletes the parent record then all the linking records for that primary key can be easily deleted. on the other hand if some user entered field is the linking field we must make sure proper child records are deleted, not accidentally remove records that are not meant to be or leave behind ghost records to hang around.

my argument is based on the representation on the video d12 version 11 videos. i think that is not correct.

[Selwyn Rabins]

Re: Xdialog2 create tables

When you want to build a dialog component that is bound to tables you have two choices:

1. bind the controls in the dialog to existing tables
2. have a5 create the tables and then automatically bind the controls to the tables it just created.

if you choose method 1 then you have to do more work yourself (you have to create the tables). but you get more flexibility in the way the tables are created and you can model your data exactly was you want.

if you choose method 2, then a5 will create all of the tables it needs. it will create an auto-increment primary key for each table and will include the linking fields from the parent table in all child tables.
there is nothing 'wrong' with this - even though it might not have been how you would have designed the tables if you used method 1.

if you want more control over how the tables are defined, use method 1.

[GGandhi]

Re: Xdialog2 create tables

thank you for your answer mr.rabins

there is nothing 'wrong' with this - even though it might not have been how you would have designed the tables if you used method 1.

i am surprised that you, of all the people, will say this. you will not be able to give any reference where the primary key of parent is not the preferred foreign, linking key of the child table. that is asking for trouble. you will never know which parent has which child, that is not the way to design a proper relational database. yes i will go on to design my table and then link the table to the dialog. that is not a big deal, it is wrong to link some field to the child against better practices and say "nothing wrong with this". linking any other field will not, in my opinion, ensure referential integrity, please feel free to correct me if i am wrong, you have more experience than i.

i am quoting from other sources

For referential integrity to hold in a relational database, any field in a table that is declared a foreign key can contain only values from a parent table's primary key

[peteconway]

Re: Xdialog2 create tables

I think when the term Primary key was used in relation to the dialog2 was a poor choice of word, as it's a reserved word for a very specific and important action in the table, I view it as the root value you decide to use as the basis of the Dialog2's connections to other tables, grids and dialogs. - Again, just my view on hoe I approach the use of the term. - Happy New Year to all - been 2013 for 15 hours here in Australia.

[bob9145]

Re: Xdialog2 create tables

The data binding builder for a repeating section allows you to create the link from PK of the parent table to the FK of the child table. So if you auto increment the primary key of parent and child table the builder will write the code to populate the FK in the child table with the auto incremented key of the parent. This follows the rules of normalization the way I understand them.Not sure what all the fuss is about there but I'll check out the other thread.

My question along these lines is:

The component data binding builder doesn't allow you to link parent and child field for just a one to one relationship without using a repeating section. I guess as a work around you could just set your repeating section count to one?

I guess you can get the value of the primary key of the parent with e.datasubmitted and then write the code to save it to the child table in the events.

Is there a proper or easier way to accomplish this? It would make a useful instructional video.

It would be nice if you could bind a control to more than one table in the control's data binding property. Now there's an idea! You could get a long daisy chain going that way.
Tks
Bob

[GGandhi]

Re: Xdialog2 create tables

hello

that was not what i saw in the version of alpha5 i have and if you look at selwyn's answer he also confirms what i saw. if you have different outcome, can you make a video or something to show, so that i can understand how you did it. also let me know your version and build so i could update to that version.