From Dwayne Wright PMP
Certified FileMaker Developer
A READER ASKS
I'm a beginner with filemaker and by searching a solution for my problem I came across your page.
Each company (GLStammFirma) has one or more contact person (GLStammKontakt), each contact person has one or more telephone numbers (GLStammTelefon). There are priorities defined for the contact persons and also for the telephone numbers.
I'd like to see in the companys layout just the most important contact person of the selected company and just the most important telephone number of this contact. With some buttons I'd like to navigate through the contact persons and the corresponding most important telephone number should switch automatically with this navigation.
Well, there is (at least) two possible directions you can go. One is to sort the relationship you are using so the most important related record is at the top and the second is second filtered relationship that only shows the most important contact. The one that I would recommend is setting a sorting options for the relationship itself.
SORTING THE RELATIONSHIP
In the bottom third of the define relationship operator box, you can see options for each table occurrence in that relationship. These settings include ... "Allow creation of records in this table via this relationship", "Delete related records in this table when a record is deleted in the other table" and Sort Records. The settings are on both sides because FileMaker relationships are bidirectional.
The sort dialog box for the related records is practically the same as the sort dialog box for records. The settings include ascending, descending or custom based on a value list for each related field you want to sort.
So you can setup the relationship to sort the related records so the most import related record is always at the top. Then you can put the related record fields on the layout and it will always show the most important record. Now this setting is independent of the portal sort settings, so that might be a bit confusing.
FILTERING THE RELATIONSHIP
Now the other way is to create a duplicate but slightly different relationship. That would involve setting up a relationship with two layers. The first layer is the same as the relationship you are using. The second layer would have a constant key field on one one side and the other side would have the flag for most important contact. So you would have something like ...
Company ID (in company) = Company ID (in contacts)
Constant 1 (in company) = Priority Setting (in contacts)
So your most important contact would need to have a 1 associated with it. I’m just saying 1 as an example. You could have any matching combination of settings that match the most important contact.
More info about the author and FileMaker in general, contact me at firstname.lastname@example.org.
© 2008 - 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.