Connections (LEGACY DOC) DO NOT TOUCH

What are Connections?

Connections are a way of connecting records within one table to records in other tables using a Connection field.

Why use connections

Connecting records and tables is primarily the reason for using a database vs traditional spreadsheets. There are many benefits as to why you’d use a connection. Let’s first understand what a connection is using an example.

Suppose we want to track our customers and their jobs. Without a database your data might look something like this:

As you can see in the above table, we have 3 jobs for the same customer- “John Simonds.” Each record has the name, phone and email address of the customer.

If we need to update John’s info we would need to edit each record to the updated customer's details. This is a basic elementary example of how connections come in handy.

The better approach here would be to have 2 tables: Jobs and Customers.

When we add a new job, we would have a drop-down of all the records in the customers table and can then display any values from the connected table inside the jobs table.

Customers table:

Jobs Table:

Once we have connections established between 2 or more tables many new functions are opened including:

• Ability to summarize data from connected tables. In our example, from within the Customers table we can see the total jobs each customer has or even the total jobs by status (open, in-progress and done).
• Ability to lookup values from one table to another. Easily, display data in the jobs table from any other record of the customers table. This can be done in forms or any data component. See Connected Values section.

Connected records also work closely with pages. For example, when viewing the details of a customer we can choose to add all the jobs that are directly connected to the customer.

Connected records can go even further and show records that are related - which is one step removed from connected.

Let’s explore the difference between connected and related tables.

Let’s say we have 3 tables:

1. Customers

2. Jobs

3. Job Notes

Each job belongs to a customer, and each job note belongs to a job.

It's important to note : Job Notes are not connected to Customers but are connected to jobs which are connected to customers.

Therefore in this example:

• Jobs are connected to customers

• Job Notes are connected to Jobs

• Job notes in this case are related to customers. In the details of customers we can even see the notes of the customer.

Here’s a diagram to demonstrate the relationship:

Adding connections

Adding connections is as simple as adding a Connection field in the data builder.

After choosing to add a connection you'll need to set several options: 

• Table Name
• Relationship type
• Display Field
• Name
• Description

Table Name

Choose from a drop-down which table this table should connect to. 

Connection Type

Choose if this is one-to-one or one-to-many. The primary difference here is do you want to allow your users to choose more than one record when making a connection. In the image above you can see that we're saying "One Job to One Company." 

The Tadabase platform will automatically manage the relationship for you and therefore doesn't need to know if the relationship the other way (Companies to Jobs) is one-to-one or one-to-many. 

Whether each company has One Job or Many jobs is not relevant, the important things to note is if each job can be assigned to many companies. 

Display Field

This will be the field used when making the connection. 

The connection is and kept behind the scenes using the record ID, not the display field. Therefore, the display field can always be changed. This field is for your representation only. You can choose a unique ID type field or any filed of your choosing. 

Name

Name is the what will be used as the title when adding this field and in the builder to reference this field. 

Name field must be unique in each table. Meaning each table can only have one field with the same name. 

See more details about adding fields here.

Managing connections

To manage the relationships between connections do so in the fields or records tab of the data builder by editing the connection field.

Inside of each table you can see a diagram of all the incoming and outgoing connection: 

You can view the full diagram of all your current connections by going to Settings > Support > Table connection diagram. You’ll be able to see each connection and the field used to connect from one table to the other.

Planning your connections

When you have more than one table and need to start creating connections determining connection structures can be key to streamlining your building process.

Once you have your tables and want to connect data, it’s helpful to think determine the following:

• Which record belongs to which record (parent/child)

• What’s more likely to stay constant vs change

• What likely comes first

Let’s look at an example: 

Connection Example 1

Objective: You’d like to manage your customers and their jobs. You have many customers and many jobs and each of the jobs are assigned to one or more customers.

We’ll have 2 tables: Customers and Jobs.

In this case we want to make sure we can track the jobs and customers as well as the relationship between them.

In both questions above the answer is Jobs. (9 out 10 times just answering what belongs to what would give you the proper answer.) Here it would mean the connection field should originate from Jobs and connect to Customers (destination).

Practically this means we would first add our customers, then, when we add a job we’ll have a drop-down of all the customers this job will belong to.

In the event when adding a job the customer is not present, you can set an option in the form to allow adding new customers on the fly. Click here to learn more.