FileMaker – Filtered Portals and a real-world Case Study- Part 1

Background When you develop in a platform for long period of time, in this case FileMaker, it is easy to get entrenched in your own ways of executing various tasks and UI tricks without looking at how the platform has evolved to simplify and improve various tasks. For me, one of those areas has been portal filtering, originally introduced in version 11, this is a feature I had not fully embraced or used. For me “filtered” portals meant filtering the results of a parent-child relationship at the relationship level. This usually meant adding various “extra” calc fields to my schema, using various multi-predicate relationships, and generally adding more table occurrences to the graph just so that the user could have different views of the data in a portal on layout. With my latest project, which was for an executive search firm, I had the opportunity to build a larger system in FM 15 from the ground up and really wanted to focus on a leaner schema and graph. My relational graphs have evolved over time to be a bit of hybrid anchor-buoy not too dissimilar than what Kevin Frank proposed in his Life After Anchor/Bouy article on the filemakerhacks website. Since the core of the system broke down into three distinct core areas; Contacts, Companies, and Searches (job searches not DB searches), the primary layouts and records found nice silos within this modified A/B / ERD configuration with the minor tables paying direct relation to the parent tables. This worked fine from a data standpoint, but what about the UI and meeting some very specific needs of the client in how they viewed the data? Challenge #1 The big focus for this client was monitoring and maintaining the contacts that would be placed in a pipeline as part of a search. The context for this was the search itself so a search had 1 pipeline but a contact could appear in 1 or more pipelines. From a process standpoint, the client wanted contacts in a pipeline to progress through 4 stages and they visually wanted to view the contacts in each stage. So, this was a simple parent – child relationship of search to search-pipeline. Thus, a set of 4 portals. My old-school way would to have been to create a calc field for each stage in the Search table and create 4 TOs of the same table and relate them by search ID and stage. A newer school way may have been to use SQL to collect IDs and set 4 global fields that would serve as a relation but all that seemed a bit too much. I had a simple parent-child relationship, I just needed to be able to filter and count the items based on simple change in the stage field. Enter the filtered portals. The end result from a display perspective of the records was easy. Using the filter portal settings in the portal dialog I could use the same relationship/portal and then filter each portal by the selected stage. While the implementation is made much easier, consideration should be given to performance. When considering a filter option, the number of records to be returned should always be a consideration since a filtered portal still calls ALL the related records and then applies the filter. This is substantially different than filtering a relationship which only accesses the filtered records. Per the client, a specific pipeline would max out between 150 and 200 contacts so the load on the filtering would be minimal and access would be on the LAN vs. WAN which is also a consideration. In the challenge referenced here, there is some question as to whether FileMaker is loading the same portal 4 times on the same layout and then applying the filtering to each or it collects the portal records 1 time and displays it through each portal that is then filtered. Based on the topography and number of records I am not sure the users will ever tell the difference. But if you have concerns about performance, I would say check out the post by Mark Scott of Beezwax, Performance Optimizations Make Compelling Case for FileMaker 15 Upgrade where he does some extensive metric testing on Portal loading in FM15. Challenge #2 So, although the display of the records in each filtered portal was straight forward, the counting applied to each portal was a little more difficult. I had a summary count field in the search_pipeline table called Rec Count. When I displayed it above each portal I realized it was not respecting the filter, all the portals showed the same count which was the total count for the related records of the search_pipeline. I immediately thought I was doing something wrong and no matter what I tried the count would not recognized the filter. I then thought maybe there was something wrong in FileMaker. It ended up being neither. In looking at a knowledge base article KB7557 I found my answer. The count is just a product of the architecture. Ok got it, so now what. With time being a constraint on the project, I went to creating a calc field in the Search table since for each portal that was an ExecuteSQL function for the Count of contacts in the associated pipeline with a specific stage. ExecuteSQL ( “SELECT Count (Search_Pipeline.”ID”) FROM “Search_Pipeline” WHERE Search_Pipeline.”ID_Search” = ? and Search_Pipeline.”Current Stage” = ?” ; “” ; “”; Search::ID; “Stage 1” ) This was straight forward, and met the objectives of the project. However, from a purist standpoint, this seemed to be an unnecessary addition of fields when all I needed to display was the count for each portal after filtering. Thus, I went looking for another way, and stumbled upon a blog post by Andrew Duncan at Databuzz Getting the IDs for Filtered Portal. In his article, Andrew proposed using a List of Summary field which was introduced in FM13. Interestingly the List of Summary field does actually respect the filtering in the portals and if you can grab the List of IDs from the first record of the portal you can count them with ValueCount. Based on a comment in blog I looked to use the GetLayoutObjectAttribute (“IDs” ; “Content” ) I could set a series of Global Variables $$Stage1, $$Stage2 etc… with a calculation of ValueCount ( GetLayoutObjectAttribute (“IDs” ; “Content” ) ) But in the end, the question became how do I ensure the calculation would actually work because the counts could change under different circumstances; i.e. when the record changes or when the user changes tab panels from the pipeline where the stages are actually managed and the progress dashboard. Using a script triggered by OnRecordLoad script trigger would work for the case when the user switched records. But how about when the user switches back to the Progress tab from the Pipeline tab? Using the OnPanelSwitch trigger does not work since it is a pre-trigger event and we need to be on the tab to set the global variables. I was therefore in search of one of the the myriad of calculations that would fire no matter what. To this end, I used a technique I had seen somewhere, where I could use the Hide Object calculation on an object like a button and have it fire all the time. I then placed a button on the layout with the following calculation for Hide Object When: Let ( [ $$stage1 = ValueCount ( GetLayoutObjectAttribute ( “stage1IDs”; “Content” )); $$stage2 = ValueCount ( GetLayoutObjectAttribute ( “stage2IDs”; “Content” )); $$stage3 = ValueCount ( GetLayoutObjectAttribute ( “stage3IDs”; “Content” )); $$stage4 = ValueCount ( GetLayoutObjectAttribute ( “stage4IDs”; “Content” )) ] ; 1 ) The button is always hidden and because of how FileMaker evaluates the Hide object calculation it always fires. In the demo file, you will see a mockup of the solution, the tab objects, portals, and most importantly a set of tables with minimal fields and counts that are true to the filtered portals with minimal “tricks” or additions. In my next Blog – Portal Filtering part 2, I will demonstrate a technique that allowed me to filter a portal by multiple fields and by using what is here allowed me to use GTRR and get the filtered records I was looking for. Todd Stark is a FileMaker 15 Certified Developer at Skeleton Key. About Skeleton Key Skeleton Key helps turn complex, complicated, and outdated systems into true information platforms. Our team of consultants and developers do this by developing custom-fit software tools and reporting dashboards that help businesses find, use, and understand their data, freeing them to focus on and grow their core business. In addition to custom databases and applications, we also provide training and coaching for getting the most out of your existing systems and understanding your unruly data. Skeleton Key is an open-book management company and active player of the Great Game of Business.