Multi-Criteria Portal Filtering
When first using FileMaker's Filter Portal Records feature it's easy to get excited about how you can limit the set of records to exactly what the user needs to see. You start adding a single filter and the result feels like magic. A simple search filter using a global field will let you extra exactly the records you want. It's great!
However, when start adding more and more filters things start to get complicated really fast. You have to become super comfortable with creating complex calculations with a lot of Boolean logic. If you don't know how to use your AND and OR operators, then it can really seem daunting.
In this video we take a detailed look at how to break down a complex multi-filter approach and make it seem really easy to achieve some super advanced portal filtering. If you've ever been confused about making your portals show exactly what the user needs, especially when using multiple filtering vectors, then enjoy the understanding you'll receive from watching this video.
Comments
Great Example - Great Teaching
On this Thanksgiving we are thankful for your 'teaching'.
Dynamic Button Bar
Could you point me to the video on how you did the dynamic button bar in this video, I can't seem to locate it. Great video thanks.
Find instead
This certainly is a great example of Let() and how to break down complex logic. However, I’d be interested in your decision to not simply use a find script that returns IDs that would drive the displayed records through a relationship gIDList-child.
Choosing the best option for the situation
This type of decision comes down to a few factors. 1) The amount of data being queried and 2) what the type of query it is. If things get more complex than presented, I might choose to go with a regular search. The downside to this are that some times you have to open another window or navigate to another layout to perform the search and get the list of IDs.
It also requires the use of a summary field to get the ListOf id values. However, there's also the possibility of using ExecuteSQL and not even having to have any joins on the graph or a summary field. Plus, the ExecuteSQL function, as far as I'm aware, is simply a wrapper around a conventional FileMaker find.
All three options (portal filter, standard FM query, ExecuteSQL) are viable and depend on the dynamics of the solution and how you are implementing. For smaller data sets I tend to simply opt for the Portal Filter because it's right there and readily available without having to setup a global field for the id values, an extra summary field and scripting the find. You just compose the calc and wire it up to your UI. Simple and straight-forward.
Hope that helps with my thought process. Good question!
-- Matt Petrowsky - ISO FileMaker Magazine Editor
Find instead 2
Hi Matt,
Thanks for the useful insights and the custom function.
I would also be interested on your take how to implement a similar UI in a find environment, with multiple criteria as in this example.
Thanks!
Locally scoped layout variable "trick"
About 24 minutes into the video you refer to the locally scoped layout variable trick, and refer us to search the site. I'be been searching for quite a while and haven't found any thing. Would you give a little better hint where we might find that video? I thought that was impossible and would really like to see how it's done.
Thank you for the great tutorials!
Larry
Search Term: "Local Variable Layout"
https://www.filemakermagazine.com/videos/leveraging-layout-variables
https://www.filemakermagazine.com/videos/hide-calculation-coolness
--
Daniel Farnan
Ignorance is curable, not preventable
Replacing the multiple cases
Hi Matt,
Great video as usual! I had prepared something similar, but this is giving me more ideas. I had another approach for the Case part of the custom function. To clean up the code a bit, especially if there is more and more conditions (and thus a large amount of possibilities), I will skip the filterCount and take this kind of route for the final test :
If ( ~filterSearchON ; ~matchesSearch ; True ) and
If ( ~filterColorON ; ~matchesColors ; True ) and
If ( ~filterFavoriteON ; ~matchesFavorite ; True )
In your opinion, would this always have similar results in efficiency? Thanks!
Curious how you would handle this
Given your solution. I want to associate People to many colors and then in the portal area, if user selects both colors of a given People I want that person to show. Example, If Mark is both Black and Blue and Beth is Blue and Orange. Situation 1: User selects only Blue. Result: Mark and Beth appear. Situation 2: User selects Black and Blue. Result: Mark only appears. How would a developer handle this implementation. Thanks Matt.
Using a global variable as the search string
This is what I am trying to use as a filter:
Position ( List::_list.name ; $$filter ; 1 ; 1 )
It works great if I create a calculated field but as soon as I put it in the portal filter calculation it fails. I can put literal text in place of $$filter and it works.
Is this a place where a global variable just can't be used
Darrel Farah
What am I trying to do might be useful
I would like to be able to put text into a global variable that filters the records in the portal.
Darrel Farah