Dynamically edit supabase db with M2M relationship on Datagrid

[Coder]

Hello,

Currently, I have 3 tables which are “products” table, “needs” table and “need_product” table on Supabase.

The relationship between 3 tables is one “product” can have many “need” items and one “need” can have many “product” items which means, “need_product” table is many to many table for “product” and “need” tables that requires to have “need_id” and “product_id” as columns.

I’m able to dynamically add and delete on “need_product” table by inserting / deleting need_id and product_id by using Data-grid.

Here, my question is how to edit them dynamically.

1. How to dynamically show dropdown options that are not part of default values.
   For example, I only want to see “Instant Marginal Analysis” from the dropdown option instead of
   what is shown on the screen shot below.

2. How to get selected or disSelected need’s id to delete or insert on Supabase when the Save
   button is clicked?
   I don’t know where to find those data to bind properly.

Thank you!

[Broberto]

I don’t understand your first question, but for the second, you gotta make a function i think with copilot, that checks your selected items array and your previous, and points out those who were removed/added. Then based on that with a workflow delete the things that result to be removed

To check if arr2 contains the value of arr1 I use the following function

return fromArr.filter(record1 => !inArr.some(record2 =>  (in_key.length>0 ?  record2[in_key] : record2) === (from_key.length>0  ?  record1[from_key] : record1)));

This should give you an array of the items that are/aren’t selected. You can put it in a formula like this.

And use it like this, or like a nocode formula. The in_key and from_key are there for the case where you have a specific value you want to filter by in each record. Otherwise set them to ""

return {
"add": formulas['9490eafc-bcef-48c1-960c-5726bcfb3300'](variables[/* Input multiselect - currentSelection */ 'cb028598-7cae-496a-bf77-86daddb175a8-currentSelection'],"",variables['caf7049f-66de-4659-87c9-9da93405781b'],""),
"remove": formulas['9490eafc-bcef-48c1-960c-5726bcfb3300'](variables['caf7049f-66de-4659-87c9-9da93405781b'],"",variables[/* Input multiselect - currentSelection */ 'cb028598-7cae-496a-bf77-86daddb175a8-currentSelection'],"")
}

Then I define based on the result, what kind of operation is gonna happen.

And only then you do your CRUD operations.

[Coder]

@Broberto

your selected items array and your previous,

@Broberto Thank you so much!

Now, I got the flow to work on this but wonder where to find the selected items array.

I made the helper function just like you showed me and tried to use the function on Multi-option split.
To pass the right parameters, I guess I need to find the selected items array and the previous.

Where can I find the selected items array?
Seems like “Datagrid-selectedRows” are empty and “Event” on Row Update only has the original values.

(I also added an item on UI to make sure to update values before working on the workflow.
Only have add option because I wanted to focus on add first before covering all the options.)

[Broberto]

You actually need to add your arrays as parameters, in the formulas[...](here), that’s why you’re getting the error.

As for the selected array, what are you using as a select? If you’re using multiselect option from WeWeb, you should be able to see, multiselect values. But I think that you might need to use this guide from Mariano

Are form variables not visible while the form is in a collection item?

No need to retrieve the input with js. You can keep track of the values using variables and workflows. create a variable of type object [image] on your inputs add a workflow triggered on change where you change the variable [image] [image] Now on submit you can access the values: [chrome_kiGf8an4uI]

to get the values in the selected array. Could you tell me more about your logic?

[Coder]

@Broberto Let me show you the grid data first.
Since I’m fetching this data that has many to many relationship, I made a function “transformNeedProducts” that returns Object.values(result);

Then, I used the function to display Grid data on DataGrid.
It’s an array of objects that has an object of product and array of needs objects that has name and id of it.

I’m using “product.id” as a unique id because I’m not sure what else I can use
but I’m assuming that this might cause an issue because it’s not technically an id of the object. I guess it’s one of my question, what would be the right unique id for this case?

I’m using multiselect option but I don’t see multiselect values. Where can i find them? I remember when I use select option for different cases, it was a lot easier to find the changed values but I don’t know about the multiselect option. Do i need to do extra step to get selected arrays for multiselect option?

I also created a variable to follow the guide from Mariano and saw there’s an item I’m supposed to bind to but again, I don’t see it on my screen. Where am I supposed to bind for path and value?

Thank you so much for your help!

[Broberto]

1. Unique id is anything that is guaranteed to be unique and each item has it, for example your uuid, if it’s unique for each item in your transformed array, then it should be all good.
2. You might want to look into the “From this Project” and look if it’s not in some folder. Or check if you really created it. I’m not sure I can help you find it like this