Portal Filtering Options
It’s so easy with FileMaker, to simply add, add and add some more - especially to the Relationship Graph. You get the request to see a different set of data, and you hop right to it by adding a new relationship and table occurrence.
Wait a second! Each time you add something new to your FileMaker solution you’re making an agreement with yourself that you’re willing to maintain an ever growing garden of technological spaghetti. With a bit of planning and know-how, you can easily satisfy a growing number of data requests by making your portals able to filter out whatever you desire to show.
In this video, I walk through the use of a dynamic portal by way of filtering data using the Custom List function. It’s the ideal solution to being able to see whatever data you want to see from a relationship where the data is already being loaded from FileMaker Server. If you’re interested in a FileMaker solution which both performs well and also offers user flexibility, then you’ll find some answers within this video.
Comments
When to use vs ExecteSQL?
Great video Matt, thank you. I probably ought to play around with Customlist before asking this question, but...what is the advantage of CustomList vs ExecuteSQL? If as you say, ExecuteSQL is more appropriate for large record sets, why wouldn't it also be so for smaller sets? Would an ExecuteSQL implementation be more tedious or complex than CustomList?
CustomList vs ExecuteSQL
It's a good question. CustomList predates ExecuteSQL in FileMaker, so in the old days CustomList was the only way to do some stuff. Now that ExecuteSQL exists it may sometimes be used instead of CustomList, the choice depends on personal preference and speed of execution, If one way works for you - fine use it, but if ExecuteSQL runs slow it may be worth trying CustomList. And CustomList can do things ExecuteSQL cannot - it's worth learning for sure. I've often used both together in a single script or even a single LET (). The only other thing to note is that CustomList is a custom function which need to be installed, ExecuteSQL is part of FileMaker.
Attachment won't open
Hi Matt,
I've tried downloading the example file twice, but it won't open for me. ( FMPA 15.0.2.220 on OS X 10.10.5). Can you repost it?
Thanks.
Can´t download the video
When I click in the download link of Full HD doesn't work. Can you take a look at it? I always watch the videos offline.
Very helpful.
Thanks. This technique worked well. I was able to add a global selection field to a layout and control what records were displayed in a portal with a case statement. This save changes to the relationship graph and in this case no SQL required.
thanks - excellent presentation
Very useful information Matt, thanks. The Custom Function you demonstrated was easy to implement and produces the results I've been trying to get for ages.
An alternative way?
I'm kind of a hobby developer, and I'm wondering about the drawbacks (if any) of doing this another way.
In a script:
1. Set a variable with the filter you'd like to apply
2. Go to Related Records in a New Window
3. Do a Constrain Find on the variable from step 1..
4. Have a summary field in this table called summaryIDList that contains a List of IDs (duh).
5. Grab the results in the summaryIDList in a $idList variable.
6. Close the Related Records window
7. Set a global field IDLIST in the original table with $idList
8. Setup the TO and match fields pretty much as you have in this video
In addition, I use an OnRecordLoad trigger to reset the IDLIST field to List ( relatedTable::id ).
I built a crude prototype doing this, and it appears to be working. It takes 5-10 seconds the first time through, but every subsequent time through is lightening fast. There are 150,416 records in my related table, and for any one record in the parent table, there might be up to 11,000 unfiltered match records.
John D Altman
Emory University