I had the opportunity to play with filtered portals yesterday! (I split my time between Salesforce and FileMaker, and it’s been a while since I’ve had a project where I get to play with these. Please pardon my enthusiasm, but this is FUN.)
I needed to set up a dynamic filter using a global field, and decided to try incorporating it into the portal filter definition rather than proceed as I normally would and incorporate the global into my relationship.
I’m pleased as punch with the result. I now have a tiny script called via trigger, and can bop-bop-bop around the solution and apply these dynamic filters to all of the portals on all of the dashboards the user has requested without touching my relationship graph.
Let’s look!
Here’s a simple shot of what I want:
The user can type anything they want into the Filter field, and the system will match on any part of any word on any field in the results in the portal. (Instantly and without effort, of course.)
Idea 1: stick the filter logic into the portal definition. Easy enough! Here’s what that looks like:
…and the logic, allowing for the filter to be empty as well:
SES__Session::Filter_Deals_g = ""
or
PatternCount ( SES_FND__Fund::Nickname ; SES__Session::Filter_Deals_g ) > 0
or
PatternCount ( SES_FND__Fund::Status ; SES__Session::Filter_Deals_g ) > 0
or
PatternCount ( SES_FND__Fund::Rating ; SES__Session::Filter_Deals_g ) > 0But it wasn’t instant. Or frankly responsive in any way.
A quick Google and the FileMaker community reminded me that I need to flush the cache. (Thanks, fmforums.com!) (And perhaps I shouldn’t code in the middle of the night.)
On my way to that answer, I came across another great tip from a commenter on filemakerinspirations.com: using Set Field to set a field within the portal to itself — rather than Refresh Window (flush joined cache results) — will reduce screen-flash for our PC-using friends.
I have to confess that PC users are more of an afterthought for me because I’m evidently a self-centered, self-indulgent Mac user. So I’m pleased when I can incorporate something for them pre-emptively rather than reactively.
So, the simple script, in English:
- Commit Records (so the changes the user makes to the global are recorded)
- Set a field in the portal to itself (shortcut to essentially refresh the cache)
- Go back to the filter field so the user doesn’t notice the system doing anything
Because I’m going to be applying this all over the place, though, I needed to abstract it a bit. With the help of a nothing-fancy custom function to parse my parameters (which you can check out in the attached sample file), here’s the final script:
Here’s the sample file if you want to see it in action: FilteredPortals.fmp12
I admit that setting the $param variable is unnecessary, but I get itchy if I type “Get (scriptParameter)” more than once, so setting that variable is a little reflexive at this point.
And yes, the script comments are longer than the script itself. That’s how simple it is!
Other keys: if you abstract the script like I did, remember to give an object name to the global filter so you can pass it into the script and navigate back to it after refreshing the portal.
Overall Benefits
- Will filter based on any part of the word (or phone number)
- Super-simple to implement across the solution
Overall Risks
I’ll need to see what the performance is like. It’s acceptable for me now over a remote connection a couple states away, but our nascent file has all of 10 records in it. Filemakerinspirations.com has some tips on (legal) performance enhancement, so I may be testing those out next. (I like the idea of using an OnTimer script to delay the “instant” calculation until fast typers have paused for .3 seconds.)
Nice demo!
I see you use hardwired field names in your script parameters.
Example:
“taskFilter|SES_TSK__Task::Status”
This can break if you rename fields. But the getFieldName function can help protect from that:
“taskFilter|” & getFieldName(SES_TSK__Task::Status)
Danke schoen, Bruce!
Nicely done Sara !
Thanks for sharing.
Really good demo Sara – simple to follow too.
Thank you 🙂
Very nice technique, Sara! Thanks for sharing this – it saved me a bunch of time!
Sara, it is a very nice demo and technique.
However, I made some test in one of my applications and the Refresh Windows is little faster.
Because in FM12 is quite difficult to measure the timing when there are complex screen to display, I used a record technique.
Using Camtasia, I record the sequence from the click and the displayed result, with both refresh window-clearing cache and save record.
Then, I calculated the frames between the two: in my application, refresh windows is about 30% faster then the saving record (both little less than a second).
I like your methods, Gianandrea. 🙂 If you didn’t have PC users to account for, Refresh Window would be fine — but even better, we have an actual Refresh Portal step now. Should be even faster, I would think!
Thanks Sara, I’ve been tinkering with a similar thing and trying to optimize it. The changing a record in the portal instead of refresh is a cool tip I haven’t heard of, but it doesn’t seem to fix my issues. 🙁
Also you can make your search handle multiple words (i.e. search for “lin Kn” to find Linda Knowles by doing something like this:
PatternCount ( KB Search::Combined for Search ; LeftWords ( KB Search::Search.g ; 1 ) );1;0)
or
PatternCount ( KB Search::Combined for Search ; LeftWords ( KB Search::Search.g ;2 ) );1;0)
Good tip, Jason, thanks!
When searching the “My Tasks” portal using the date it doesn’t work. In other words the second I put a 3 into the filter field all records disappear. I need filter by date to work
Hi Seth; perhaps you’ve found a way here already! I’d play with the “get as text” function to de-date-ify the date fields, so you can compare them to the text entered in the global search.
Great script. Thanks!
I added these two lines so that if someone edits the middle of the filter field the cursor location doesn’t get bumped to the end:
in the opening Variable settings:
Set Variable [$sel; Value:Get ( ActiveSelectionStart )]
and at the end of the script:
Set Selection [Start Position: $sel; End Position: $sel-1]
Good addition!
I also added two lines which set $$Filtered_Portal_Count to the current count as the portal is filtered.
Go to Portal Row [Last] // washed away by later Go to Object [Object Name: $filter]
Set Variable [$$Filtered_Portal_Count; Value:Get ( ActivePortalRowNumber )]
I improved the appearance on my iPhone by putting a “Freeze Window” before the “go to last portal row” to get a count of the filtered portal.
A remaining mystery is why the first character is ignored after I load this layout; the portal filtering only seems to update after the second call to this script. I can certainly live with this But I’d be interested in any clues to this behavior.
I “solved” the “ignored first character after layout load” by a script trigger “on layout load” which goes to the search field and calls the script trigger as if the search field was modified. But I’m still puzzled at why the first character after layout load is otherwise ignored in your sample file and my adaptation.
But that’s a very minor detail. This functionality as awesome!
Years later… I’m glad you like it! 🙂
The inaction on the first character is odd to me, too. The Commit step doesn’t seem to take action on that first character within the script. If I force a commit by clicking out of the field, a single-character filter works fine.
I’m still not getting this to work for me. However, I think I found one way to generalise your script even more by setting the variable $filter to Get( ActiveLayoutObjectName ). That way you can do away with the filter object name in the parameters.
Fredrik, sorry it didn’t fit the bill! I’ll try to catch your comment sooner next time so we can troubleshoot it.
I realize this is probably a dead thread by now, but I figured I’d take a chance, and ask a question regardless.
First off, I would like to say thank you to Sara Severson for sharing this script with the world free of charge for others to look at, modify, and incorporate into their own FileMaker projects.
I have already implemented this wonderful solution into a project that I have created that takes a CSV file full of battery data from my phone (%, temperature, mA/mV charge/drain, etc.), and it correctly filters a portal that displays said data.
However in another similar project I have another file that has another CSV that contains data that is concatenated in large blocks so I have to separate the data with LEFT, MIDDLE, RIGHT, FILTER, etc. in Calculated Fields in order to display the data correctly in the portal for my secondary project.
Now comes my question…
How can I get this script to search a portal that displays data from a calculated field?
When I attempt to do exactly what is already working in the first project, I get the following error when performing my search.
“This action cannot be performed because this field is not modifiable.”
Any assistance would be greatly appreciated.
Once again, thank you for your time.
Chip, I’m guessing you found a solution in the two years since this comment, but in case others have the same issue: This is likely failing on the set-a-portal-field-to-itself-to-force-a-portal-refresh step (can’t set a calculated field), which you should be able to sub out for the newish “Refresh Portal” script step.
I hope you found a good solution already!
Thank you for that info! The script you built plus the Install OnTimer Script at .3 was perfect for my solution!
Fabulous!
I wonder if there’s an improved way of doing this using FM14’s ‘refresh portal’ script step?
ABOSLUTELY YES. Yep yep yep. See the issue a few people reported further down in the thread; this would avoid that, I believe.
Thank you very much for this article! As so often, Soliant is a great resource.
Just move the “Commit Records/Request” script step BELOW the “Set Field By Name” and you are ready to fly.
You’re welcome! The whole FM community is a pretty great place to be, as evidenced by you all on here. 🙂
Thank you for this solution!
I try to improve it a bit, like jason mark explained, but didn’t manage to make it work… And I was wandering: the “0 ; 1 ) ” is referring to what function ? (in his solution here:
PatternCount ( KB Search::Combined for Search ; LeftWords ( KB Search::Search.g ; 1 ) );1;0)
or
PatternCount ( KB Search::Combined for Search ; LeftWords ( KB Search::Search.g ;2 ) );1;0)
)
Once again, thank you!
It is almost working for me, but when two users at the same time are using the search, they get a error record in use. How to solve this ?
Found the solution. Switch the steps that refresh the portal, then it works in network situations when to clients search together.
Clan you please help ne to solve it on a server ? What do I have to do ?
Can you show me the right script steps ?
PATRICK I had same problem when both user search it says ” i cant modify the record ” can you please share how you fix your problem –
i found the problem. you have to change the access rights in your file. when you upload the file in the database folder it will change the rights to read only. so you are not able to make any modifications. change it and it works.
Hi Patrick! (And Oliver V. and fahran) I can’t reproduce your error on this side, but here are some thoughts:
1. The search field itself is global, so users shouldn’t have a conflict there.
2. The only place I can see potential record-locking is in setting the portal field to itself, to force a portal refresh…
3. …and as Salman Javaheri pointed out, we now have a “Refresh Portal” script step.
So I say, ditch the whole “set field” business and just refresh the portal. That should avoid any monkeying with record ownership.
And thanks for supporting each other on this forum! Sorry it took me a while to hop back on here myself.
Great Example !!! Can use it so much in my solutions. Thanks !!!
Great, thanks for sharing!
You’re very welcome!
Thanks a lot !! I use this technique a lot in my solution and now i put my solution on a filemaker server 14 and i got a error message that this action is not possible. Is this technique works on server ?
Hi macworker! I tried to reproduce the error, but when I access this from FMS 14 it’s working fine. Can you tell me more about what’s happening right before you get the “action not possible” message? (And I’m glad the trick was helpful for you, generally!)
Hi Sara – thank you so much for making this code public domain. Would you be able to help me with something? No matter what I try – I cannot get the loop-back to the global field to work. I’ve stripped down your example, recreated mine several times from scratch – but the loop still breaks after one character entry. Did you modify your example in another way?
Sorted – I hadn’t included the ParseParameters as a custom function. Thank you!
Thanks for the update Sara! It seems to improve performance too. I’ve simplified it so that I can use the same pair of scripts for multiple filter fields (I have a lot in my layouts):
1. Install the Before CF – there might be a native alternative
2. Give the portal a name
3. Give the global filter field object the same name, but add “_filter” at the end
4. The Timer trigger script runs the main Trigger script with the parameter: Get(ActiveLayoutObjectName)
5. The main Trigger script does the following:
Commit
Refresh portal: Before ( Get(ScriptParameter) ; “_filter” ; 1 ; “” )
Go to object: Get (ScriptParameter)
Install OnTimer script
Any idea why this now only works for users with the [Full Access] privilege?
Even if I use a new privilege set that has access to everything, it still doesn’t work!
Beautiful idea, thank you so much!
Old post but thanks for this Sarah and others. Works a treat
Thanks! I try to create new project like this in my file but I can’t coding this “ParseParameters ( $param ; 1 )” Why please help!
Hi Huon! That step is using a custom function called “ParseParameters,” so you’ll need to add that custom function to your solution. In case you haven’t done it before: In my sample file, go to File -> Manage -> Custom Functions, and look at the ParseParameters custom function. Copy that from my file into yours, and you should be golden!
An elegant solution, thx Sara!
Glad it was helpful. 🙂
Absolutely. Everyone is asleep by now and in the holy silence I am trying to understand what the callback script exactly does. (Do I understand correctly that any field could be ‘set by name’ within the filtered portal to empty the cache, so your choice of ‘SES_CON___Contact::Title’ was arbitrary?
(( Sorry for the beginner’s question, first dated Filemaker a month ago. :-))
Gergely: a better option is available now in the “Refresh Portal” script step. No need to trick it into a refresh by setting a field to itself. Best wishes to you and FileMaker. 😉
Hi Sara, great work and thanks for posting.
Having implemented this in an employee database i am having issues when 2 or more people search at the same time, whether on FileMaker Pro, WebDirect of FileMaker Go.
If the search field is selected, no one else can do any searching.
Any ideas if there is a workaround ?
I have the same problem. I have add refrest portal but nothing happen.
Hi Carlos and ChalkyDave,
Have you confirmed that you’re using a global field for the search field? Sounds like a record locking issue, which shouldn’t be the case with globals.
Yes, its a global field. I used your example solution in my filemaker server an I have the same problem.
this was very helpful for me today, thank you sara!
Thanks for posting this Sara it will definitely be useful for smaller filtered data sets. However, when I compared this against an existing “Select Student” filter that used a relationship between the Filter global and an exploded key CF on the Student Name side (and 45,000 records/names), the Filtered Portal was much, much slower taking several seconds for each update of the portal. And that’s in a non-networked situation. On the other hand, the exploded key, created using the GetReduxList or other similar CF, is almost instantaneous.
William, thanks for the note! Yeah, I had doubts about the efficiency of this against a large-ish data set. Glad you have another approach that works well for you.
Filtering a portal will act against all of the records in the relationship, and needs you to know those records at the client level so you (the client) can calculate the filter criteria. Add to that the cache flushing we’re doing (so you have to re-download the data set from the server each time), and it’s pretty inefficient.
Pingback: Skiing the Great Wall of China (literally), Filtered Portals, and more... - FileMakerProGurus
Hi I’m working with your solution. The main problem is when I set the on timer script. it works great but when i delete the content of the global search field , The script enter in a infinite loop .
Hi there,
Have you also added a blank Install OnTimer, to cancel out the original request so it doesn’t keep running? It sounds like wherever you’ve set the cancelling OnTimer, it may not be triggered when the global field is blank. If you have a calling script that calls the filterPortals script on a timer, then the first script step in filterPortals should be a blank OnTimer.
Hi Sara, very cool filter. I am trying to get this done, but actually have never used a custom function. Any instructions on that so I can get the parseparameter function in there?
Also, I am trying to filter an image catalog by file name, and will include pixel size later. Will the parse function filter with in a filename? ie: filter using “X3” and get a result that contains “x3″…EZ-Scape-Pro-X3_Flat_Full2.png or X3Pro_inaction_297px.jpg for example.
Hi Matt, welcome! Adding a custom function to your app is a little manual; you’ll essentially recreate it in your own file, under Manage -> Custom Functions. You’ll recreate the name and parameters, then paste the contents of the function into the calculation window. Geist Interactive has a blog post with more details on custom functions, to fill in the gaps: https://www.geistinteractive.com/2018/05/22/like-a-boss-filemaker-custom-functions/. Have fun!
Regarding the future filter for X3: the filtering in this demo is unrelated to the custom function — I just used the custom function to pass information to the filtering script. The filter is triggered by the script, but the magic happens in the portal setup itself. Short answer: the filtering there is not picky! At least in my tests, it’ll filter down to a couple of characters in the middle of a string, regardless of whether those characters are their own word. I think your “X3” test will pass.
This worked a treat. Thank you very much !
One issue is an update to the any modification timestamp in first record in the portal being filtered, is there a way to avoid this?
Hi Andrew,
Check the comments above re: using the new-ish “Refresh Portal” script step instead of setting a field to itself. Refreshing the portal gives us the refresh we need without having to “change” any records, so your timestamps should remain untouched!
FMPro 18 and this is STILL the best solution. I can’t thank you enough.
Thanks to FileMaker’s JavaScript integration you can use the webviewer instead of a global field. This is useful if the same layout is used across multiple windows.
Because of the way portal filtering behaves, the filtering is not window independent if both are on the same parent record, so I haven’t bothered referring to window iterations.
See https://community.claris.com/en/s/question/0D53w00005OE7v5CAD/refresh-portal-refreshes-all-windows-portals-that-share-the-same-parent-record
Basic code below, you just need to define the portal name in the portal filter and web viewer source:
Webviewer source:
BODY TAG GOES HERE (seems to be blocked in this comments section)
CHEVRON GOES HERE
input type=”text” onkeyup=”filter()” placeholder=”Search Jobs” id=”search”
CHEVRON GOES HERE
SCRIPT TAG GOES HERE
function filter(){
var params = {
job_portal: {
text: document.getElementById(‘search’).value
} };
var encodedParams = JSON.stringify(params);
FileMaker.PerformScriptWithOption(“filter_portal”,encodedParams,1)
; }
CLOSE SCRIPT TAG GOES HERE
CLOSE BODY TAG GOES HERE
Portal filter:
Let ( filter_value = JSONGetElement( $$portalfilters ; Get ( LayoutName ) & “.” & Get ( RecordID) & “.job_portal.text” );
IsEmpty ( filter_value ) or PatternCount ( jobs::description & ¶ & jobs::type ; filter_value)
)
filter_portal script:
Set Variable [ $sp; Value:Get (ScriptParameter) ]
Set Variable [ $portal_name; Value:JSONListKeys ( $sp ; “” ) ]
Set Variable [ $portal_json; Value:JSONGetElement ( $sp ; $portal_name ) ]
Set Variable [ $$portalfilters; Value:JSONSetElement ( $$portalfilters ; [ $layout & “.” & $record_id & “.” & $portal_name ; $portal_json ; JSONObject ] ) ]
Refresh Portal [ Object Name: $portal_name ]
I’ve noticed that when using FMGo to type into the filter field, the iPad keyboard window jumps up and down as you type. It allows you to type your words but the jumping up and down is annoying. Love this solution regardless.