Is It Possible To Insert Records Into Multiple Tables?

Wappler General How To
#1

In the Server Actions, can I insert data to multiple tables. If so, here’s a rough example of the steps I have in mind:

All of the following would be displayed on the same page.

Using the Server Actions to setup a personnel database with several tables the hold unique data relative to an individual.

Here’s my sketch with the Action Steps…

Action Step 1. Insert a person’s contact information into the contact table with the contact insert form.

Action Step 2. Query the contactID from the previous insert and use it to hide the previous contact form, show the next address insert form, and use the contactID as a foreign key to link the next table.

Action Step 3. Insert a person’s address to the address table with an insert address form, using the previous contactID as a foreign key. Likewise use this addressID to hide this form and show the next form, etc.

Continue this pattern until all the tables related this person’s record.

Is this possible? Or am I dreaming?

#2

More than possible, I use it all the time, when I have sql tables that have real foreign keys assigned to certain fields, and one record in one table can not exist without the presence of a corresponding entry into a secondary table.
I have done about 7 inserts at once in one of my stranger projects.

EDIT: This was the best tutorial i ever stumbled across regarding foreign keys, just incase you want or need it.

1 Like
#3

Alright! Man, that really is exciting. It was just lining up all those steps and not being sure how to join them. I use foreign keys all the time to help join many tables. I’ve just never used them congruently dependent on the previous action step… if that makes sense.

Is it accomplished with a repeat or just one step after the other until completed? I know we can grab the previous table’s ID and use in the next action step. However, some of my tables have up to 5 IDs from related table IDs. Each being a ‘foreign key’ for differing types of queries or joins in differing reports etc.

For example: These tables share their UIDs with many others.

I have five tables that are linked with nothing but FKs. Here are a couple…

Itinerary Table -> itinerary_id, user_id
Linked to … by itinerary_id to …
Pastor Table -> pastor_id, itinerary_id, user_id
Linked to … by pastor_id to …
Church Table -> church_id, pastor_id, itinerary_id, user_id
etc.

#4

If you have a very brief template to lean some direction, I would appreciate it. When I lined up all the inserts with each calling for the FK_ID of the associated table. When I open the Insert Options and select the table, it loads every column from all the tables. Is that what’s suppose to happen?

Thanks!

#5

For me, I have to admit, I don’t think I have had too many situations where I am calling in more than a single foreign key from the last table, so I generally just have the insert steps stacking.

Database Connection
Security Provider
Security Restrict
Database Insert 1 - Which is inserting to an auto incrementing row in its table
Database Insert 2 - Where to foreign key is assigned the auto incremented ID from the first table

Even in situations where I have had up to 3 Foreign Keys going into a certain table, they have still followed a similar path, here is an example

I have a form on my page with 3 fields, fieldA, fieldB, fieldC inserting into 3 different tables linked by FKs

tableA - idA, fieldA
tableB - idB, fieldB, FK_idA
tableC - idC, fieldC, FK_idA, FK_idB

The idA, idB, idC all get auto generated from an auto increment primary key
Insert Step 1 takes fieldA from the form
Insert Step 2 takes fieldB from the form and FK_idA as the identity of the first insert
Insert Step 3 takes fieldC from the form and FK_idA as the identity of the first insert & FK_idB as the identity of the second insert.

I assume in your scenario the user_id has already been generated long before this insert ever takes place which is why it is a little confusing. Try consider a few ideas to get around that.

You could try get the user_id from the security provider identity if that matches the current logged in user.
OR
You could make sure your form is full of hidden fields that have data already inserted from all the tables so it is easily accessible in all your inserts you need to do. Could also be a single hidden field with an array of data where you just access the parts of the array you need in each insert.
OR
You could have a server action where you have inserts and queries, so after insert 1 there is nothing stopping adding a query filtered by the identity of the first insert which sets a variable that can be used as part of insert 2

I really hope this makes sense.

#6

Yes, it makes sense.

The reason that there are so many IDs – at least to my limited knowledge – is due the query that differing reports needed. I just assumed that it would be a way for me to have many differing ways to combine these tables. It may be an overkill. Ha!

The user_id is from the security provider’s identity from the logged in user. I thinking was that there may be more than one user that will have their own itinerary in the future and this would help keep their data separate from other users.

I do have a mix of all of your options listed, and this list you have provided has helped me to confirm what I have played around with.

What I’m getting into now is the complexity is getting more and more difficult to keep up with. Ha!