Clearly the introduction of charts is one of the more visually compelling additions to the latest FileMaker release. But if you use portals in your solution to any degree, the new ability to filter those records efficiently and dynamically is perhaps one of the more functionally compelling.

Every FileMaker portal, including those in FileMaker 11, requires a table occurrence referencing a related table and a relationship between it and the table occurrence supporting your layout.  At the most basic level, if you wanted to view all invoices for a specific customer, you would build a relationship between your Customers table and your Invoices table based on the customer’s unique ID. But suppose you wanted your portal to just list those invoices that were outstanding, or all those that were more than a specific amount. To solve this, typically you would create a new table occurrence and relationship based on the filtered data you want to view.  And then, of course, you would have to point the portal to the new relationship. There are ways around this of course with some more robust programming, but a quick and easy approach is not quite possible.

FileMaker 11 introduces Portal Filtering which solves these problems and the results are impressive.  Enter layout mode, double click on your portal, and you find a new checkbox labeled “Filter Portal Records”, with a “Specify” button.  Click on the button and you are presented with the familiar calculation engine interface where you can specify the filter criteria. So let’s say you want to view only those invoices that are greater than $1000.00. Simply enter “Invoices::InvoiceAmount > 1000” into the engine and voila. When you re-enter browse mode your portal records are filtered accordingly and you never even opened up your database manager.

Now of course the example above is incredibly simplistic and still requires you to go back and forth between the portal setup window and your live application to obtain the filtered.  And of course we don’t necessarily want all of our users to be doing such things, whether we’ve provided them that level of access or not. Because the filtering is supported by the calculation engine, your ability to automate the filtering process for your users is incredibly broad.

Let’s say we want to filter the portal to only those records with a particular status. We can setup a few buttons that set global variables to filter those records with a single click. First, create a simple script with two steps. The first step will be “Set Variable”, which will set the global variable $$InvoiceStatus to the status we want to filter by. We’ll send that status as a script parameter when we click on the button, so set the value here to “Get(ScriptParameter)”. The second step will be “Refresh Window” with “Flush Cached Join Results” checked. This is necessary for a redraw of the layout, otherwise the portal will not visually update itself with the new set of filtered records. Save the script as “Set Portal Status” and return to the layout.

Next, create three buttons, labeled “Open”, “Paid” and “Overdue”, and place them in the layout above the portal. In the button setup dialogue for the “Open” button, click on “Run Script” and select “Set Portal Status” and as a script parameter, enter “Open”.  Follow the same procedure for the other two buttons, but change the script parameter to the appropriate status.

Finally, return to your portal setup, check the “Filter Portal” checkbox, and enter “Invoices::Status = $$InvoiceStatus”.  When you return to browse mode, clicking on any of those buttons will automatically and immediately filter your portal records accordingly.