From Dwayne Wright PMP
Certified FileMaker Developer
Please Note: If you are viewing this page in a news feeder, the images may get munged up a bit. For the best experience, please visit the journal directly by clicking (here).
FileMaker 11 has a new feature that allows for filtered portal options from the portal setup dialog box. Opinions about this new feature have been mixed and vary wildly. I've heard comments that range from "not completely thought out" to "the best new feature in FileMaker 11".
However, there have been techniques to filter portals ever since portals first came out (FileMaker 3.0 BTW). One of my favorites involves the use of the GetField function because it makes the process easy, clean and robust.
THE GETFIELD FUNCTION EXPLORED
The GetField function can return at least two different results based upon if you include quotation marks in the syntax of the field name. If you use quotation marks, you get the information inside of the field. If you don’t use quotation marks, it looks at what is inside of that field. If the data inside the field you reference within the GetField parameter is equal to the name of another field, it returns the contents of that field for that record.
HOW ABOUT AN EXAMPLE?
Say that we have a FileMaker table that has information about a number of people. The functionality we want to present is the ability to see other records that match the record we are on. So the matching recods are show in a portal on the layout and we want the user to have the ability to filter what they consider a match. For example they only want to see matching cities, matching states or matching phone area codes. I created a short quicktime movie to illustrate the concept and you can view/download it by clicking (here).
GOING UNDER THE HOOD
To filter our portal, we will want to create a global field and save it as a text result. It will need to be a value list with the 3 options we want. These options will also be the names of the fields in our table and will be using as our child keys. As you might have imagined, these value list selection options will be City, State and Area Code.
In the “PARENT MATCH FIELD: GetField(Portal Filter)” calculation, we create a field called Parent Match Field and we are using the GetField function to get our global filter field ( which we call portal filter ). So if we choose City, the contents of the City field will be in the Parent Match Field field. Using the GetField function without the quotation marks will return us the value within the field we are choosing. So if the Portal Filter field has be value City within it, the function will return the value of the City field for the current record we are on.
Here is a sneak peek at the calculations in out parent and child match fields.
You can experiment with the quotation marks inside of the GetField function as well to see how the results change. Just make sure the quotes are off when you save the file or the example will be broken.
In the “CHILD MATCH FIELD: State & "¶" & City & "¶" & Area Code
“ calculation, we are putting in all the possible matches for a particular field and separating them by a carriage return. This is what we call a multiple key field.
BTW: THIS EXAMPLE USES A SELF RELATIONSHIP
A Self Relationship is a common term for a situation in which both sides of the FileMaker relationship are the same table. It is even possible that the matching key fields are the same field. This can be handy when using the Go To Related script step or when using Aggregate Functions. Self relationships can be one to many, many to many or even the filtered varieties. In this example, we want to see similar contacts, so a self relationship is the best way to go.
I added a tab to the example file so that you can play with the settings of the Portal Filter field and watch the Parent Match Field update itself.
There are a few things we want to recommend, if you choose to use this example in your FileMaker file. The state should be validated to be a 2 character text field. There is at least one city that has the same name as a state ( New York ) and that could goof you up ... if you don’t use 2 character state abbreviations.
An example file can be downloaded by clicking (here).
More info about the author and FileMaker in general, contact me at email@example.com.
© 2007 - Dwayne Wright - dwaynewright.com
The material on this document is offered AS IS. There is NO REPRESENTATION OR WARRANTY, expressed or implied, nor does any other contributor to this document. WARRANTIES OF MERCHANT ABILITY AND FITNESS FOR A PARTICULAR PURPOSE ARE EXPRESSLY DISCLAIMED. Consequential and incidental damages are expressly excluded. FileMaker Pro is the registered trademark of FileMaker Inc.