Portal filter based on categories

[polarpro]

Hi there,

I wonder if somebody can help me with a portal problem.

As background here is the structure of the database

In my database the fields common to all records are stored in a single big “base” table, with relationships to other tables based on “type” of data, that hold the other pieces of information. (Each type requires some specific fields, that’s why.) So, each entry consists of a “base record” that is related to one “type record”. For example, there is a table for the type “Topics”, and a table for the type “Resources”.

Relationships between entries are being established via a join table using the base records’ keys.

As each entry belongs to a specific category, the base table has a Category field. (Examples: category “Medicine”, category “Psychology”).

In order to establish a relationship between a topic and a resource I put a portal on a my layout that shows the names of all the resources that exist via a cartesian join. (For this purpose only, I created a relationship directly from Topics to Resources (using anchor-buoy)). Clicking on the desired resource starts a script that then relates the entries to one another.

So far, everything works fine; there is just one thing: I want the portal to show only those resources that belong to the same category as the topic. For example, when the topic belongs to the category “Medicine”, I don’t want to the portal to show resources from the “Psychology” category.

The only thing I can think of at the moment is to set up a global field in the topics table and a Category field in the type table (besides the Category field that is already in the base table). The portal then shows only those records with Topics::gCategory = Resources::Category.

I’ve tried it, and it works, but I am not too happy with the solution, as I need to establish a new portal relationship for each category. On top, I’d prefer to store the category information in the base table only.

Any ideas? Highly appreciated!

Mike

[comment]

Did you mean something like this?

CommonTypes.fp7.zip

[polarpro]

Thank you for the file. Very helpful!

Last night, while I was racking my brain, I found a similar solution using a self-join.

Even more though, I’ve found a way to establish a relationship that works for all the categories. So, it is not necessary to create a relationship / portal for each category. I use a self-join with the same field: [color:purple]Base::Category = Base2::Category, (maybe similar to the self-join in your file).

It works smoothly, am quite content with it – but to be honest, I don’t understand how the self-join/same-field relationship really works.

If you find the time you can take a look at the attached file. You can add as many categories as you want - really like it :B

PortalTypeCategory.fp7.zip

Edited December 13, 2008 by Guest

[comment]

Your solution is practically the same as mine. The only difference is that you have additional occurrences of the sub-type tables (Topics and Resources) hanging off the self-joined TO of the supertype table. This way you can show a separate portal for each sub-type, where in my demo they are all shown together in the same portal.

BTW, it shouldn't be too difficult to filter the single portal so that it only shows objects that are in the same category, but of DIFFERENT type than the current record.

As for how it works: I'd suggest you look at the RG, put yourself mentally at the starting point (where the portal is), and ask yourself "which records do I see from here". Then follow the lines and repeat the same question at each point, until you are looking at the final TO (the one that populates the portal).

[polarpro]

I agree.

The reason why I have additional occurences of the type tables hanging off the self-joined TO of the supertype table is that I want to display the title of a topic / resource. The title, however, is stored in one of the type tables, not in the Base table. The filtering is not the problem, but a portal based on the self-joined Base table would show only IDs, not titles.

[comment]

The title, however, is stored in one of the type tables, not in the Base table.

Well, you could show both titles in a single portal by overlaying them. But perhaps you should reconsider this arrangement. A sub-type table should have only fields that are specific to the sub-type. If both topics and resources have a title, it should be in the common table.

[polarpro]

In the example file the titles are of the same kind, to make it not more complicated. But in the "real" file the equivalents of the Title fields are different from each other and require different treatment. That's why I decided to put those fields that contain the "headline" of an entry in the "type tables", as I didn't want to have too many empty fields in the base table.

Btw, do you have some more information about self-joined relationships that have the same match field? Though I work a lot with self joins I don't got this here completely, um, isn't [color:blue]Base::Category=Base_2::Category true for all records?

[comment]

um, isn't [color:blue]Base::Category=Base_2::Category true for all records?

No, of course not. Think of relationship as a find with criteria. Only records that meet the criteria are related. So in your example, only records (in the same table) that have the same Category as the current record are related to the current record.

[polarpro]

Ah, I see. I didn't think of having 'Medicine' as my "search criterion". What a nice way of gathering records that have a certain thing in common, no matter what it is; opposed to using a field with a constant value on one side.

Thank you!

Mike