Especies del Género Meloidogyne (Sosa, 1983)

When you join two tables using a relationship, you establish criteria that FileMaker Pro uses to display or access related records. Your criteria can be simple, such as matching a field in Table A with a field in Table B, or more complex. A more complex relationship definition will usually return a narrower set of related records. Examples of complex relationships include using multiple match fields on one or both sides of the relationship, comparing match fields using non-equal relational operators, or using calculated match fields.

Single-criteria relationships

In a single-criteria relationship, data in one field is matched to data in another field.

For example, a record in either table is related to any record in the other table when the values in the Client ID field in the Clients table and the Client ID field in the Phone Numbers table are the same.

Use a single-criteria relationship when you want to relate two tables based on a single common value, such as a serial number or an ID number. For example, a clients database has two tables: Clients, which stores names and addresses, and Phone Numbers, which stores phone numbers and types of phone numbers, such as work, home, fax, and so on. The data is split between two tables because a single client can have multiple phone numbers.

The tables have the following fields:

The Client ID field is the match field in the relationship between the two tables. In the Clients table, the Client ID field is set to

automatically enter a serial number, giving each record in the Clients table a unique ID number. In the Phone Numbers table, the Client ID is just a simple number field.

Because the relationship is defined to allow the creation of related records in the Phone Numbers table, the Client ID number from a Clients record will be automatically inserted in the Client ID field in

the Phone Numbers table in each new related record. This allows many records in the Phone Numbers table to be related to a single record in the Clients table.

Users will be able to enter a phone number in an empty portal row, and FileMaker Pro will automatically create a related record for that phone number.

Multi-criteria relationships

In a multi-criteria relationship, you increase the number of match fields, which increases the criteria that FileMaker Pro evaluates before successfully joining related records. In a multi-criteria relationship, FileMaker Pro compares the values from each match field on each side of the relationship in the order in which the fields appear. This is known as an AND comparison; to match successfully, every match field must find a corresponding value in the other table. Use a multi-criteria relationship when you want to relate two tables based on more than one common value, such as a client ID and a date.

Table Field name Comment

Clients

Client ID Number field, auto-enter serial

number. Match field for the Clients table

First Name Text field

Last Name Text field

Address1 Text field

Address2 Text field

City Text field

State Text field

Phone Numbers

Phone Number Text field

Phone Number Type Text field

Client ID Number field. Match field for

the Phone Numbers table

Enter a phone number in empty portal row Portal

In this relationship, both Client ID and Rental Date are match fields to the Rental Line Items table. A record in the Clients table with a Client ID value of 1000 and a Rental Date value of 10/10/2004 will only match those records in the Rental Line Items table where both fields Client ID and Rental Date have values of 1000 and

10/10/2004, respectively. If Client ID fields in both tables have matching values, but Rental Date fields in both tables do not, no related records are returned by the relationship.

For example, a travel equipment rental database has three tables: Equipment, a static table that stores equipment rental inventory; Clients, which stores client names and addresses; and Rental Line Items, which stores a record of each equipment rental, including the equipment ID, the client ID, and the rental date. The purpose of this database is to track equipment rentals and display all rentals by a selected client on a selected date.

Although the database uses three tables, the multi-criteria relationship is between the Clients and Rental Line Items tables. These tables have the following fields:

Because the relationship is defined to allow the creation of related records in the Rental Line Items table, users will be able to enter rental information in an empty portal row, and FileMaker Pro will automatically create a related record for that rental.

Relationships using comparative operators

In a comparative relationship, you use one or more of the following operators to compare match field values in the two related tables. The relationship returns related records when the comparison evaluates successfully, according to the criteria you establish.

Table Field name Comment

Clients

Client ID Number field, auto-enter serial

number. One of the match fields to the Rental Line Items table

Rental Date Date field. The other match

field to the Rental Line Items table

Rental Line Items

Match fields

Match fields Client ID Number field. One of the match

fields to the Clients table

Rental Date Date field. The other match

field to the Clients table

Equipment ID Number field

Relational operator Matches these records

= Values in match fields are equal

≠ Values in match fields are unequal

< Values in the left match field are less than values in

the right match field

≤ Values in the left match field are less than or equal

to values in the right match field

> Values in the left match field are greater than values

in the right match field

≥ Values in the left match field are greater than or

equal to values in the right match field

Table Field name Comment

Select to allow creation of related records in Rental Line Items table

In this example, the Tour Date fields in the Tours table and the Clients table are match fields. If a record in the Tours table has a Tour Date value of 12/11/2004, all the records in the Clients table with a Tour Date value of 12/10/2004 or earlier are related to it. In the Clients table, if a record has a Tour Date value of 12/11/2004, all records in the Tours table with Tour Date values of 12/12/2004 or later are related to it.

Relationships that return a range of records

A relationship that returns a range of records is similar to a multi- criteria relationship, except that you use comparative operators to find records that are greater than one of your criteria and less than your other criteria. This type of relationship is commonly used to return records that fall between a range of dates or serial numbers. For example, from within Table E you want to view all the records in Table F that have Date Field F values that are greater than Starting Date E values and less than Ending Date E.

This relationship returns those records from Table F that have a Date Field F value that is later than Starting Date E and earlier than Ending Date E.

x All records in the left table are matched to all records

in the right table, regardless of the values in the match fields

Relational operator Matches these records

Greater than relational operator

Match field Match field

Match field Match fields

Indicates that several different relational operators are used in this relationship

This relationship uses the greater than and less than relational operators

For example:

Another example:

Self-joining relationships

A self-join is a relationship in which both match fields are defined in the same table. Define a self-join to create relationships between records in the same table. Use a self-join in a portal on a layout of the current table to display a subset of data that is in the current table, such as all the employees of each manager.

When you define a self-joining relationship in the relationships graph, FileMaker Pro generates a second occurrence of the table upon which you are basing the self-join. FileMaker Pro does this to prevent the relationships graph from forming a cycle, which would make it impossible to evaluate the relationship.

This relationship returns related records in Table G when the value in Text Field G1 is the same as the value in Text Field G2.

For more information about the types of relationships, see Help.