Table of Contents

Plan Your Connections: Simple Example

Lesley Updated by Lesley

Scenario

In this article, we will walk through the process of planning your connections. We will share tips and best practices, along with answer common questions both beginners and Knack pros have about connections.

If you're not sure whether you need to use connections, check out our guide to Connect Related Data. You will find many examples of how connections are used in Knack apps to:

  • View related data: You can create relationships between your data so that when you view information about a company, you can also view information about all of the contacts that are associated with that company as well.
  • Ensure users only have access to their own data: Limit the data that users see on a page so that they only see the data that is relevant to themselves. This way, customers only see the orders they have created once they login.
  • Run calculations and visually summarize your data: Create reports that show the total sales made in a given month and even how much each salesperson contributed to that total.

We will plan out the connections for a customer portal. You can see the finished app here: Customer Portal Demo App.

Requirements

If this is your first time creating an app, you'll need to know some basics about adding tables, fields, pages, and views. You can start by working through the articles in our Builder Basics section. Other good resources can be found in our Design Your Database article and our Working With Pages article.

To plan your connections, you need to understand a few things about the app you want to build:

  • What data will you be tracking?
  • What users will be accessing your app?
  • What workflows do you want to create for your users?
  • What types of reports do you wish to create?

How you set up your connections will determine what is possible in your Live App.

Note: This article is designed to be read completely, from start to finish. To get the most value, we recommend reading the whole article.

Steps

Plan Your Tables And User Roles

Before you can determine how your tables relate to each other, you will first need to know what tables you'll need. If you're coming from an Excel or Access/SQL background, you can think of a table like a spreadsheet or a database table. Not using either of those? That's okay! This section is helpful for all Knack users.

Tables represent common groupings of data. Each table should on represent a single type of data. User Roles are common groupings of data just like tables, but they can login!

For example, in our customer portal app we will be tracking invoices and customers. Data like customer addresses or phone numbers, while it may be important to display on an invoice, does not identify the invoice itself. That data identifies the customer. So, a table for invoices containing order dates and customer addresses no longer represent a single type of data.

The power of connections is that we can keep the data separate, yet still draw on it when we need to. We can create an invoice page in our app that displays not just the invoice’s data, but the relevant data from the connected customers.

Tables vs User Roles

In many apps, you’ll need user roles to allow users to login and access the data. In Knack, user roles are used to control permissions in and access to your app.

So, should we make a customers table or user role?

The answer to this depends on your business. In a business-to-consumer (B2C) scenario, your customers are individuals. In a business-to-business (B2B) scenario, your customers are companies, each with individuals who work there. Ask yourself - what kind of data am I working with? Who needs to login?

In a B2C scenario, the custoemr name, address, phone number, total spend, etc. are all data points that identify an individual - a single type of data. This individual needs to log in to view their orders, update their info, and more. That means we will create a user role for Customers.

In a B2B scenario, we have two types of data. We have the customer name, address, phone number, and total spend. All this data identifies a company. We also have contacts at that company. Each contact has their own identifying info - contact name, email address, phone number, and job title.

In this scenario, we create a table for Customers and a user role for Contacts. Customers represent the company data - companies are not people, so they can't login. Contacts represent the data of the individuals who work at the Customer companies, who do need to login.

If you’re creating an app for internal use only, where your customers would not be logging in, there’s no need for a user role. However, if you think there’s a chance you might open this app to customer access in the future as a portal, it’s easier to make a user role for your customers from the very beginning, as tables cannot be converted into user roles. You don’t have to give them login access or create passwords right away.

Check out this guide for more information on what you can do with users in your apps: Users and Access.

Example

Let's start with planning the tables and user roles we'll need in our Customer Portal Demo App. This app is based on a B2B business relationship where each customer is an individual.

We want to track a few different types of data with the Customer Portal app:

  • Customers
  • Their service requests
  • Their invoices

We also consider what users will be accessing the Live App. In this case, we want:

  • Customers to be able to login to view and edit their own records, but not be able to see anyone else’s records.
  • Managers to be able to access the system to manage the service requests and invoices.
It can be helpful to write out your tables & user roles on a piece of paper with their fields listed. Draw lines between tables to represent relationships - this will help you when you start planning the connections.

In the graphic above, you can see how we've split up our different types of data into tables. Each table contains data representing only a single group of data - these are the fields listed under the table name.

Because we will be connecting these tables, we do not need to store data like the customer's address field on the Services table. The address only belongs in the user roles it identifies - Customers. You can later access that customer's address from their connected Service records. That way, you only store and manage the data in one location.

Determine What Connection Types You Need

Once your have your tables, it's time to determine how they are related. This is done using connections.

The connection rules that apply to tables also apply to user roles. So, while we will use the word table here, know that the same rules apply to user roles.

There are three types of connection relationships in Knack:

  • One-to-many: Each Contact connects to one Company, but each Company is connected to only many Contacts.
  • Many-to-many: Each Student connects to many Classes, and each Class connects to many Students.
  • One-to-one: Each Manager connects to one Location, and each Location connects to one Manager.

Learn more about connections types here.

One-to-many types are the most commonly used connection type - they work for the vast majority of relationships.

Example

Let’s go back to our Customer Portal app.

The Customers user role relates to two other tables:

  • Services - each Customer submits Services.
  • Invoices - Customers are issued Invoices to pay for completed Services.

The graphic below shows how you can map out the relationships between your tables for this example.

