Forums

Hello,

I'm struggling with the next scenario (CF4RC3, J2.5):
Two tables, one form.
1st table holds an id (incremental) + some other columns.
2nd table, holds the same id + another column, which together create a unique index (and some other columns of course).

While submitting the form, the same raw is updated, instead of a new one created, no matter what is the combination of the two columns.

Of course, I also need to load the data from the two tables, but I didn't get to it yet, since I didn't solve the INSERT/UPDATE issue.

The instructions regarding the models (in the load record action)are not clear enough and I fill that I miss the entire concept, right at the first stage of designing the structure of the tables, fields etc. I know that the fields in the form should be set specifically according to the model name, but how everything will work together is far from being understood.

Any change to get a complete explanation for the right approach (and details...)?

Thanks,
Emanuel

Hi Emanuel,

So this is the issue which has been solved here:
http://www.chronoengine.com/forums/index.php?option=com_chronoforums&cont=posts&f=26&t=65990

or there is still something unsolved ???

And thanks for posting the fix there🙂

Regards,
Max

Hi Max,
In the post you mentioned, I only separated part of the issue (updating or creating a record based on multiple column index).
I'm not sure that we can call it "a fix". It is more kind of a manual work around...

But the question in this post is much wider and relates to the concept of dealing with "One-To-"Many" tables relationship,which is quite common I think.

This is the scenario
I have two tables, in the second one there is a foreign key pointing to the first table.

I want to create a form which will be used to:
[list=1]

create a new record (meaning INSERT a raw in the first table, then grab the new indexed id to the second table and of course will populate the rest of the input data in the relevant fields of the two tables.

Load the data from the two tables to the form, based on the id and another field, which in the second table create a unique multiple column index.

Update the two tables (here it is a bit tricky since once we have one record in the first table, we'll always have at least one record in the second table, related to the same id, but then we might update the first table, but INSERT a new record in the second table. Again - one to many.

Once I upload the data from the two tables to the form, I want to use them also out of the input fields (for example - show an image based on a file name from the database).

[/list:o]

So the questions are:
[list=1]

Assuming we'll need to set a model to arrange all the data - what are the entire definitions we should make. I mean, how to name the inputs, how to name the fields in the database.

To upload the data from the DB, how should we do it from two tables. Put the action twice and call to each table from different action? And since I'm calling to a record based on two columns in the second table, should I just define the entire WHERE statement and disregard the 'DB field' and 'Request Param' fields in the DB Record Loader action panel (this is what I do now, but not sure if this is the optimal way)?

Should I write the entire INSERT/UPDATE code manually or is there a way to use the DB Save action?

And if the answer to the previous question is "You should code manually", what security issues I should expect which I should be careful to take care of, assuming that the process won't go through the submitting process you wrote?

[/list:o]

I didn't touch the validation issues here yet. Let's understand the main concept firs...

Max, I'll be happy to write a complete tutorial with screen shots and code examples. But I'm way too far from getting the whole picture yet...

Thanks for replying,
Emanuel