17 Replies Latest reply on Sep 12, 2008 7:13 AM by (Chris_G_Trim)

    Multiple Select List/Menu

    roborino3 Level 1
      Loosing my mind on this one. So I would like to have a dynamic list/menu where a user could select multiple items and save it to a MySQL database table. I have tried a couple ways and can't seem to get it to work correctly. I realize the name parameter must be an array (name=list_menu[]). However, the update wizard doesn't know what to do with that as it just puts in the one of the selected items. And when I can get it saved to the database correctly, the dynamic list/menu doesn't know how to compare the results it receives with the appropriate row so that it can show it as selected. Any and all help is very much appreciated. It would seem like this would be something very simple to do...however, I seem to be missing it. Thanks!
        • 1. Re: Multiple Select List/Menu
          roborino3 Level 1
          Greetings! Is it possible that this doesn't exist in the Developer Toolbox? Simply put I would like to create a dynamic list/menu and allow for multiple selections. As well, I would like to be able to insert and update those selections to a MySQL database. Seems simple enough, however, I can't seem to find any Server Behavior that can do this. Thanks!
          • 2. Re: Multiple Select List/Menu
            Günter Schenk Level 4
            Hi Rob,

            there´s a Server Behaviour called "Comma-separated Menu" which allows the user to render a comma separated string of values as a multiple select. The value/label pairs of this menu will be retrieved from an additional recordset which queries an auxiliary table that´s holding this data.

            You´ll find this behaviour under: Server Behaviours -> Developer Toolbox -> Form Controls

            BTW, another method for applying a multiple select feature to your form is ADDT´s "Comma-separated Checkboxes" behaviour

            Cheers,
            Günter Schenk
            Adobe Community Expert, Dreamweaver
            • 3. Re: Multiple Select List/Menu
              roborino3 Level 1
              Thank you very much for the response. Couple questions. So is there a way to show which one is selected from another sql select query? Also, noticed the size parameter doesn't work. With four items in the list and setting the size to 2 all four items show up. I would have imagined there would have been a scroll bar. If I use the Comma separated Checkboxes behavior, would I have the same problem? How to a show which items are checked from a select query and how would I use and update query to save the ones that are checked? Thanks!!!
              • 4. Re: Multiple Select List/Menu
                Günter Schenk Level 4
                Hi Rob,

                ------
                How to a show which items are checked from a select query and how would I use and update query to save the ones that are checked?
                ------

                both the Comma-separated Menu and the Comma separated Checkboxes behaviors will store the values (not the labels) of the selected entries as comma-separated list in a, say, varchar column of your "main" table -- the auxiliary query I mentioned is needed to have those behaviours compare the stored values against the existing values/labels of that "other" table, and both behaviours will display the matching values as selected. Hard to explain ;-)

                ------
                Also, noticed the size parameter doesn't work. With four items in the list and setting the size to 2 all four items show up. I would have imagined there would have been a scroll bar
                ------

                Haven´t played much with the Comma-separated Menu behaviour, so I can´t tell what´s up with that.

                ------
                If I use the Comma separated Checkboxes behavior, would I have the same problem?
                ------

                no, because it´s getting rendered as series of checkboxes, and the number you´re deciding on in the "display columns" input field will control some sort of "horizontal looping", means a value of 4 will display 4 checkboxes in each table row.

                If a list of multiple select options isn´t that extensive anyway, I´d personally consider using the Comma separated Checkboxes behaviour, as selecting values from a series of checkboxes is certainly more straightforward than selecting multiple entries from a menu.

                Cheers,
                Günter Schenk
                Adobe Community Expert, Dreamweaver
                • 5. Re: Multiple Select List/Menu
                  roborino3 Level 1
                  Hi Günter

                  -----
                  both the Comma-separated Menu and the Comma separated Checkboxes behaviors will store the values (not the labels) of the selected entries as comma-separated list in a, say, varchar column of your "main" table -- the auxiliary query I mentioned is needed to have those behaviours compare the stored values against the existing values/labels of that "other" table, and both behaviours will display the matching values as selected. Hard to explain
                  -----

                  Ya, not sure I understand what you are saying here. Never saw anything about an auxiliary query. Is it possible you could try to explain again? Either comma separated menu or checkboxs, doesn't matter at this point. This is what I got now:

                  input name="comma" id="comma" wdg:recordset="rs_associations" wdg:subtype="CommaMenu" wdg:type="widget" wdg:displayfield="name" wdg:valuefield="id" wdg:size="2"

                  Not sure what other functions exist to send another query to so when it automagically develops the select statement it selects those where are suppose to be selected.

                  Thanks,
                  Rob
                  • 6. Re: Multiple Select List/Menu
                    Günter Schenk Level 4
                    Hi Rob,

                    -------
                    Ya, not sure I understand what you are saying here
                    -------

                    well, I understand my words pretty fine, but maybe noone else does :-) However, that was just an attempt to summarize that stuff in a few words, but maybe I should rather point you to the help file which provides some more explanation in the chapter "Form Controls" (see "Comma-separated Checkboxes" respectively "Comma-separated Menu")

                    ------
                    Never saw anything about an auxiliary query
                    ------

                    that´s the recordset which retrieves the value/label pairs from the table which holds the data that you want displayed when using the comma-separated menu or comma-separated checkboxes behaviours.

                    Cheers,
                    Günter Schenk
                    Adobe Community Expert, Dreamweaver
                    • 7. Re: Multiple Select List/Menu
                      roborino3 Level 1
                      Hi Günter,

                      Again, thanks for your help. Sorry I am being so dense...just seems like this is a function that would be used all the time and to have it so complicated is just baffling me.

                      -----
                      ... I should rather point you to the help file which provides some more explanation in the chapter "Form Controls" (see "Comma-separated Checkboxes" respectively "Comma-separated Menu")
                      -----

                      So the help files are typically horrible. There is usually no additional information other then explaining what the boxes are on the dialog. With that said, this is what I found. A picture of exactly what I want, however there is nothing that is documented showing how you assign selected or checked items. It states, "The selected recordset should contain the data that you want displayed when using the comma-separated menu." I get that, but how do you tell it which ones should render selected?

                      -----
                      that´s the recordset which retrieves the value/label pairs from the table which holds the data that you want displayed when using the comma-separated menu or comma-separated checkboxes behaviours.
                      -----

                      So I understand there is a query to populate the menu or checkboxes, but how do you tell it which ones should be selected or checked? The only values to put in the menu or check box dialog is label and value.

                      Thanks,
                      Rob
                      • 8. Re: Multiple Select List/Menu
                        Günter Schenk Level 4
                        Hi Rob,

                        let´s assume you want to render a series of checkboxes using the Comma-separated Checkboxes behaviour, and let´s assume this series of checkboxes are to represent a list of "states" -- you´d basically need a separate "states" table with the following columns:

                        - state_id (type: int, autoincrement, primary key)
                        - state_name (type: varchar)

                        this "states" table could hold the following values:

                        1 | Germany
                        2 | USA
                        3 | Turkey
                        4 | Canada

                        -------
                        So I understand there is a query to populate the menu or checkboxes, but how do you tell it which ones should be selected or checked? The only values to put in the menu or check box dialog is label and value
                        -------

                        "value" equals the "state_id" column, and "label" equals the "state_name" column.

                        When using e.g. the Comma-separated Checkboxes behaviour in conjunction with a query (aka: Recordset, let´s name it "states" for now) which retrieves the "state_id" and the "state_name" columns from the "states" table, it´s an array of comma-separated "values" which are inserted into a varchar column of your main table -- this could be an array like "1,3,4"

                        -------
                        how do you tell it which ones should be selected or checked?
                        -------

                        that´s why you need to add this "states" recordset on both an Insert Record and an "Update Record" page, because the Comma-separated Checkboxes behaviour will check the stored array of values ("1,3,4") against the "states" table, and if for instance your main table´s varchar column holds the values "1,3", the Comma-separated Checkboxes behaviour will - on an "Update Record" page - display the matching "Germany" and "Turkey" checkboxes as selected, but the other ones as unselected, because the values "2" and "4" haven´t (yet) been stored in the main table´s varchar column.

                        Cheers,
                        Günter Schenk
                        Adobe Community Expert, Dreamweaver
                        • 9. Re: Multiple Select List/Menu
                          roborino3 Level 1
                          Hi Günter,

                          -----
                          - state_id (type: int, autoincrement, primary key)
                          - state_name (type: varchar)

                          this "states" table could hold the following values:

                          1 | Germany
                          2 | USA
                          3 | Turkey
                          4 | Canada
                          -----

                          I got the part about the States table. Lets call the recordset rs_states. So in my Comma-separated menu I got:

                          input name="state_list" id="state_list" wdg:recordset="rs_states" wdg:subtype="CommaMenu" wdg:type="widget" wdg:displayfield="name" wdg:valuefield="id" wdg:size="3"

                          Where wdg:displayfield=name, for instance Germany and wdg:valuefield=id, which would be 1.

                          -----
                          When using e.g. the Comma-separated Checkboxes behaviour in conjunction with a query (aka: Recordset, let´s name it "states" for now) which retrieves the "state_id" and the "state_name" columns from the "states" table
                          -----

                          I am assuming the Recordset you refer to above is the one I called rs_states. Where I lose you is next...

                          -----
                          it´s an array of comma-separated "values" which are inserted into a varchar column of your main table -- this could be an array like "1,3,4"
                          -----

                          My confusion is that it sounds like you are talking about the states table (rs_states)...I think what you meant to say is that it refers to the main table you mention. So the main table would be something like:

                          the "main" table could hold the following values:

                          id|member_id|state_ids
                          1 | 1|1,3,4
                          2 | 3|2,3
                          3 | 2|3
                          4 | 4|2,4

                          What I don't understand by your last comments is how the comparison occurs between the main table and the states table.

                          -----
                          that´s why you need to add this "states" recordset on both an Insert Record and an "Update Record" page, because the Comma-separated Checkboxes behaviour will check the stored array of values ("1,3,4") against the "states" table
                          -----

                          Since the Comma-separated menu behavior only can take one recordset, in this case rs_states how is it able to make a comparison to lets say rs_main table?

                          Below is the list of activities I would perform, at least the activities I think I should be performing:

                          1. create table states with id and name
                          2. create table main with id, member_id, state_ids
                          3. create recordset rs_states with id and name
                          4. create recordset rs_main with id, member_id, state_ids filtered on member_id
                          5. create input tag for comma_separated menu
                          6. create comma_separated menu and put recordset as rs_states, label as name and values as id.
                          7. create update record for table main which updates on member_id and updates state_ids with state_list

                          What else am I missing?

                          Thanks,
                          Rob
                          • 10. Re: Multiple Select List/Menu
                            Günter Schenk Level 4
                            Hi Rob,

                            your assumptions are all OK, so...

                            ---------
                            Since the Comma-separated menu behavior only can take one recordset, in this case rs_states how is it able to make a comparison to lets say rs_main table?
                            ---------

                            ...rather than trying to provide more information about how it works behind the scenes, it´s now my turn to ask you one thing: does it work ? ;-)

                            -------
                            1. create table states with id and name
                            2. create table main with id, member_id, state_ids
                            3. create recordset rs_states with id and name
                            4. create recordset rs_main with id, member_id, state_ids filtered on member_id
                            5. create input tag for comma_separated menu
                            6. create comma_separated menu and put recordset as rs_states, label as name and values as id.
                            7. create update record for table main which updates on member_id and updates state_ids with state_list
                            -------

                            this sounds good, so what happens if you try it this way ?

                            Cheers,
                            Günter Schenk
                            Adobe Community Expert, Dreamweaver
                            • 11. Re: Multiple Select List/Menu
                              roborino3 Level 1
                              Hi Günter,

                              In a word...Nada!

                              Well actually, it saves the parameters okay...what it doesn't do is when I bring the page back up it doesn't show the ones that where previously saved as selected. I could always, one way or another save the info (meaning preform a sql update to save the info). What I can't do, and what is bothering me the most, is how do I get it to show what has been saved, or previously selected.

                              So if I have the rs_states recordset, the one with all the states I want in the menu/list...how do I use the rs_main, the recordset with the info that was saved via an Insert or Update, to show as the previously selected states when the user pulls the page back up?

                              Thanks!
                              Rob
                              • 12. Re: Multiple Select List/Menu
                                Günter Schenk Level 4
                                Hi Rob,

                                --------
                                Well actually, it saves the parameters okay...what it doesn't do is when I bring the page back up it doesn't show the ones that where previously saved as selected
                                --------

                                so it seems you implemented that behaviour on a standard multiple insert/update/delete records - form, and it´s only when that form switches to "Update" mode, the comma-separated values don´t seem to get picked up correctly ?

                                Generally it does work, however I recall some other folks reporting the same issue in a handful of forum threads, and the solution to this happened to be quite easy -- if I only could remember what it was, as my workflow is different ;-)

                                My suggestions at this point:

                                1. try searching the forums, as the solution is buried somewhere

                                2. please post the page code on your remote host as .txt file and provide a link to that file

                                3. let´s hope someone else who had this very issue before, can jump in and tell you what to do

                                4. if 3. doesn´t happen in time, I´ll try to search for a solution myself

                                Cheers,
                                Günter Schenk
                                Adobe Community Expert, Dreamweaver
                                • 13. Re: Multiple Select List/Menu
                                  roborino3 Level 1
                                  Hi Günter,

                                  -----
                                  so it seems you implemented that behaviour on a standard multiple insert/update/delete records - form, and it´s only when that form switches to "Update" mode, the comma-separated values don´t seem to get picked up correctly ?
                                  -----

                                  Currently, I am not at my development system, so I will post a link the page later.

                                  I guess I am not being clear, my apologies...My question is, pertaining to your comment above is when the user returns to the page and wants to see what they put in previously, which was saved to the database and now should be on the screen so the user can modify it, how does the behavior know what those values are? The behavior only has the recordset of rs_states...it never see rs_main which holds the previously saved values. Therefore, how the page is currently written it will never compare the two recordsets. So how/where do you put the rs_main so that it can perform that comparison and show in the behavior which ones were previously selected?

                                  Thanks,
                                  Rob
                                  • 14. Re: Multiple Select List/Menu
                                    Günter Schenk Level 4
                                    Hi Rob,

                                    -------
                                    I guess I am not being clear, my apologies...My question is, pertaining to your comment above is when the user returns to the page and wants to see what they put in previously, which was saved to the database and now should be on the screen so the user can modify it, how does the behavior know what those values are? The behavior only has the recordset of rs_states...it never see rs_main which holds the previously saved values. Therefore, how the page is currently written it will never compare the two recordsets. So how/where do you put the rs_main so that it can perform that comparison and show in the behavior which ones were previously selected?
                                    -------

                                    that´s what I need to find out by looking at the page code and see if there´s maybe a little error in there which prevents stuff from running as expected.

                                    Cheers,
                                    Günter Schenk
                                    Adobe Community Expert, Dreamweaver
                                    • 15. Re: Multiple Select List/Menu
                                      roborino3 Level 1
                                      Hi Günter,

                                      So after following the steps I outlined earlier I realize I was doing something out of sequence and the value was not being set by the update query. Once I rearranged my steps I got it all working correctly now. Whew!

                                      Thank you so much for being patient with me! You assistance is very very much appreciated.

                                      Rob
                                      • 16. Re: Multiple Select List/Menu
                                        Günter Schenk Level 4
                                        Hi Rob,

                                        -----
                                        Whew!
                                        -----

                                        yeah really - you can´t believe how happy I am to know it works now :-)

                                        Cheers,
                                        Günter Schenk
                                        Adobe Community Expert, Dreamweaver
                                        • 17. Re: Multiple Select List/Menu
                                          Hi Gunter,

                                          ----------------------------------------------------------------
                                          so it seems you implemented that behaviour on a standard multiple insert/update/delete records - form, and it´s only when that form switches to "Update" mode, the comma-separated values don´t seem to get picked up correctly ?
                                          ----------------------------------------------------------------

                                          I'm having the same problem!
                                          I have a standard form which I have added a Comma Separated Checkbox to. The insert works fine, but update doesn't display ticked selections.
                                          I've been scouring the forums all day to find an answer. Does it need custom coding with explode() or am I missing something?

                                          Chris