Filtering a Portal based on Categories
I am stuck with a problem and wonder if there is a solution...
First the easy part:
Each entry in my database consists of two uniquely related records: One record belongs to a table called Base, and the other record belongs to a table based on the type of data. For example, there is a Topics table, or a Resources table.
The Base table has among others a field called Category. For example, there is a category called "Medicine", and one called "Psychology".
In order to establish relationships I put portals on my layouts. For example, an entry that is a topic has a layout with a portal that shows all of the available resources (via cartesian join).
Until this point, everything works actually fine; buttons and scripts establish proper relationships and so on.
However: What I try is to filter the portal in a way that it only shows those resources that belong to the same category as the topic.
As the category is stored in the Base record only, and not in the Resources record, a portal that shows Resources records can not contain any information about the category it belongs to. So, without the category I cannot do any filtering.
I now have two solutions:
#1: Creating a Category field not just in the Base table, but also in the Resources table with Base::gCategoryMedicine = Resources::Category.
It works, but I am not too happy with this, as I don't want to double store the info.
#2: Creating a self join from Base with Base::gCategoryMedicine = Base_2::Category, and relating the new TO to Resources.
It works, too, but I really would prefer to not have the portal table two hops away, as this can make filtering very slow.
Furthermore, both solutions require a new "portal relationship" for each category. One for all of them would be better :)
Thanks for having read all this so far. Any suggestions are welcome :)