Insert records into two distinct tables upon form submission?

App Building
1

Hi Retool Team,

I'm currently trying to build a form which is going to insert records into an external database. I was wondering if it would be possible to create something like the image below.

Once the form is submitted one table would receive the title and URL while in another table I'd insert the list of users. The user could add one or more users by clicking the + button. Is it possible to have a form that handles a dynamic list and then when the form gets submitted have a script that processes the form and insert into separate tables?

image
image1766×858 66.6 KB

2

Welcome to the forums!

This is very doable, but a bit of an advanced technique.

Your basic steps are listed below. You will need to research how to do these, all of the data is available on the docs or the forum, I just don't have the bandwidth for a full tutorial!

  1. Create your form from for your parent table. If you use the form builder It will automatically create your insert query for you.
  2. Create a ListView for your users. You will use an array in a temp state var as the source for you ListView.
  3. Clicking your + button adds another object to the array in the temp state. You could also add a - button to remove a user.
  4. Create a JS query that does something like this:
// Add you parent record
await queryParentInsert.trigger()
// Create an array of users to send to a Bulk Insert query
let users=[]
Listview1.data.forEach(user => {
   users.push({fname: user.txtFirstName, lname: user.txtLastName, email: user.txtEmail})
}
await queryUsersInsert.trigger({additionalScope: {data: users}})
  1. Next you need queryUsersInsert which is a bulk insert query that has {{data}} as its Array of records to insert.
  2. Finally you will want to some cleanup like clearing the form and your Temp state var to get ready for another insert.

This can be modified for adding/removing users to an existing parent record.

3 Likes
3

Thank you for your reply.
With the steps you provided and a bit of research I was able to implement the form the way I wanted.

For the clean up part I ended up simply doing something like this:

userList.setValue([
  {first_name: "", last_name: "", email: ""},
]);
1 Like
4

Great!

I would make one change to your cleanup and just do userList.setValue([]) so it is reset to an empty array rather than an array with one value of an empty object. But I may be wrong on that depending on your actual implementation.

1 Like