Connection Fields

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 come 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 come 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 have come secondary?	Jobs are more likely to come 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.

Creating a Connection Field

Now that you have determined which data table is the child data table within which you will add a Connection field, let's demonstrate how to actually add a Connection field within your selected data table. 

To add a new connection field, click on the Connection field on the Add Field menu within your selected child data table.

When creating a new Connection field, there are several settings you can customize such as those highlighted in the image below.

Select the connected table

This is where you can select the parent data table you would like this child data table to connect to. You can select the parent data table to connect to via a drop-down list. 

One-to-One vs. One-to-Many

Select a connection type of one-to-one or one-to-many. Selecting the connection type will enable your users to either select one or multiple records when making this connection.

For instance, does one contact connect to one company or does one contact connect to many companies? 

If you choose a one-to-one connection type, users will only be able to select one company for each contact as illustrated in the image below.



If you choose a one-to-many connection type, users will be able to select multiple companies for each contact as illustrated in the image below.

While you are prompted to choose a connection type of one-to-one or one-to-many for the child to parent data table (One contact connects to one company or One contact connects to many companies), Tadabase does not prompt you to select a connection type for the parent to child data table table (you are not prompted to choose if one company connects to one contact of if one company connects to many contacts). This is because Tadabase automatically manages the parent to child relationship for you and does not need to know if the parent-child relationship type is one-to-one or one-to-many. While the Tadabase platform does not need to be informed whether one company connects to one contact or to many contacts, it does need to be informed whether one contact connects to one company or to many companies. 

Choosing a Display Value

Select the field you want users to use when connecting records using this connection field. 

For instance, when adding a connection field within a Contacts data table to connect to a Companies data table, you can select which field within the Companies data table you would like to represent this connection when users connect contact records to company records. If you select Company Name as the display field, users will see company names when making this connection. 

Display fields are used for representation only. Connected records are stored using the record ID, not the display field. Therefore, you can change the display field at any time, and changing the display field will not affect the connected records.

1. Name-Select the field name you wish to use to reference this connection field.
   
   

   The names for each field must be unique within each data table. Meaning, there can not be two fields within the same data table that have the same field name.  

    

2. Description- this is where you can optionally describe this connection field for your own reference. 

Managing connections

Once you add connection fields to establish connections between data tables, you may need to periodically remind yourself of which data tables connect to one another and how they connect.

Tadabase provides two ways to view your connections- one on the data table level and one on the app level.

You can view the incoming and outgoing connections to/from each data table by clicking on the diagram icon located within each data table as illustrated below. For instance, the diagram icon of the data table in the image below displays a connection diagram of the Contacts data table connecting to the Companies data table via the Company field.

Additionally, you can view the full diagram of all your app's current connections by going to Settings > Support > Table Connection Graph. There, you will be able to view each connection and the field used to connect one data table to the other.

To change or delete connections, you can do so within the connection field of the selected data table. 

Related vs. Connected Records

Connected records can extend one step further to related records. Related records are one step removed from connected records in that the records are not directly connected to one another but rather related to one another. 

For instance, let's take the above Contact Directory example and add one more data table of Notes. Say you have a Companies data table, a Contacts data table with a connection field to the Companies data table (each contact belongs to a company), and a Notes data table with a connection field to the Contacts data table (each note belongs to a contact). Although the Companies data table and the Notes data table are not connected, they are related via the Contacts data table. This is illustrated via the Contact Directory app's table connection graph:

Although Companies and Notes are not connected, Notes are connected to Contacts which are connected to Companies, making Companies and Notes related. Like connected records, related records can be automated with record rules, summarized in related data tables, and viewed within the details page of a related record. For instance, within the details page of a specific company you can view all your contacts connected to this company as well as all the notes related to this company's contacts. 

 

Let’s explore the difference between connected and related records using one more example.

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 Job Notes data table with a connection field to the Jobs data table (each note belongs to a job).

In this example, Job Notes are not connected to Customers but they are connected to Jobs which are connected to Customers. Therefore, the records within the Job Notes data table are related to the records within the Customers data table. 

Below is a diagram illustrating the relationship of Job Notes being related to Customers via Jobs:

Similar to the example above, related Customer and Job Notes records can be automated, summarized, and viewed on one details page. For instance, within the details page of a Customer you can view all the jobs connected to this customer as well as all the job notes related to this customer's jobs as illustrated in the image below.