Getting Started with Connections

What are connection fields?

Connection fields connect records in one data table with records in another data table.

When we say that records are connected, or that there is a connection between two data tables, we are referring to the connection made between records in one data table to records within another data table using a Connection field. 

Note: Adding a Connection field establishes a connection between two data tables, however, it does not connect the actual records together. Think of a Connection field as a phone line. When you create a phone line, you have established a method of connection between two people, but you haven't actually connected those two people, you have simply created the ability for them to connect. Similarly, a connection field creates the ability for two data tables to connect, but you must then do the work of actually connecting the records together.

Why use connection fields?

The ability to connect records in one data table to records in another data table reflects the core power of database applications and is the primary reason why businesses seek database applications over traditional spreadsheets. 

While Tadabase does an excellent job of storing your data, your data can't do much good just sitting there in storage. Connections are what empower you to take your data to the next level and what supercharges your data for faster and easier summarizing, tracking, calculating, reporting, and automation of all your business information.

Let's explore some of the key benefits of using Connection fields to connect your records.

To automate and expedite adding new information

Using Connection fields to connect records saves a significant amount of time when adding new information.

For instance, if you would like to track all your contacts and the companies they work for, you can create a Contacts data table and a Companies data table and use a Connection field to connect the records within both data tables. Doing so would enable you to save a significant amount of time when adding new information because every time you add a new contact you can simply select the company that the new contact works for while simultaneously viewing all that company's information.

With a traditional spreadsheet, you would have to re-enter the company's information from scratch every time you add a new contact.

Using Connection fields to connect records will save you invaluable time when adding new data and it can also reduce errors that stem from repeated manual data re-entry.

To automate and expedite editing existing information

Using connection fields to connect records can also save a significant amount of time when editing existing information.

For instance, if you are tracking your Customers and Jobs with a traditional spreadsheet, this is how your Jobs spreadsheet may appear: 

As you can see in the image above, there are three jobs, each with the same customer, "John Simonds". All three job records include the Customer Name, Phone, and Email fields to display the customer's information. Say you need to edit Customer John Simond's name, you would need to edit his name throughout every job record that includes this customer's name field. This can quickly become tedious and repetitive and incur needless, manual errors.

With database applications and the use of Connection fields, repetitive updating of individual fields becomes obsolete as you are able to update a field once, which will automatically update any and all connected records that include that field. 

To calculate and summarize information from a related data table

Using Connection fields to connect records empowers you to calculate, summarize, and track related information from one single viewpoint at a glance.

For instance, say you have an Employee Time Tracking application with an Employees data table, a Time Sheet data table, and a Connection field connecting these two data tables with each Timesheet belonging to one Employee. This Connection field will empower you to track, calculate, and summarize information from one data table within another. For instance, from the Employees data table, you can view values from the Time Sheet data table including each employee's total hours, an average number of hours, and total weekly hours. 

You can use the Connection field to create any complex formula fields you need and summarize additional calculations such as employees' hours across custom pay periods and date ranges. 

Additionally, you can use the Connection field to automate critical calculations of your connected and related data which can provide you with invaluable analytics and reporting functionality across all your values.

To summarize connected and related records on one details page

Using connection fields to connect records also enables you to track connected and related records on one details page.

For instance, say you have a Customers data table, a Jobs data table with a Connection field to the Customers data table (each job belongs to a Customer), and a Notes data table with a Connection field to the Jobs data table (each note belongs to a Job). Although the Notes data table is not connected to the Customers data table, it is related to the Customers data table via the Jobs data table.

Within a customer's details page, you can view the customer's connected jobs and related notes so that you are viewing all the customer's connected and related information on one page.

Click here to learn more about related records vs. connected records.

To automate changes in connected or related records

Using Connection fields to connect records also empowers you to automate changes in connected and related records, such as automatically inserting or updating connected and/or related records when forms are submitted. 

For instance, say you have open job positions, and every time you hire a new employee and assign him/her to a position you can automatically update the position status from "open" to "filled". You can easily accomplish this with a connection field which will empower you to then create a record rule to automate this record update.

Click here to learn more about creating record rules to automate changes in connected or related records.

To import connected records.

When importing connected records into a Child table with a connection to a Parent, you can use any unique value to establish the connection. For example, let's say we have two tables.

1. Companies (Parent)
2. Jobs (Children)

Each Job connects to a Company, and let's say the Company has a unique identifier called "Company Code." When importing Jobs, you can set the Company column to the Company Code of the Company and select that connected field in your import template. Once the connection is made, a unique Tadabase Record ID connects that Job to the Company, giving you access to any Company field in the Job record.

You only need one connection from the Child to the Parent table to import and match any field in the Parent. While you can match any field in the Parent, it's important to ensure you use a field where each record value is unique. Otherwise, if there are duplicate Parent values, it will connect the Child to the first found Parent record that contains that value in your database.

