Add "n" amount of rows in postgress DB from multiselect dropdown

Queries and Resources
1

So suposed I have these postgress tables

User
ID | name | lastname | --> PK:ID

Color
|ID| name_of colour| --> PK ID

Color Dependency
ID| User_id| Colour ID| --> PK:ID Foreing KEY USer ID related to User ID Colour ID ref to colours table

So you could have in Color dependency table something like this:

ID| User_id| Colour ID
1       2      green
2       2      red
3       2      blue

How I cat populate column "Colour ID" with multiple colours that come from a multiselect dropdown in retool ?

multiselect drop down has a GetColours event that triger a select * from colours table and populates it

It has also
Values : {{GetColours.data.id}}
Label: {{GetColours.data.name}}

Not sure here how to do a multiple row insert into a postgress table from this multiselect component. I had read this post and I think is similar to what I want: How do I add multiple rows to table in PostgreSQL? - #2 by bradlymathews
But it´s not quite clear this:

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

what this line comes from? @bradlymathews ?
bundled_sku: tblProducts.selectedRow.data[0].sku

In my case the sql table "Colour dependency" would be populated from a drop down which contains the User Id and name (I´m just using the ID) and from a multiselect drop down with multiple values.

2

If the source of your colors is a multiselect component instead of a table (as in my example) then would do something like this (note that I have improved the code for this so it only requires two queries):

(async () => {
  var bundledItems = [];
  multiselectColors.value.forEach(row => {
    bundledItems.push({user_id: usersDropdown.value, colour: row.id})
  });
  let data2 = await qryAddColours.trigger({additionalScope: {bundledItems}})	
 })();

qryAddColours would have {{bundledItems}} in Array of records to update (or insert) property.

Hope that makes sense?

3

hey @bradlymathews Will test and let you know. What does the (async () function does? didn´t see that in your original.
thank for replying. Also "data2" is defines but not used

4

This pattern for JS queries uses Javascript's Async/Await capabilities so that Retool will run the queries sequentially. And yes, that is an improved syntax vs my original code which from an older post.

Normally, if you run more than query or set a temp var or update a component with .setValue() and then run a query, these actions can happen in parallel. This way of coding forces everything to happen one thing at a time because it is very often important for one query to finish before the next one begins. So I now always use this pattern if my JS has to tell Retool to do more than one thing.

You are correct that data2 is not used. If I wanted to use the .data returned in the query I could call qryAddColours.data afterword, but I find that it is more reliable if .data is passed back from the query itself, so I just have that hardcoded into my patterns doc I cut and paste code from.

5

So basically for my case I should create first JS script:

(async () => {
  var bundledItems = [];
  multiselectColors.value.forEach(row => {
    bundledItems.push({user_id: usersDropdown.value, colour: row.id})
  });
	
 })();

And then just create postgress DB query GUI -> select my table --> Action type "bulk update via primar key"
and then in the Array of records to update I call the JS script? Sorry if I ask basic stuff, but quite new in retool

6

You've almost got it!

You actually call the JS script from wherever you are initiating the update (a button, a Save Changes event handler or wherever.)

The JS script calls the DB query by including this line (assuming your query is named qryAddColours):

let data2 = await qryAddColours.trigger({additionalScope: {bundledItems}})

You set the query up just like you mentioned, but in the Array or record to update you put {{bundledItems}} as that is the array you passed to it from the JS using additionalScope.

Retool will show {{bundledItems}} in red as if it were an error. This is OK and expected as the query editor does not know what the * bleep * {{bundledItems}} is, but the query execution will know when the time comes.