Deciding where to place related fields

•Directly on layouts: Place related fields directly on a layout to display data from the first related record, even when there are more than one related record that match the criteria of the relationship. (The first related record that is displayed is determined by whether the relationship specifies a sort order.)

•Place related fields on the invoice that displays data from the Clients database, such as the client's identification number and contact information. (The match field is Client ID.) There is only one record in the Clients database for each Client ID, so each related field on the invoice displays the data for that client.

•Place a related field on the invoice that displays the most recent value from the Order Date field in the Order History database. (Again, the match field is Client ID.) If the client has placed more than one order, there are multiple records in Order History that match this client's Client ID. By defining a sort order on the Order Date field when you define the relationship, the most recent date displays in the related field when it's placed directly on the layout (not in a portal).

•Place related fields on the invoice that display data about each ordered item, such as Product ID, Product Name, Unit Price, and so on. (The match field is Order ID.) Since in most cases there is more than one product on the invoice (you're displaying more than one related record), you create a portal to hold the related fields. Each row of the portal displays one related record with the related fields you select from the Line Item database.

When you place a related field in a portal, FileMaker Pro uses one of two starting points to evaluate the related data to display: the record in the portal’s table, or the record in the layout’s table. The starting point is significant because it affects the related data that the field displays.

If the path of relationships from the layout’s table to the field’s table includes the portal’s table, the record in the portal’s table is the starting point. Otherwise, the record in the layout’s table is the starting point.

For example, the following relationships graph shows a school enrollment database. It contains tables for teachers, classes, and students, and an enrollment table to indicate which students are in each class. There is also an advisors table (another occurrence of the teachers table) which assigns a faculty advisor to each student.


Placed field	Path from layout’s table to field’s table	Does path include portal’s table?	Starting point	Comment
Name field from Students table	Classes-Enrollment-Students	Yes	Portal’s record	The placed field is from the portal’s table (a very common occurrence). The field would display an enrolled student in each row of the portal.
Name field from Advisors table	Classes-Enrollment- Students-Advisors	Yes	Portal’s record	The placed field would display each student’s advisor in each row of the portal.
Name from Teachers table	Classes-Teachers	No	Layout’s record	The placed field would repeat the teacher for the class in each row of the portal, not the first related teacher value for each student.
Class Name from Classes table	Classes	No	Layout’s record	The placed field is from the layout’s table (an uncommon occurrence). The field would repeat the class name for each row in the portal, which is redundant if the layout includes the Class Name field outside of the portal.

•Value lists: when a value list is defined to include only related values from a field, and a field in a portal is formatted to display this value list. (The starting point determines the values displayed in the value list.)

Usually, FileMaker Pro determines the correct related data to display. However, you can change the starting point by modifying the relationships graph to include other tables and relationships, and then changing the related fields referenced in the portal.