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?