Select Multi Select

Wappler General How To
1

I’m sure I’m missing something simple but I’m stumped.

I have a multi-select field which gets a list of categories from the tbl_categories table. I then have a table tbl_stocks which has a list of stocks and a table tbl_stock_categories which stores the category id’s associated with each stock id.

First of all what I need is to be able to have the multi-select field select the already selected values from the tbl_stock_categories table. Then when I save the form I need those values to update if they’ve been changed.

I suppose I could just put a category_ids field in the tbl_stocks table but if there was a straightforward way to do it with my current database structure I would prefer that.

Thanks.

2

What database are you using?

3

MySQL

4

Is it a multiuser system where each user has their own category selections?

5

At the moment it’s just one list of categories but in future it could be user specific.

6

Store the multi-select values as a string array in the user table and retrieve using an IN clause.

7

I was trying to avoid storing the values as a string array.

8

Just use a standard junction table then. There’s few other reasonable options

9

How do I use that with the multi-select?

10

Use a repeat with the array of categoriy_ids passed in the form value to the server connect, adding each ID to the junction table with the selected stock_id

11

How do I pre-select the items that are already in tbl_stock_categories (junction table) .

image

So for the category select for stock_id 66 categories 1 and 2 should be selected when the form is opened.

Also - I’m updating so just adding those values to the tbl_stock_categories table won’t work. I guess I could just delete any current records and then add the new ones.

12

The multi selects expects an array of the values, returned by your data source in the Selected Values option.

13

Hey @Teodor. I get that, but for some reason I can’t figure out HOW to get that array.

14

It’s returned in a JSON array naturally - so you’ve got the IDs you need form the SELECT query. When posting the values back, you would first clear all the values then add the select_id items back

15

My understanding is that the multi select expects something like
“categories”: [1,2]

but the server connect query returns

“categories”: [
{
“category_id”: “1”
},
{
“category_id”: “2”
}
],

16

If you need a comma separated array or just a single value array you can use the flatten function followed by a split (split if none comma separated)

Screenshot 2022-10-23 at 18.06.51
Screenshot 2022-10-23 at 18.06.51714×1430 25.6 KB

Single value array
get_all_users.flatten('id').split(',')

Comma separated
get_all_users.flatten('id')

1 Like
17

Thanks @Sorry_Duh . That was the answer!