Many-To-Many Portal Filtering

[bdam]

I have been looking around these forums for more than a week seeking all the info I can about portal filtering. There are some pretty ingenious work arounds for 1 to 1 and 1 to many portals but I am having difficulties moving these ideas to many to many. Let's use the classic Class and Student example. A class has many students and a student many classes. The first included example shows these two tables with a join table between them. Now, in the class layout it makes sense to be able to filter the portal to allow a teacher to see which students are present, sick, or excused. However I cannot get it setup correctly to do that. The last example shows the best attempt but as you will see FileMaker takes issue with relating tables via a calculation field (ie: unindexable).

Any ideas?

Thanks all,

Bryan Dam

Aside: FileMaker could really use the ability to filter/search portals at the portal level, not via the relationships. This would eliminate so many odd calculations and unnecessary fields.

M2M_Portal_Filtering.zip

[Ender]

Yes, the match field for a relationship must be indexed. Calcs that depend on related values cannot be indexed, as in your example.

There are a few ways to work around this, depending on the application:

1. Define regular stored fields in the intermediate table with auto-enter calcs or lookups to pull the value from the parent table. These copies can be indexed and therefore used as match fields. The limitation is that they don't update automatically when the parent values change.

2. Rework the relationship so that the parent table is involved in filtering the relationship to the target table. This doesn't appear to be possible in your example.

3. Move the global filter field to the intermediate table and use the parent table as the target for the result portal. This seems to work well for the example you gave (See attached.)

M2M_Portal__Filtered_.fp7.zip

[bdam]

Ahh, thanks Ender.

I had already tried your first suggestion (lookup fields) and was able to make it work. However, if I change a student's status I need it to reflect immediately. In most cases this is the way I would expect the relationship to work so lookups were out.

That lead me into trying to find a way to bring the child key into the join table. When that failed I didn't think to bring in the parent key instead. Since the parent key is the global that is the best candidate in the first place.

Now, a more general question then about globally stored fields. What happens when you have several people using this database at the same time? If FM locks the global then no one else would be able to change the filter until it is unlocked by the first user. If FM doesn't lock the global then will the filter be changed on-they-fly and suddenly change for all users?

I am using the Class/Student and an example only. I am in the midst of redesigning a old set of FileMaker databases dating to v3 times. I'm trying to find the best ways to do many to many relationships since there's a great deal of opportunity to leverage them.

Thanks Again,

Bryan

[Ender]

Now, a more general question then about globally stored fields. What happens when you have several people using this database at the same time?

Each workstation has its own instances of global fields. Changes on one workstation do not affect the others.

[bdam]

Perfect.

Thanks again Ender.