1 Reply Latest reply on Jan 26, 2016 4:55 AM by AlastairMcInnes

    Multiple portals to one table or multiple tables in one portal?

    AlastairMcInnes

      Hello,

       

      I'm developing a new database to replace two older ones. The databases contain details of books including a list of subjects that the book is about. The subjects match the categories on our website.

       

      We have several websites, and several lists of subjects, but now we want to store all the data in a single database. They have asked for two main browsing layouts - one is to show all the subjects that a book is assigned to, regardless of the website, and the other is to show the subjects for each website separately.

       

      I constructed a subjects table which has a Website field as well as the subject name and the website's numeric ID for the subject. In order to make the many-to-many relationship work, there is also a SubjectBookLinkn table.

       

      On the first layout, I have a portal which just shows all the subjects linked to (via the link table) - they are sorted by website code but not filtered. and I added the code and the subject as portal fields. This works fine.

       

      On the second layout, I have 4 portals (one for each site) which are filtered to show only the subjects with the relevant website code. I didn't bother including the website code on these portals since each list is intended to show only the subjects for one site. This almost works, but not quite. The problem is in searching - if I wanted to find all books categorised in, say, Art on Website 1, I go to find mode and put Art into the subject field and then press Search. But the subject field in all four portals is actually the same field - Subjects::SubjectName so they all display "Art" and the search finds any book with Art as a subject for any site.

       

      I tried creating 4 aliases for the subject table and linking the four portals to one - this stops the search term duplicating itself to the other three portals but the search still searches the whole subject table.

       

      Obviously I could just have 4 different lists of subjects, which would be fine, but is there then a way of showing the related records from all 4 on the single portal?

       

      So I'm looking for a way to achieve what the business wants - a portal on one layout showing all subjects regardless of site and 4 portals on another layout showing only subjects relevant to one site.

       

      Is there a way to do this, or fake it if not?

       

      Thanks,

      Alastair

        • 1. Re: Multiple portals to one table or multiple tables in one portal?
          AlastairMcInnes

          Well, just in case anyone else is interested, I figured out a workaround.

           

          I used 4 tables for the 4 sets of website subjects, and 4 link tables to link them to the books. Then it was easy to have 4 portals on one layout, one for each site.

           

          To fake the single list, I added a field to each Subject table called BrowseDisplay, which is a calculation defined as

           

          Website & " " & Category

           

          (where the bit in the quotes is a tab - use Ctrl+Tab in the editor).

           

          Then I defined 4 Value Lists - one for each subject table which are defined to be the BrowseDisplay field from the relevant subject list table, using the "Include only related values" option, setting the starting table to be the Books table.

           

          This gave me four Value Lists containing the related Subjects for each site. I set them to sort alphabetically.

           

          Finally, in the Books table, I defined a calculation field, Subjects, as follows:

           

          Let ( [Site1 = ValueListItems ( Get ( FileName ) ; "Subjects1" ) ;

                   Site2 = ValueListItems ( Get ( FileName ) ; "Subjects2" ) ;

                   Site3 = ValueListItems ( Get ( FileName ) ; "Subjects3" ) ;

                   Site4 = ValueListItems ( Get ( FileName ) ; "Subjects4" ) ] ;

                  If ( ValueCount ( Site1 ) > 0 ; Site1 & "¶" ; "" ) &

                  If ( ValueCount ( Site2 ) > 0 ; Site2 & "¶" ; "" ) &

                  If ( ValueCount ( Site3 ) > 0 ; Site3 & "¶" ; "" ) &

                  If ( ValueCount ( Site4 ) > 0 ; Site4 ; "" ) )

           

          Which creates a single value list based on the non-blank value lists for each site. I arranged Sites 1-4 in alphabetical order to extend the alphabetical ordering of the underlying value lists. The field is set to recalculate as needed.

           

          On the layout, I drew a text field to display the Subjects field, and added a tab at 35 points so that what the user sees is two columns - the first being the website code and the second being the subject name. I added a vertical scroll bar.

           

          It seems a little long-winded but it does the trick.