Portal Level Filtering

[bdam]

One of the many things I would love to see in FM is the ability to sort a portal right at the portal level, not via monkeying with my relationships. As it stands, filtering portals in 1-M or M-M relationships involves adding fields and/or calculations. This simply ends up in larger databases with more calculating to do (ie: bloat). Maybe I'm just being a purist here, but I like to relate my tables and then have the freedom to easily format and filter the results as I please as in any SQL based RDBMS.

There are tons of posts on here related to portal filtering so it is certainly a highly demanded functionality. However the process is usually so complicated that it takes several explanations and examples to fully show the process.

Wouldn't it be nice if there was just a highly visible and easy to use filter built into the portal like the sort is? Allowing it to be based on a field and/or script variable would make it one powerful piece of functionality.

Bryan

[Søren Dyhr]

If you, in a tabbed layout make each occurance of the portal have it's own sort order, won't it require extra fields neither does it course this to happen:

This simply ends up in larger databases with more calculating to do

For each pair of Inc/Dec set of tabs, would the filesize only grow a "whopping" 4K! I tested it with a portal of 4096 portalrows sans scrollbar to prevent slowish rendering.

--sd

[bdam]

I'm not talking about sorting portals here; I'm talking about filtering them. I realize that you can do a lot of different things to get filtering to work but the ability to filter right on the individual portal instance itself seems like it would cut through a lot of the hassle and clutter. Instead of having a host of relationships for every which way you want to filter you can use the same relationship and merely filter them in the layout itself.

But now that you mention sorting, (better) portal sorting seems like kind of a great idea too. Currently portals are limited to be spreadsheet-esque. So just like (almost) all other spreadsheet implementations it would be nice to have an optional header row you could use to sort the portal. You know, click header once and it sorts that field ascending, click it again and it sorts descending. While I agree this could be done with a multitude of tabs; everyone in my company would complain about having to choose between a whole set of tabs just to sort their data.

Bryan

[Søren Dyhr]

Instead of having a host of relationships for every which way you want to filter you can use the same relationship and merely filter them in the layout itself.

Well, an awful lot is accomplished by doing it this way:

http://fmforums.com/forum/attachment.php?attid/5706/

The very topic was up (...well you might know?) at Devcon this summer; Moyer learned us to avoid too many indexes turned on in a largescale solution and instead seek shelter under a single indexed cartesian product-key for searches.

The technique is the same as this thread struggles with, just as relational keys instead, pretty much like Comment's template:

http://fmforums.com/forum/showtopic.php?tid/83405/

...but today would I probably use this in an extended version covering all values needed, instead:

http://www.briandunning.com/cf/277

--sd

[bdam]

My whole point here is not that port filtering is impossible in many-to-many relationships. My point is that doing so is a total pain.

What if I want to filter my portal using a non-key field? I have plenty of situations where that would be the case. I work in the publishing industry so a good example is just a book. It has a nice primary key already, the unique ISBN that each book is assigned. Well a book has a huge list of attributes; page count, binding, size, type of paper, revision, copyright date, and so on. Using current methods I would need to wrap all of these up in a primary key in order to filter by them.

Wrapping everything up into a primary key and then extending the key to include every possible permutation of the combined fields is what I would define as a huge pain. You're adding calculations and duplicating data only because that's the only way to do it, not because it's the best.

Now, if FM allowed filtering directly at the portal. You no longer need to add every field you wish to use for filtering in a primary key. You also don't need to concern yourself with every possible way your users wish to filter while you design your database structure. Fewer calculations, cleaner primary keys, and a great deal of flexibility.

The 'how' they did this could go a bunch of different ways. They could add some sort of 'filter by' in the same way they do the 'sort by' now in portals. They could also allow sub-finds in the portal that would allow you to refine your search in the parent table.

Bryan

Edited December 29, 2005 by Guest

[Søren Dyhr]

Well it seems like you stem from a SQL'ish background, so you you're no stranger to making requests either ...only that the presentations layer in your view lacks a little to be desired.

Perhaps is it so that you have little or no use at all for the layouts made for you by default when creating the many2many relation. What I think of is that you by a GTRR(SO) makes a found set by which you then can constrain the found set further.

Even with SQL environments aren't the fried pigons flying straight into your mouth, you have to make some kind of action. This is where presentation layer comes in and present your data in another way than the initial relation dictates.

I think you problem is that you cheer excitement to be served, have reversed the order of dealing with the matters - could the reason be that you refuses to use "Anchor Buoy / Hierarchical Table Occurrence Grouping (HTOG) Method" ?

http://www.filemaker.com/downloads/pdf/FMDev_ConvNov05.pdf

(look at p.37) ...well I know the pro and the cons (i can read myself) but explain why it isn't working in your case?

Back to you wish - I'm here thinking of portalized finds, which pre fm8 have been a little iffy, since saving the clipboards contents not always goes well ...and knowing that tampering with the users stuff in the clipboard, is a developer offence of the graver kind.

Take a look at how matters are dealt with in this template:

http://www.nightwing.com.au/FileMaker/demos8/demo804.html

So instead of the dialog Ray uses, could you establish found sets from the many2many relation via GTRR(SO) (remember no need to stop in join-table, any more) From there constrain the found set to your likings and procede to presentation layer portal to show the filtered found set via the gathered ID's the CF makes.

There might as well be some points in variations over this technique, when making you solution user centric:

http://www.filemakermagazine.com/modules.php?op=modload&name=News&file=article&sid=544

While giving it the whirl to be a portalized search, should pretty straight forward after getting the gist of Rays CF...

--sd

[bdam]

First, I would like to thank Soren. He has pointed out some excellent resources here that have helped me a great deal to understand the different ways we can filter portal results. In particular, the NightWing example is most interesting (to me). It allows the user to search non-key fields but maintains the relationship based on the primary key. To me that is a most important step.

That being said, I invite everyone reading this thread to look at these examples and ask yourself: "Do I want to build, extend, and maintain this thing?" Would you want to come into a large company’s database and try and work on it? You have custom functions, multiple table occurrences, globals, calculations, multi-keys, complex scripts all to do what boils down to a simple SQL 'Where' statement.

My want and wish in this thread has never centered on what is or is not possible in FileMaker. It is all about how easily the portal functionality can be implemented, extended, and maintained. My argument is that the current functionality makes doing portal filtering in a many to many relationship more difficult than it needs to be. If we had functionality that would allow portal filtering right at the portal we wouldn't need any of that stuff.

Imagine if you will, a world where you simply related two tables based on their primary key. Just one occurrence of each table related together directly or via a join table. Filtering the portals would be a trivial step in the layout or via a script step that would hide/omit records that you do not want. What if you need to add a field or change what fields to filter? Easy, just change it right in the view or script step. Need several different layouts using the same relationships but wishing to filter the portal differently? Not a problem, just do it right there in the portal’s implementation independent of all other implementations.

Again, I’m not saying that filtering many to many portals in FileMaker is impossible. What I am saying is that this is a really important functionality that I believe should be easier to build, extend, and thus maintain. If Soren or anyone else compares the current functionality (via the examples) with my suggestion and prefers the current setup please let me know why. If you question what I am suggesting, feel free to demand more explanation.

Bryan