Setting Up Many-to-Many Filter Relationships

With many-to-many relationships, the base entity can be linked to many related entity records, and the related entity records can be linked to many base entity records. For example, an organization may require the ability to search for persons who have been assigned a particular function. Because each person can have many functions and each function could be linked to many persons, there is a many-to-many relationship between the Functions and Persons entities.

Many-to-many filter relationships are similar to one-to-many relationships in that the administrator must specify the Related Entity and Related Field. For many-to-many filter relationships, however, additional information must be specified. The Join Field and Join Table fields on the Filter Relationships form are only relevant when setting up many-to-many relationships between entities. For this example, the filter relationship between the Persons and Functions entities requires the join table vwPersonFunctions (which is also the base view for the PersonFunctions sub-type entity).

1. Open a new Relationships form from the Entities form for the base entity (located in the entity's Configuration > Filter Relationships tab).
   
    
2. Select the Related Entity from the list.
3. Set the Relationship Type to Many-To-Many.
4. Enter the name of the table that defines the link between the two entities in the Join Table field.
5. Enter the name of the field within the Join Table that links the Join Table to the base entity into the Related Field field.
   • For example, when creating a many-to-many filter relationship with Functions for the Persons entity, the Related Field corresponds to vwPersonFunctions.PersonID, which is the Persons entity's link in the Join Table.
   • The Join Table should be a view in the Database Objects service. You may need to create this table that defines the relationship between the entities if one does not already exist.
      
6. Enter the name of the field within the Join Table that links the Join Table to the entity specified as the Related Entity into the Join Field field (for example, FunctionID).
7. Enter a description. This description displays in the list of related entities when setting up a filtered view or search for the base entity.
8. Click OK to close the Relationships form, then save the Entities record.

Unlike in one-to-many relationships, many-to-many relationships do not share a direct link between the tables and require a join table to provide that link. For this reason, many-to-many relationships are also known as indirect relationships.