Linking Databases Together

A 'Data Source' field links one database item to an item in another database. It stores the other item's identifier and shows a Select or Search control for choosing it. Insites does not enforce the link: deleting the linked item does not change items that point to it, and the field does not cascade updates or deletes. Your code decides what happens to orphaned links.

The 'Data Source' fields allow you to build databases with the following features:

• Linked items:

  The field stores the identifier of the linked item. A 'Data Source' field holds one identifier. A 'Data Source (Multiple)' field holds a list of identifiers.

• Reading linked data:

  Insites resolves a link when your code queries the data, not when an item is saved. Your code can then read the linked item together with the item that points to it.

Conceptual example

To understand how these links work, let's look at a simple store setup with two tables:

• The parent table : Holds the master list of people.
• The child table : Holds the list of purchases.

The child table of orders () holds a 'Data Source' field. It stores the of a row in . Each row in the orders table can then be associated with a specific customer from the customers table.

Insites does not check that the stored CustomerID exists in the customers table. If your application needs that guarantee, check it in your code.

table_customer

table_orders

In this example, is the parent table while is the child table. 'CustomerID' in is the 'Data Source' field used to link the two tables together. It stores the CustomerID of a row in .

Creating a Data Source field

Before we proceed with creating a 'Data Source' field, note that there are two 'Data Source' field types:

• Data Source: Saves the value as a String and only saves one value per field.
• Data Source (Multiple): Saves values as an Array and saves multiple values per field. Used to set relations.

To link databases with a 'Data Source' field:

1. Select Data from the main menu.
2. Select Databases.
3. Locate the database where you want to add a 'Data Source' field and select its Configuration row action link.
4. Navigate to the 'Fields' tab.
5. Select ADD FIELD.

6. Fill out the information required on the form. You may review the input requirements by visiting the following links:

   • Data Source
   • Data Source (Multiple)
7. Select SAVE FIELD to save your field.
8. Select SAVE to finalize your changes.