To represent relationships between pages, the Platform uses a table lookup convention. The metadata stored in Pages and Page views is used as a guide to the Platform on how to build the queries it runs in SQL Server.
The easiest way to understand this convention is to add records in related tables to a view in the Views/Advanced Search tool and look in the SQL Layout tab to see how the Views/Advanced Search tool applied the table lookup convention to refer to the table that contains the field being used for display or criteria.
The convention can be summarized as <Foreign_Key>_Table.<Field_Name>.
Up to 6 levels of foreign key lookups can be chained: <Foreign_Key>_Table_<Foreign_Key>_Table.<Field_Name>.
This is contextual. The first foreign key must be on the table related to your current page for a lookup to function
A common scenario is selecting the Participant Type for display in a Contacts View. In this example, we will explore how that relationship is expressed in the Views/Advanced Search Tool, the Table Lookup Convention, the database itself and SQL (with no use of the convention).
Contact > Participant > Participant Type
This is what the Views/Advanced Search Tool looks like navigating this relationship to select the Participant Type field for a View in Contacts:
When the Participant Type field is selected in the Views/Advanced Search Tool, here is the resulting SQL (which uses the Table Lookup Convention):
Participant_Record_Table_Participant_Type_ID_Table.[Participant_Type] AS [Participant Type]
Here is a simplified version with highlighted field names. You can see how the "_Table" gets added to represent the relationship:
Participant_Record_Table_Participant_Type_ID_Table.Participant_Type
The following diagram shows the three related tables: Contacts, Participants, and Participant Types. The Contact table is related to the Participants table because the Contact field "Participant_Record" stores an ID (a key) from the Participants table. The key icon in the Participants table represents the primary key, which is the ID field for the table.
The lines between the tables represent these relationships. In the SQL example below, they are represented by the JOIN clause.
The same relationship in SQL (without the use of the convention) would look something like this. Here it is written as a subquery which could be used as a field. The same fields are highlighted for comparison (they read in reverse):
(SELECT PT.Participant_Type FROM Participants P JOIN Participant_Types PT ON P.Participant_Type_ID = PT.Participant_Type_ID WHERE P.Participant_ID = Contacts.Participant_Record)
You can see that the Table Lookup Convention greatly simplifies the representation of this table-to-table relationship.
When the Table Lookup Convention is used, the platform converts these references to standard SQL statements and uses the convention names as table aliases. The same fields are highlighted for comparison:
SELECT Participant_Record_Table_Participant_Type_ID_Table.Participant_Type FROM Contacts LEFT JOIN Participants AS Participant_Record_Table ON Participant_Record_Table.Participant_ID = Contacts.Participant_Record LEFT JOIN Participant_Types AS Participant_Record_Table_Participant_Type_ID_Table ON Participant_Record_Table_Participant_Type_ID_Table.Participant_ID = Participant_Record_Table.Participant_Type_ID
