Bulk inserting to DB from checkbox group

[Andreasms]

• Goal:

My goal is to insert several rows at once into a table with the data coming from a checkbox group. The data to be submitted are the checkboxes that are selected. Each new row contains three pieces of information which also are the three columns aside from the primary key in the target table.
(1) a business_id from localstorage
(2) a meal_type_id (int not null)
(3) a meal_type_variation_id (int not null)

The target table is called business_x_meal_type_variation

• Steps:

I feel like I've tried everything that I know of and can Google my way to and even ChatGPT haven't been able to do the trick. I have not tried JS yet, because I don't know how to write the code.

This here is the setup.

Basically, for a given restaurant, if it serves either or all of the four meal types I'll check off their respective checkbox revealing a set of possible variations.

Let's say I've checked off the variations for Breakfast as in the image.

This means that the restaurant
(1) Serves breakfast.
(2) Serves gluten-free, vegan and vegetarian breakfast.

I want to insert three rows into my table.
The business_id is the same for each row.
The meal_type_id is the same for each row.
The meal_type_variation_id depends on the variation chosen.

In Retool, selecting those three values also means there are 6 values that are NULL values which the latter column wont accept (to avoid having e.g. breakfast written in there without variation multiple times).

I obtain the business_id using {{fetchLocalStorageBusinessId.data}}. That works perfectly fine.

If I make a query for each of the four types of meals, the meal_type_id can simply be 1, 2, 3, and 4 for each of the four queries.

Getting a query to first read which of the four types (breakfast, brunch, lunch, dinner) are selected and then which variations within each of those would be the ideal scenario.

In that case, I can obtain the meal_type_id using SELECT * FROM menu_meal_type

That table only contains meal_type_id and name as below:
1 breakfast
2 brunch
3 lunch
4 dinner

Final piece of information. For the four meal-type checkboxes, in the beginning, I had them shown using the query above. But then since it's always going to be the same four values and to simplify my attempt at inserting the records, I made them into four separate checkboxes and simply name them using a label.

I hope this covers all necessary information without also being too confusing. Basically, how do I insert records from a checkbox group but only the ones selected while ignoring the rest.

Thank you so much

[MikeCB]

Hi @Andreasms,

There are quite a few different ways to tackle this, but I think some JS will make this possible.

For starters, I'd either create a query or variable/function to hold all of the meal_type_variation_id id's and labels.

You can do this with code by going to the code section, and adding a JS query that looks like this:

An alternative, if you've already got those key:value pairs in your DB would be to create a query that returns them in the same format. Doing this ensures that the variation ID's are the same across meal_types.

Next, create your 4 checkbox groups, you can use the label add-on to give them a header.

Switch the mode to mapped (top of the properties pane along the right side) and select the JS Query or DB Query mentioned above, whatever returns the variations and their values as the data source. In my case, the query was called variations.

Repeat that for each of your meal types, here is an example of it working for Breakfast and Lunch, with 2 variations.

You can right click on one of those checkboxGroups and select view state to see how data is stored when you check / uncheck boxes. Ultimately, you want to bulk insert data. Retool makes this easy if you provide an array of key: value pairs that match your database field names.

Because this solutions uses multiple checkbox groups, we can use a Transformer to convert the data into a format that makes it easy to bulk insert. Here is the code that works for this example:

// Helper function that accepts the checkbox and the meal_type_id
const mapVariations = (values, meal_type_id) => {
  // Iterates over the selected options and adds in the business_id and meal_type_id
  return values.map(v => ({business_id: {{ fetchLocalStorageBusinessId?.data }} ?? 100, meal_type_id, meal_type_variation_id: v}))
}

const breakfast = mapVariations({{ checkboxGroup1.value }}, 1) // Call the helper function and tell it breakfast is ID 1
const lunch = mapVariations({{ checkboxGroup2.value }}, 2) // Repeat for lunch
// ... repeat above lines for other meal_types

return [...breakfast, ...lunch] // add "...mealType" for other meal types to the array

The output of this transformer looks like this:

This is formatted in a way that can now be used directly in your bulk insert query. So create a new query, select GUI Mode, choose your table and "bulk insert records". For the array of records, put it the value from the transformer.

Now, when you want to save the save, just hook up a button or some even to trigger the query and it will add all the data to your table.

[Andreasms]

Hey Mike

I only had time to test it today.

Works perfectly.

Thank you so much.

I spent hours on this before finally deciding I should go to the community and ask for help. It's only the third time I've asked for help in here, but its amazing the kind of help I have gotten each time.

Thank you so much for taking time out to show me how to do this.
I hope my the title of my post will help others to find your answer.

Very much appreciated.