Planning your connections

Before adding a Connection field, it is best to first think about within which data table you plan on adding the Connection field.

For instance, say you have a Contact Directory application with a Contacts data table to track your contacts and a Companies data table to track the companies each of your contacts work for. You would now like to add your first Connection field to begin tapping into the potential and power of your database application. When adding your first Connection field, you may wonder- should you add the Connection field within the Contacts data table or the Companies data table? 

Understanding a basic premise of how Tadabase connections work will help you make this decision as well as intuitively plan all your future Connections fields. Tadabase was designed to maximize connections made within the child data table belonging to the parent data table. Therefore, as a general rule, Connection fields should be added within the child data table that belongs to the parent data table. 

Now that you know that Connection fields should be added within the child data table to connect to the parent data table, there are three questions you can consider to help you determine which data table is the child data table:

1. Which data table belongs to the other data table?
   
2. Which data table is more likely to change?
3. Which data table is more likely to have come secondary?

The data table that is the response to these questions will generally be the child data table and the data table within which you should add the Connection field to connect with the parent data table.

Let's explore how each of these three questions helps to determine which data table is the child data table. 

1. Which data table belongs to the other data table? The data table that belongs to the other data table is the child data table, similarly to how a child belongs to a parent. As a general rule, Connection fields should be added within the child data table that belongs to the parent data table.
   
   
2. Which data table is more likely to change? The data table that is more likely to change is generally the child data table. The data table that is more likely to remain constant is generally the parent data table. As a general rule, Connection fields should be added within the data table that is more likely to change (the child data table), rather than within the data table that is more likely to remain constant (the parent data table). 
   
   
3. Which data table is more likely to have be secondary? The data table that is more likely to have come secondary is the child data table, as opposed to the data table that is more likely to have come first which is the parent data table. This is similar to how a child comes secondary to a parent, as in you would not have a child without first having the parent. As a general rule, Connection fields should be added within the data table that is more likely to have come secondary (child data table) rather than within the data table that is more likely to have come first (parent data table).

Now, let's apply these three questions to the above Contact Directory example to determine which data table is the child data table in which you should add your Connection field.

1. Which data table belongs to the other data table? Each contact belongs to a company; therefore the Contacts data table is the child data table. (For instance, Contact John Smith works for Google. Google does not work for Contact John Smith).
   
   
2. Which data table is more likely to change? While contacts are more likely to come and go within a company, companies are more likely to remain constant. The Contacts data table is more likely to change and is, therefore, the child data table.
   
   
3. Which data table is more likely to have be secondary? You would not have a contact working for a company without first having a company to work for. The Contacts data table is more likely to have come secondary and is, therefore, the child data table.

As the answer to each question is the Contacts data table, this data table is the child data table and therefore where the Connection field should be added.

Let's explore one more example. 

Say you have a Customers data table to track your customers and a Jobs data table to track each customer's jobs. You would like to add a Connection field to connect the records within these two data tables, but which data table will you add the Connection field to?

As you know, the Connection field should be added to the child data table, so let's refer back to the three questions above to determine which data table is the child data table:

	Answer	Explanation
Q: Which data table belongs to the other data table?	Each job belongs to a customer.	❌“John belongs to the Website job.” ✔ "The website job belongs to John.” The customer does not belong to the job, rather each job belongs to a customer.
Q: Which data table is more likely to change?	Jobs are more likely to change.	Once a customer is added to your database, you may have to change some contact information once in a while but, generally, customer information is likely to remain the same. On the other hand, job information will frequently change as new jobs are added and completed for each customer.
Q: Which data table is more likely to be secondary?	Jobs are more likely to be secondary to customers.	Generally, you would not have a job without first having a customer to do the job for. Therefore, customers are more likely to come first and jobs second.

The answer to each of these three questions is the Jobs data table. This helps you determine that the Jobs data table is the child data table, and therefore where you should add your connection field to connect to the Customers data table. 

Although it is possible to add a Connection field within the parent data table, doing so will not maximize the connection functionality that could be achieved with a child-to-parent connection. 

Determining which data table is the child and which is the parent will also help you plan which data table is recommended to add first within the Data Builder. Generally, it is recommended to add data tables from parent to child and to begin adding the data table that is most constant (parent-like). For instance, in the example above, it would be recommended to first add the Customers data table and then add the Jobs data table. Adding data tables in this order will enable you to first add your customers, and when adding jobs have the ability to select customers from a drop-down list. 

In the event that data tables were added to the Data Builder in the order of child to parent, such as first adding the Jobs data table and then adding the Customers data table, you can simply enable a form to pop up within your Jobs component on the live app in order to add new customers on the fly from the Jobs component.

 

 

Additional Resources