Foundation: Relations and Conditional Lookups

Relations

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:

  1. Visually indicating relations in your project.
  2. Supporting Relation Diagrams.
  3. Supporting referential integrity validation.
  4. Creating a default join for conditional lookups.

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:

  1. 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.
  2. 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
  3. Multiple referenced lookup fields can be mapped to the same Conditional lookup field.
    1. Conditions can be used to choose which lookup value to use or you can select the first non-empty value.

Next