Foundation: Relations and Conditional Lookups
In TimeXtender Relations are used to specify how two tables are related. Relations are created by dragging a field from the source table onto the related field on the destination table. Typically this is done from the "One" to the "Many" of a one-to-many relationship, or from the Primary Key to the Foreign Key.
Relations are used for:
- Visually indicating relations in your project.
- Supporting Relation Diagrams.
- Supporting referential integrity validation.
- Creating a default join for conditional lookups.
Conditional Lookups are fields created in a table in which the values are populated from another table based on a join (relation). While they are quite different, this is how your traditional SQL LEFT JOIN is performed in TimeXtender. Conditional lookups are created by dragging a field from the source table onto the destination table name. Typically this is done from the "One" to the "Many" of a one-to-many relationship.
Important key features of Conditional Lookups:
- Joins specify which value is inserted from the source table. For example, if ProductModelID = 1 then insert the value of ModelName where the Primary key is also = 1. If a relation between the two tables exists, a lookup field with no Joins specified will use the Relation as a join.
- Conditions can be assigned to lookups. Populating the referenced value only if a condition is met. For example, if [Field] is empty or if [Flag]=1
- Multiple referenced lookup fields can be mapped to the same Conditional lookup field.
- Conditions can be used to choose which lookup value to use or you can select the first non-empty value.