Related Fields On FileMaker Layouts

From Dwayne Wright PMP
Certified FileMaker Developer

WEB: www.dwaynewright.com
EMAIL: info@dwaynewright.com
TWITTER: dwaynewright
YOUTUBE: FileMakerThoughts

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). 

In this example, we have an employee table and promotions table. On the standard layout, you will see the employee name, the promotion title and the promotion date. The layout is linked to the employee table but the promotion and promotion date fields are from the promotions table. There is a relationship between the two tables using the primary key field from the employee table. The relationship is also setup to automatically create new records and is setup to sort the records in descending order by the promotion date.

The sort order of the relationship is important in this case. The related field you place on the layout will show the first compared value of the relationship to the table occurrence. By default, you will see the first related record created. This is NOT what you would want in the example above because it would give you the first related promotion / salary information and you want the last or most current information. To see the latest record, be sure you sort the relationship you are using, so the most current record appears first ( just like you would if you wanted it to show up that way in a portal ).

Also the related field on a layout, using a relationship to create new related records automatically, is also critical in this example. If you do not have any related records and have the new related records automatically setup in the relationship dialog box, then you can type new data into that field just like a field that belongs to the parent table. This is like a relational splice between table occurrences.

You can see a portal that show the various promotions. The portal is sorted by the order they were entered. However, the relational fields show a different sort order. The related fields have a sort order descending from the date and time entered. In FileMaker 6, the only way we could do this was with two relationships using the same key fields but a different sort order. This is because in FileMaker 7 a portal can have it's own sorting and override the sort setting of the relationship it uses.

I decided to go a few steps further in this example. It occurred to me that there was no way to safely add new promotions. So I decided to employ yet another new FileMaker 7, the ability to open up a new window and make it look like a dialog box. This new really does work like a dialog box because you have to click the submit button for the new record to be saved. This is because the layout is setup so that record changes are not saved automatically.

So the "Add New Promotion" button runs a script that opens a new window in the promotions table and inserts the primary key field from the employee record. This is done because the button has an attached optional script parameter doing so. The user enters in the new promotion and the current date and time are recorded via auto enter options for those fields. When the user clicks the submit button, the new record is saved ... and ... you can see the new promotion information in the related field from the starting layout.

An example file can be downloaded by clicking (here)
=
More info about the author and FileMaker in general, contact me at info@dwaynewright.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.