Multiple MySQL Table Update on form submit?

I have a problem I'm sure someone does every day, but I need to know if DataAssist server behavior or maybe another package can do this.

I have a student form that I submit.. the students are in one table, their parents / care givers are in another table. I need to be able to send the student data to the student table, get back the student ID, send the caregiver names to the caregiver table, get back the new caregiverID, and then update the table that assigns that caregiver to student.

I have several students that overlap caregivers. I need to be able to add maybe 6 caregivers to each student on this submit..

So all that happens when I hit submit.. hopefully..

I see that all that is mentioned is multiple record inserts, but not multiple table inserts?

help!

Unfortunately... I bought the Dynamic Suite to try it out..

It works great on single Record Sets.. but I do not see it able to handle multiple table updates on one submit. Unless I'm missing something.. WebAssist? Anyone?

The Wizard is great.. displays/search/update/delete/insert into a single table no problem and does a great job.

How is everyone doing the multi-table updates?

you do this with the Manage Relational table behavior.

the manage relational table behavior will update your linking table that creates the many to many relationship of the student to parent / caregiver


you would need to create the student insert. Then create a record to pull all of the parents that should be listed to select from

using a repeat region, you can display each parent as a checkbox.

The add the manage relational table behavior to update the lining table.

this post has some details for working with the manage relational table behavior:
showpost.php?p=84393&postcount=17

This post has been deleted.

Thank you for the pointer. I did not see the Manage Relational Table server behavior.

I've tried to figure it out to see if it fits for my problem. I'm not sure it does, but maybe you can clarify how it works after I try to explain the problem more clearly.


One Insert form.

Top data entry part is:

Student first/last/middle names
4 other Student fields

Second part is to insert possibly several Parents names, into Parents table
Parents First, Last, Middle

Third part is to assign the student to a class

There is a relational table that connects the Students to Caregivers
There is a relational table that connects students to classes

I fill in that form and hit submit.

I tried to use the MRT behavior on just the first two but I was unable to figure it out using the referenced help post. I also tried to read through the 'Help' pages, my head hurts. :)

So... my options.. make a multi-page form, not what the client wants..
Or collect all the data from the table.. then on submit I drop to a function that takes all the variables and does the INSERTS one at a time.

Second one sounds less complicated.. but I'd really like to use Data Assist if possible.

Thanks again for your help..

Yeah, the way you want it to work, all one form at the same time, will require hand coding to accomplish.

Okay, consider that a feature request for the next version. :)