Linking Data
Overview
Linking is done by the Cinchy ID, which is unique. When you create a link column, you select a column to link to. This is simply a decision on which field to show from the linked record. You should pick a unique field to link on to avoid confusion if possible.
Once a record is created, its Cinchy ID never changes. This means that modifying the row of data in the linked table won't change the relationship in your table to that row. This also means that if you didn't use a unique column, even though the UI looks the same, you are actually linking to different rows.
Choose a linked column
In general, you should only use unique columns as the linked column. This needs to be balanced with readability in other tables.
For example, Full Name might not be unique to every employee, but it's more readable and understandable than Employee ID. In other cases, it makes sense to link via an ID and add a display column to show relevant information.
Allow Linking
To help other builders follow best practices of only linking to unique (or close to unique, such as Full Name) columns, you should un-check the Allow Linking checkbox for non-unique columns so they won't be able to use it for linking.
Allow Display in Linked View
If this option is unchecked, it prevents users from showing this column in another table.
For example, if you have an ID card # within an employees table, you may not want to display it to the rest of the company because it simply would not be relevant when they're linking to employees and want to see additional information (such as department, title, location). Arguably, a lot of these columns are also taken care of by access controls (since most people won't have access to view that column).
Deselecting this box should be done sparingly, as it doesn't impact the security of your data, only how convenient it's to see it.
Display Columns
When you select a record to link to on the Manage Data screen, it can be useful to see additional information about the records to ensure that it's the record you want to link to. You can add additional display columns in the advanced options for link columns.
When you type in the cell, all displayed columns will be searched through, not just the Linked Column (Image 3). (Green doesn't have a B in it, but #00B050 does so the Green record shows up)
Linking Additional Columns Using a View
When you add a linked column to your table, you are able to use that column as a reference point for adding other columns, and their associated data, from the linked table into your display. You are able to use table Views to do so.
This concept is best understood with an example.
Use Case: A builder is creating a table called [Product].[Release Notes]. For each release note, they (1) want the label of the associated ticket from the [Product].[Tickets] table and (2) want to display other information about the product ticket such as its status.
Requirement (1) can be done with a linked column.
Requirement (2) can be done using a view as follows:
- Create the link column for requirement (1) as normal. In this example, the column "Ticket" has been created and links to the "Label" column of the [Product].[Tickets] table.
- Identify the additional data you want to pull into the [Product].[Release Notes] table from the [Product].[Tickets] table. In this example we want to pull in the ticket status.
- Create a new View on your original table.
- Under the View "Columns" tab, find the linked column and click on the triangle to expand it. In our example, this will populate a new list with all of the columns in the [Product].[Tickets] table that have linking allowed.
- Select a column to display. In this example we will select "Status". You can add in as many display columns as needed for your use case.
- When you navigate to your new View, you will now see the additional linked data. In this example, when we select an entry from our "Ticket" linked column, the associated ticket status from the [Product].[Tickets] table will auto-populate.
Link Filter
The link filter filters out records from the drop down list. This is useful for reducing the options to only the typical use case. Commonly used for filtering the drop down to a list of active users or other resources, while not preventing someone from entering missing records with inactive resources.
This is only a display filter; it doesn't prevent other values from being entered as long as they're valid records in the linked table.
Relationships
You can define 1 to 1, 1 to many, and many to many relationships.
1:1 Relationship
Generally it's rare to link 1:1 relationships since they should usually be in the same table. For example, you would not have a separate table for Employee Phone Number and Employee Address, they would simply be two columns within the Employees table. However there are cases nonetheless where it makes sense, for example, a Keycard tracking table where each keycard has 1 assigned employee.
To enforce a 1:1 relationship within Cinchy, you set the unique constraint and leave it as single-select when creating a link column.
1:Many Relationship
A common relationship to have is a one to many relationship. For example, one customer can have multiple invoices.
To enforce a 1:many relationship within Cinchy, you want to create a link column in the table on the “many” side of the relationship (in the above example, in the invoices table) and leave the link column as single select.
Many:Many Relationship
You can also have a many to many relationship. For example, you can have multiple customers, and multiple products. Each customer can order multiple products, and each product can be ordered by multiple customers. Another example is books and authors. An author can write multiple books, but a book can also have multiple authors. You can express many to many relationships in two ways.
For the use case of multiple customers and multiple products, you can use orders as an intermediary table to create indirect relationships between customers and products. Each order has one customer, and each order has multiple products in it. You can derive the relationship between customers and products through the orders table.
To create a many:many relationship through a different entity, you want to create a table for orders. Within orders, you want to create a single-select link to customers and a multi-select link to products.
For the use case of books and authors, it makes sense to create a multi-select link column in the Books table where multiple authors can be selected.
To create a multi-select link column in Cinchy, you select the Multi-Select option when you create a new link column.