WHAT IS A LINKED DATABASE COLUMN?

A Linked Database Column is a Database Column Type that lets you build links between entries in different Kahootz databases in the same workspace. This reduces the need to duplicate information and helps to keep your information up-to-date. We’ll explain the idea and the terminology using some examples.  

Suppose your workspace has 3 databases which store information about Projects, People and Organisations.

In your Projects database, you might want to store the name of the project manager for each project. Instead of adding “Project Manager” as a text column, you can add it as a Linked Database Column that links to an entry in the People database.  

Here’s the Projects database after adding the “Project Manager” column:

When someone adds or modifies a project, instead of typing in the project manager's name - like they would with a TEXT column - they can select the Project Manager by browsing or searching the People database.

Here’s the page that lets you select a Project Manager for a project by browsing the People database:

When someone views a project, they can click on the project manager’s name to see that person’s full entry in the People database.

Here’s what you see when you click on a project manager’s name.  

Because the information is linked and not copied, if you make any changes to the People database, those changes are reflected in the Projects database. For example, if you fix a spelling error in a person’s name, that update is shown in the Projects database. If you delete a person, they will be removed from all the projects they are the project manager for.  Updates like this to Linked Database Columns are immediate and automatic.

You can add as many Linked Database Columns as you like to a single database. 

If you want to store the organisation that’s responsible for each project, you can add another Linked Database Column to the Projects database called “Organisation Responsible” which links to the Organisations database.

Here’s what the Projects database looks like after adding the “Organisation Responsible” column:

You can add multiple links to the same database for different purposes.  

If you want to store the list of team members for each project, you can add another Linked Database Column in the Projects database called “Team Members” that creates a second link to the People database.

Here’s what the Projects database looks like after adding the “Team Members” column:

Once you have set up some Linked Database Columns, you can easily navigate around your data, following links between projects, people & organisations.

ALLOWING MULTIPLE VALUES TO BE SELECTED

With a Linked Database Column, you can decide whether users can select just a single value or multiple values, when they add or modify an entry.

In the previous example, you would set the “Project Manager” column to allow just a single value – because each project has just one manager. 
The “Team Members” column would be set to allow multiple values because a project can have several team members.

Like other database column types, Linked Database Columns can be mandatory, which means that users are required to select a value.  You would set the “Project Manager” column to be mandatory because a project must have a project manager.

RECIPROCAL COLUMNS

It’s often useful to see the links between databases from either direction.  

In the previous example, we added a Project Manager column to the Projects database that links to the People database. But when you look at a person in the People database, you also want to see which projects they are the project manager for, without having to search through the Projects database to find the person’s name.

Linked Database Columns let you do this very easily!  When you add a Linked Database Column, Kahootz can automatically add a column in the database you are linking to called a Reciprocal Column.  The Reciprocal Column links from the linked-to database back to the original database.

When we added the “Project Manager” column to the Projects database, Kahootz automatically added a Reciprocal Column called “Projects this Person Manages” to the People database.

Here’s what the People database looks like with “Projects this Person Manages”  column:

The links in Reciprocal Columns are automatically updated by Kahootz.  If you modify “Project X” in the Projects database and set “Bob” as the project manager, when you view “Bob” in the People database, “Project X” is shown in the list of projects he is managing.

Using Reciprocal Columns means you can update entries from either end of the link.  As well as modifying a project and choosing the project manager, you can also modify a person and choose which projects they manage from the list of projects.

Reciprocal Columns are optional – you don’t need to create one if it would not be useful.  Reciprocal Columns can only be added at the time the Linked Database Column is added.  They cannot be added to an existing Linked Database Column.

When a Linked Database Column has a Reciprocal Column, both ends of the link are equivalent.  The Reciprocal Column for “Project Manager” is “Projects this Person Manages”, and the Reciprocal Column for “Projects this Person Manages” is “Project Manager”.

You can delete a Linked Database Column or its Reciprocal Column without affecting the opposite end of the link.  For example, you can delete the “Project Manager” column in the Project database without affecting the “Projects this Person Manages” in the People database  – and vice versa.

LINKING ENTRIES IN THE SAME DATABASE?

Up until now, your Linked Database Columns have linked between two different databases.

Suppose you want to store a person’s line manager in the People database. You can add a Linked Database Column called “Line Manager” to the People database that links to the People database itself.

Here’s what the People database looks like after adding the “Line Manager” column:

Even when you add a Linked Database Column that points to the database itself, Kahootz can add a Reciprocal Column.  The Reciprocal Column for the “Line Manager” column is “People this person Manages”.   As before, the Reciprocal Column is optional.

THE ‘MAIN COLUMN’

When you view a database, you’ll notice that for Linked Database Columns Kahootz shows the value from one column value for each linked entry.

For example:

• When you view a project in the Projects database, the “Project Manager” field shows just the person’s name.  
• When you view a person in the People database, the “Projects this Person Manages” field just shows the project’s name

That’s because each database has a Main Column.  The Main Column is the value that will be displayed when you link to that database from another database.

In our examples, the Main Column in the Person database is the person’s name, and that’s why the person’s name is shown in any databases that link to the Person database.

If the value for the Main Column on a linked entry is blank – for example when a person has been added but their name is blank - Kahootz will display the Entry ID instead.

You can change the Main Column for a database at any time.  Only “Text”, “Long Text”, “Numeric”, “Email” and “Entry ID” columns can be the Main Column.   If a database does not have a Main Column defined, Kahootz will simply display the Entry ID for entries that you link to.

One other point to note is that you cannot use a Locked Column as the Main Column.

ACCESS CONTROL

There are some important points to note about how Linked Database and access control work together.

When a user views a database with a Linked Database Column:

• They will be able to see the Main Column values for entries in the linked database, even if they do not have permission to view the linked database or its entries
• They will not be able to view more details about linked entries that they do not have permission to view

When a user adds or modifies an entry in a database that has a Linked Database Column:

• They can only link to entries in the linked database that they have permission to view
• If the database entry already has linked entries that the user does not have permission to view, they will only be able to see the value for the Main Column

When you add a Linked Database Column:

• You need View permission on the database you want to link to
• You need Modify permission on the database you want to link to in order to add a reciprocal column
• You can only link to a database in the same workspace

Additional Notes

You need to have a Kahootz Enterprise License to use the Linked Databases feature.

There are some additional points to note about Linked Database Columns:

• Once you have created a Linked Database Column, you cannot change which database or column it links to
• You cannot modify Linked Database Columns on the “Modify Multiple Entries” page
• You cannot use Linked Database columns in calculated columns
• You cannot import data into a Linked Database Column.  You can only modify Linked Database Columns on the add and modify single entry pages
• You cannot create a database that contains a Linked Database Column by copying an existing database or from a workspace template

We will change these in a future update to the service.