Save changes in a checkbox group in a m:n database table relationship

Hello Retool Community,

I'm working on an application on Retool now for quite some time and until today I have circumvented one problem that I just couldn't figure out myself and I hope that you can help me with this!

In short, the application I'm developing is used for matching students and tutors for tutoring classes. Every student and teacher can have several subjects (e.g. Mathematics, Biology, etc.) attributed to them that they either demand or offer. So, this is a m:n relationship that is represented in my database scheme through the tables "students", "demands", and "subjects" (graphic shows only the abstracted scheme for the students relevant to my question):

In my application it is possible to edit all information about existing students and teachers (e.g. name, birthday, city, etc.) as well as the subjects attributed to them. The former is already implemented, but the latter is something I can't wrap my head around. As you can see in the next screenshot this editing is done within a big form that includes several elements, where the subjects are currently represented by a checkbox group that can be seen at the end of the following image (sorry, labels only in German):
[[I can't add two pictures in this post as I'm a new user, so I'll add it in a follow-up comment to this post.]]

Loading the information is no problem, but I need help on how to save changes for this checkbox group to the "demands" table in my database. There are several possible scenarios that can occur:

  • Additional subjects are added
  • Subjects are deleted
  • Additional subjects are added and other subjects are deleted

By the way, the database is a MySQL database.

I hope that someone can help me with this problem and I'm looking forward to your suggestions!

Thanks and best regards,
Timo

Here is the screenshot from my application:

I am thinking you would want to use a bulk update (or bulk insert if a new record) to do this.

Here is the entry from my own Retool Best Practices doc on how I do bulk updates not based on an existing query. Not exactly what you are doing but close enough you should be able to adapt it. You will need to decide where is the best to put this for you, e.g. the jsTrigger you may already have for handling updates/inserts or on your Student update/insert query's onSuccess.

Also check out: Writing to SQL

Creating an Array From Scratch
You may be doing a bulk insert on a table that you do not have a query or table for, a many-many linkage table for instance, and you need to get columns from multiple sources, use this pattern.

You need to create a js query that will build the array which is used by the Bulk query. This example adds records to the line_items table and uses the current invoiceid as the foreign key.

// jsMakeBulkBundleArray
  var newData = [];
  tblLineItems.selectedRow.data.forEach(row => {
    newData.push({
    line_item_id: row.line_item_id,
    bundled_sku: tblProducts.selectedRow.data[0].sku
  })
});
return newData

Now make another js query that will tie it all together.

// jsBulkBundle
  jsMakeBulkBundleArray.trigger({
    onSuccess:function(data) {
      qryBulkBundle.trigger({
        additionalScope: {
          dataArray: data
        }
      })
  }
});

Next up, make the actual sql bulk query. Use {{dataArray}} as the Array of records to update in your SQL query

Finally you need to trigger the jsBulkBundle query from somewhere like a save button.

Hej Bradly,
thanks for your help! I had to make some further changes to your solution, but I finally got it to work through a similiar approach than you took.
Best, Timo

@betternet ,

Glad to hear it! Would you like to share what worked for you for the benefit of the community?

Sure! I actually adapted my original layout in Retool from using checkboxes to using a table. This way it is not possible to change multiple entries at a time, but it still is possible to add a new subject, delete or change an existing one:

I did use the bulk update you proposed at another point of the app where new students can be added to the system and where the operators can select multiple subjects for them. There the advantage is that I don't have to check whether a subject was deleted (as in my original use case) as just new subjects can be added.
There I used a slightly different version in a JS to craft my array that I then passed on to my bulk insert:

var newSubjects = [];
for (var i = 0; i < re_subjects.value.length; i++) {
    newSubjects.push({student: addStudent.data.result['0'], subject: re_subjects.value[i]});
}
return newSubjects;