5 Replies Latest reply on Mar 6, 2008 11:53 AM by Günter Schenk

    Many to many displayed in list

    Mick Tomlinson Level 1
      I'm developing a file manager where files have titles, descriptions, and subject categories amongst other things. File -> Subjects is a many to many relationship, linked by a file_subject table.

      I have successfully used the Many to Many Wizard to make the edit/update detail form work - which provides a Nested Repeat Region showing all subjects as checkboxes.

      Now I need to be able to display a list of all files, with one field listing all the many subject categories that a particular file belongs to. Is there a way to do this in ADDT? I can make a list with a nested repeat region showing the link table subjects, but don't know how to translate those to display values from the actual subject table. Does that make sense?

      Any assistance much appreciated.
        • 1. Re: Many to many displayed in list
          Günter Schenk Level 4
          Hi Mick,

          -----
          with one field listing all the many subject categories that a particular file belongs to
          -----

          how do you store all those "subjects" in your comma-separated column, the "subject_id" I reckon ?

          However, you might want to read my little "Query-Tipp: extract 1 value from comma-separated list" tutorial I posted at the Interakt forums:

          http://www.interaktonline.com/Products/Dreamweaver-Extensions/MXWidgets/Product-Forum/Deta ils/54511/Query-Tipp%3A+extract+1+value+from+comma-separated+list.html

          Maybe this is what you´re looking for...

          Cheers,
          Günter Schenk
          Adobe Community Expert, Dreamweaver
          • 2. Re: Many to many displayed in list
            Mick Tomlinson Level 1
            Dear Günter

            Many thanks for your suggestion, although I probably wasnt clear enough first time round I didnt just want to extract 1 item from the comma list, but rather translate the ID into a NAME (from a related table).

            However... I have now managed to get it to do what I wanted by replacing ADDTs nested Detail recordset with a new one with some joins:

            SELECT subject.sub_name FROM file, subject, file_ subject WHERE file_subject.file_id=123456789 AND file.file_id = file_subject.file_id AND file_subject.sub_id = subject.sub_id ORDER BY file_subject.sub_id

            Dont know if its the best way, but it works!
            Thanks again
            Mick
            • 3. Re: Many to many displayed in list
              Günter Schenk Level 4
              Hi Mick,

              ----
              Dont know if its the best way, but it works!
              ----

              if it works, it *is* the best way :-)

              Cheers,
              Günter Schenk
              Adobe Community Expert, Dreamweaver
              • 4. Re: Many to many displayed in list
                Mick Tomlinson Level 1
                Hi Günter

                I managed to paste my various bit of code into a page made with the Dynamic List Wizard, and my comma list works fine (as I wanted) in one of the columns.

                Question is... do you think it might be possible to adapt the page further, so that I could filter or sort results using the nested Detail recordset (listed above)?

                I don't know a huge amount of php/MySQL, but I can see that the list sorter is 'very tied into' the main recordset - hence I can't figure how to incorporate another recordset for sorting.

                Thanks as always for your input!
                Mick
                • 5. Re: Many to many displayed in list
                  Günter Schenk Level 4
                  Hi Mick,

                  -----
                  Question is... do you think it might be possible to adapt the page further, so that I could filter or sort results using the nested Detail recordset (listed above)?
                  -----

                  as you say yourself, the list sorter grabs its stuff from the main recordset, and I don´t see a possibility to embed columns coming from "elsewhere"

                  Cheers,
                  Günter Schenk
                  Adobe Community Expert, Dreamweaver