A relationship is a connection between two tables. A table-to-table relationship is used to better organize your data, and reference data existing in another table rather than repeating the same data in multiple tables.
 
By referencing data from another table, you will save time by avoiding entering the same information in multiple tables.
 
Example: A customer table maintains the customer contact information, and customer address. A customer can 

place several orders that are stored in an Order table. Adding a relationship between the customer table and the order table will organize your data and save you time because you will not have to re-enter the same customer info for every record in the order table. With a relationship between the customer and order table, you will be able to simply link order records to the corresponding customer records.


To establish a relationship between two tables:


1. On the sidebar menu, click on the drop-down arrow next to the desired table, and click Relations. This will display the Table Relations Page.

 

2. On the Table Relations Page, click Add Relation at the upper-right corner.

 

3. On the Create Relations Page displayed, click the drop-down option to select the other table to which you wish to add a relationship to. Once the table is selected from the drop-down, the Create Relations Page will be displayed.


4. Under the section titled “for this relation, which is true?”:

 

Select the parent-child relationship you wish to create between the two tables by selecting one of the relationship options displayed on the screen. 


For example: a customer can place many orders (hence a single customer can have many order numbers), but order numbers are unique (hence one specific order number is only assigned to one specific customer). In this case, the relationship option would be as follow: Each Customer may have many Orders.

 

In this case, since a customer can have many orders, the customer represents the parent table, and the order represents the child table.



Note: Based on your selection, the Application will display the Parent and Child Tables on the screen.


5. Under Parent Table, select the Key Field of the parent table from the drop down option. 

The key field is the unique identifier (Unique ID) assigned to each distinct element in a table. For example, the unique Customer ID given to each customer represents the Key Field of the customer table.


Note: The Record ID field, generated by Ignatius on each table, is typically used as the key field for relationships, however you can specify a different key field if desired.

Only fields with the Unique Constraint will be available as a key field. The Unique constraint is set when adding fields to a Table. 



Next, on the Create Relations Page, under Child Table, the Field specified represents the name of the reference field that will be added to the child table. This field will relate to the key field in the parent table.

 

In the example screenshot below, the field Related Customer will be added to the Order Table and will reference the Customer ID (Key Field of the Customer Table (Parent Table)).




6. OPTIONAL STEP: In addition to creating a relationship, you will also be able to create Lookup Fields:


A Lookup Field is a read-only field that displays information pulled from a reference table (parent table). When a record is created in a child table, lookup fields will be automatically populated with the info pulled from the parent table. 


For example, customer info such as First Name, Last Name, Address will be automatically pulled from the customer table, and populated onto the order table based on the Reference Field Customer ID.


If you do not wish to create Lookup Fields, skip this step and continue to the next step below. 


Note: Lookup fields can be added at any point by editing the table-to-table relationship.


To create a lookup field: 

  • Under Child Table, click the Add Lookup Field button. 


  • Click on the drop-down option to select the Lookup Field you wish to add to the child table.  


  • You can add other lookup fields if needed by clicking the + option.


  • If more than one field is added, the screen will display +/– options to add and remove fields as needed. Use the +/– options as needed.  


  • Click Save when done. 


7. Once the relationship details are added, click Save to create the Relationship. 


The Relationship created will now be displayed on the Table’s Relationships Page.


Note: The relationship can be edited at any point by clicking the edit icon next to the relationship record on the RelationsPage for that table.


Once the relationship is created, the reference field will be automatically added to the child table (the table referencing the information from the parent table).


In the example screenshot below, the field Related Customer was automatically added to the Order Table to reference the Key Field (Customer ID) of the Customer Table.