Customers and Services

Customers can log into their portal to submit Service requests, which then move through the company workflow before being marked as complete. Each Service record represents a single instance of a service being provided to a customer. That makes the relationship between Services and Customers one-to-many - one Customer is related to many Services.

Customers and Invoices

Invoices are created by a company to collect payment from a single Customer for one or more Services completed. That makes the relationship between Invoices and Customers one-to-many - one Customer is related to many Invoices.

Determine Where to Add Your Connections

Once you know what type of connection you need, you determine where to add the connection. In this section we will go over best practices for adding each type of connection.

Though there are two tables, you do not add the connection to both tables. The connection only needs to be added to one of the tables to create a relationship between your records. Which table you add that connection to matters.

Adding a One-to-Many Connection

When working with a one-to-many relationship, you have a parent and a child table. The parent table is the "one" part of the relationship, and the child table is the "many".

Let's take the following example: Each Company connects to many Contacts, but each Contact is connected to only one Company.

In this example, Companies is the parent table and Contacts is the child table.

With a one-to-many type of connection, we add the connection to the child table. The reason for this is better workflow options, visual presentation, and data management in your apps. For an example, see the "Which Table To Add Your Connection To" section of this article.

In this example, we add the connection to the Contacts table, select the Companies tables, and use the default one-to-many configuration.

Example

Let's go back to our Customer Portal app. We already mapped out our tables and how they are related.

Since all of the connection types in this app are one-to-many, we determine which is the parent and which is the child of each relationship. Then, we add the connection field to the child.

Customers and Services:

  • One Customer is related to many Services
  • Customers is the parent table
  • Services is the child table
  • Add the connection to the Services table, pointing to the Customers table

Customers and Invoices:

  • One Customer is related to many Invoices
  • Customers is the parent table
  • Invoices is the child table
  • Add the connection to the Invoices table, pointing to Customers table

Adding One-to-One or Many-to-Many Connections

Working with one-to-one or many-to-many connections is more straightforward. You don't have to worry about parents or children. Rather, you add the connection to the table you will be editing more often.

Let's say you have many Students assigned to many Classes. Which one would you rather edit? Do you want to add Classes to each Student from the student's record? If so, add your connection to the Student table.

Add Your Tables and User Roles

You’ve completed all your planning, so it’s time to add your tables and user roles.

Use the green "+" button in the Data section of the Builder to add your tables and user roles.

If you are adding user roles for the first time, you will need to activate them first. For instructions on activating user roles, see this article.

Connect Your Tables and Records

There are two stages to connecting your records:

  1. Create a connection between two tables
  2. Connect individual records via the connection field

This article is focused on step #1 - planning and creating the connection between your tables. Let's now add the connections between our tables and user roles.

Connect Your Tables

Navigate to the tables you are adding your connections to. One the right-hand column of the Data section of your Builder, you'll see a green "+" button.

In the Services table, add a connection and select your Customers user role. On the following popup modal, you'll see the default option is for a one-to-many connection.

Click "Add Connection" to connect Services to Customers. You'll see the connection you added in the right-hand connections column and as a field in your table.

Repeat this process for your Invoices > Customers connection. For more detailed instructions on adding, editing, and removing connections see this article.

A connection between two tables is represented as a field in the table the connection is added to. This is called the connection field. From the connection field, you select the record(s) from the connected table you would like to connect the selected record to. You can rename your connection fields to make them more descriptive, by editing that table’s display field in the table settings. This means that you do not have to manage primary or secondary keys in Knack - we manage them for you behind-the-scenes.

Connect Your Records

There are many ways to connect your individual records, from connecting them during an import to using a form view.

To learn about the different methods of connection the records in your database, see our guide Connecting Records Together.

Notes and Troubleshooting

Using pen and paper to visualize your tables and connections can be very helpful. We encourage you to do your planning outside of Knack before building.

My app is more complicated than this - how do I plan my connections?

Check out our complex example if your app is more complicated than our example here.

Don't get discouraged if you don't get it at first. Understanding connections are often the hardest part of learning how to use Knack! If you still need help figuring out how to connect your tables together, reach out to us at support@knack.com with a list of your tables and a description of what they represent.

What if I need many connection fields between the same two tables?

On rare occassions, you may need multiple connection fields between two tables. Please keep in mind that outside of the limited cases mentioned below, we rarely encourage double connections.

Potential cases include:

  • Livestock: You want to track the mother and father of a horse or other animal. In this case, you can add two connection fields from Animal back to Animal. The first connection field is called Father, the second connection field is called Mother.
  • Students: You want to track two Guardians for each Student. From the Student table, add two connection fields to the Guardian table, renaming them Guardian 1 and Guardian 2.
  • Document or Project Roles: You want to track which user from the Employees user role submits, approves, and manages each Project. From the Project table, add three connection fields to the Employees user role, renaming them Submitter, Approver, and Manager.
  • The Warehouse Manager example listed in our Complex Example article.

If you’re still unsure, please reach out to us at support@knack.com.

How do I see what type of connection I have?

If you are learning from one of our many demo apps, you will want to see how the connections are currently setup. You can hover over the connection in the right sidebar of your table to see the relationship type of each connection originating from or going to that table:

You can also click on the connection field to see the type:

More Troubleshooting Tips

For more notes and troubleshooting tips for connections, see this article.



How did we do?

About Connections

Connection Types

Contact