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 or other formatting of the posting may fail. For the best experience, please visit the journal directly by clicking (here)
FileMaker has the ability to dynamically change a relationship by altering the parent key information. Many times this is called a filtered relationship because you can add options to your parent key data that filters the resulting child records. I originally created this example to show off the ability to filter a portal by a single character. You can click a single character and see all your matching child records in a portal. Then it occurred to me, the user would also like to have the option of a birds eye view of all the possible related records. So I added the "All" option to the value list.
FYI... I only created example child records for A through C, so if you click any other option... the portal will not show any related records. Feel free to click the A, B, C and All radio button values and watch the portal update accordingly. You can also add new records to the child file were the company name or contact name start with letters other than A, B or C.
WHAT I DID TO THE CHILD TABLE - In the child table, I created two data fields, a calculated combination of those two fields, a calculated child key and an auto enter primary key. I don't even use the primary key field in the child table but it's a habit.
The two text fields are Company Name and Contact Name, which I use to calculate what I put in my portal to show the matching child record results.
Company With Calculation ( calculation - text result ) which is designed to show both the Company and Contact information unless one of those fields is empty. The to show just what data is there. The calculation is ...
IsEmpty(Company Name), Contact Name,
IsEmpty(Contact Name), Company Name,
Company Name & " - " & Contact Name)
The Company With Calculation is designed to show the user the company and contact information in the portal. We are combining two fields of data into one field, as long as both fields have data. This saves room in our portal row because we only have to have one field that can show X amount of characters. This is great when there isn't data in one of the fields or one of the fields has a lot of characters.
Child Key For Portal ( calculation - text result ) is my calculated child key field. It is used to show the first letter of the entered company and if the company name is empty, then it will show the first letter of the contact name. We use the left function to capture that one character. We are also adding the text string of "All" to every record but put a carriage in between them. This adds the effect of having a multiple line key field. The calculation looks like ...
IsEmpty(Company Name), Left(Contact Name, 1),
Left(Company Name, 1)) & "¶All"
WHAT I DID TO THE PARENT TABLE - We started off by creating Filter Character Global, a global text field. We added it to the layout and set it up to be a radio button type of value list. The value list has every letter of the alphabet, the number characters of zero thru nine and the text string of "All". We then setup our global field to be the parent key in the relationship to the field in the child file Child Key For Portal.
Next we added the portal to the layout and added the Company With Calculation field in the portal using the relationship we just created.
The 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.