Table of Contents

Plan Your Connections: Complex Example

Danielle Kellogg Updated by Danielle Kellogg

Scenario

In this article we will walk through the process of planning your connections. We will share tips and best practices along with answering common questions both beginners and Knack pros have about connections.If you’re not sure whether you need to use connections, check out 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 warehouse management app in this example.

We chose a warehouse manager app for this example because it represents a couple complex object and connection concepts:

  • How to represent movement - moving products between multiple locations
  • Multiple connections between the same objects

If you have a simpler app and find this example to be too complicated, see our simple example.

Requirements

If this your first time creating an app, you'll need to know some basics about adding objects, fields, pages, and views. You can start by reading our Builder Basics section

Other good resources can be found in our designing the database and building pages sections of the knowledge base.

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 and how?
  • 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.

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 Objects

Before you can determine how your objects relate to each other, you first need to know what objects you’ll need. If you’re coming from an Excel or Access/SQL background, you can think of an object like a spreadsheet or a database table. This section will be even more helpful if you’re not coming from a database background!

Objects represent common groupings of data. Each object should only represent a single type of data.

Example

Let’s start with planning the objects and user role we’ll need in our Warehouse Manager Demo App.

We want to track a few things with the Warehouse Manager app:

  • Products
  • Where the products are currently located
  • How much of each product is in each location

From that information, we can determine the distinctive types of data we want to track:

  • Products: The products themselves - name, description, price, etc
  • Locations: The locations themselves - address, name, etc
  • Location Inventory: The amount of each particular Product at each particular Location. This data doesn’t directly identify a Product or a Location, so it belongs in it’s own object.
  • Stock Transfers: The movement of Products between Locations.
It can be helpful to write out your objects on a piece of paper with their fields listed. Draw lines between objects to represent relationships - this will help you when you get to planning the connections.

In the graphic above you can see how we’ve split up our different types of data into objects. Each object contains data representing only a single type of data - these are the fields listed under the object name.

You can see that some objects represent static data - such as a product record - where other objects represent movement - such as a stock transfer.

Determine What Connection Types You Need

Once you have your objects, it’s time to determine how they are related. This is done using connections between your objects.

The connection rules that apply to objects also apply to user roles. Think about roles as objects, except they also have login access. So while we will use the word object here, know that the same rules apply to user roles.

Think about your objects in pairs of two. For each pair, think about whether they fit in any of the below scenarios.There are three types of connection relationships in Knack:

  • One-to-many: Each Company connects to many Contacts, but each Contact is connected to only one Company.
  • 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 - they work for the vast majority of relationships.

Example

Let’s consider the relationships in our Warehouse Manager app.The Stock Transfers object relates to two other objects:

  • Products - each Stock Transfers record represents the movement of a single product
  • Locations Inventory - the product moves from one location to another location.

Locations Inventory represents the amount of stock of a single Product at a single Location.

The graphic below shows how you can map out the relationships between the objects.

Products and Stock Transfers

Since Products may move around multiple times, we know they may be associated with multiple Stock Transfers records. However, we know that each Stock Transfers record is associated with the movement of a single Product record. That makes the relationship between Stock Transfers and Products one-to-many - one Product is related to many Stock Transfers.

Stock Transfers and Locations Inventory

The relationship between Stock Transfers and Locations Inventory can be tricky because there are two Locations Inventory records associated with each Stock Transfer record - the origin and the destination.

We don’t connect Stock Transfers directly to Locations because the Locations object only contains data related to the Location itself, not its inventory contents. That’s the purpose of the Locations Inventory object!

You may think this is a many-to-many relationship. However, if you added a many-to-many connection between Stock Transfers and Locations Inventory, you would lose a ton of functionality in your app. Continue reading to see how.

This is where you must think about your app as a whole. The types of workflows, views, and calculations you want to build are important here.

For each Locations Inventory record, we want to:

  • Recognize when a Stock Transfer represents incoming stock versus outgoing stock
  • Use equations to calculate stock levels for each product at each location

Current Stock = Starting Inventory + Incoming Stock - Outgoing Stock

With a many-to-many connection, both the origin and the destination locations are grouped together, making it impossible for our equation to recognize which is which.

In order to perform our stock calculations, we must consider two separate relationships between Stock Transfers and Locations Inventory:

  • Incoming Stock Transfers
  • Outgoing Stock Transfers 

A single Locations Inventory object will have many incoming Stock Transfers and it will also have many outgoing Stock Transfers.

That means the relationship between Stock Transfers and the origin Locations Inventory is one-to-many - one origin Locations Inventory is related to many Stock Transfers.

That means the relationship between Stock Transfers and the destination Locations Inventory is one-to-many - one destination Locations Inventory is related to many Stock Transfers.

Locations Inventory and Products

Remember - Locations Inventory represents the amount of stock of a single Product at a single Location.

That means each Product record will have many Locations Inventory records associated with it - one for each Location.

That means the relationship between Locations Inventory and Products is one-to-many - one Product is related to many Locations Inventories.

Locations Inventory and Locations

The Locations Inventory relationship with Locations is similar to its relationship with Products.

That means each Location record will have many Locations Inventory records associated with it - one for each Product.

That means the relationship between Locations Inventory and Locations is one-to-many - one Location is related to many Locations Inventories.

Don’t get discouraged if this sounds complex to begin. Understanding connections is often the hardest part of learning how to use Knack! If you need help figuring out how to connect your objects together, reach out to us for help at support@knack.com with a list of your objects and a description of what they represent.

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 objects, you do not add the connection to both objects. The connection only needs to be added to one of the objects to create a relationship between your records. Which object 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 object. The parent object is the “one” part of the relationship and the child object 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 object and Contacts is the child object.

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

So here we would add the connection to the Contacts object, select the Companies objects, and use the default option configuration.

Example

Let’s go back to our Warehouse Manager app. We already mapped out our objects 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 object.

Stock Transfers and Products:

  • Stock Transfers represent the movement of Products between locations
  • One Product is related to many Stock Transfers
  • Product is the parent object
  • Stock Transfers is the child object
  • Add the connection to the Stock Transfers object, pointing to the Products object

Stock Transfers and destination Locations Inventory:

  • Stock Transfers represent a product arriving to a location, which is accounted for in Locations Inventory
  • One destination Locations Inventory is related to many Stock Transfers
  • Locations Inventory is the parent object
  • Stock Transfers is the child object
  • Add the connection to the Stock Transfers object, pointing to the Locations Inventory object

Stock Transfers and destination Locations Inventory:

  • Stock Transfers represent a product arriving to a location, which is accounted for in Locations Inventory
  • One destination Locations Inventory is related to many Stock Transfers
  • Locations Inventory is the parent object
  • Stock Transfers is the child object
  • Add the connection to the Stock Transfers object, pointing to the Locations Inventory object

Locations Inventory and Products

  • Locations Inventory represents the current amount of Product at a given Location
  • One Product is related to many Locations Inventories
  • Products is the parent object
  • Locations Inventories is the child object
  • Add the connection to the Locations Inventories object, pointing to the Products object

Locations Inventory and Locations

  • Locations Inventory represents the current amount of Product at a given Location
  • One Location is related to many Locations Inventories
  • Locations is the parent object
  • Locations Inventories is the child object
  • Add the connection to the Locations Inventories object, pointing to the Locations object

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 field to the object 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 object.

You can rename your connection fields to make them more descriptive. The origin connection between Stock Transfers and Locations Inventory can be renamed to Origin Location, with the destination connection being renamed to Destination Location

Add Your Objects

You’ve completed all your planning, so it’s time to add your objects.

Using the green “+” buttons in the Schema section of the Builder to add your objects and user roles.

You can read more on adding and managing your objects here.

Connect Your Objects and Records

There are two stages to connecting your records:

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

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

Connect Your Objects

Navigate to the objects you are adding your connections to in the Schema section of the Builder. On the right-hand Connections section use the green "+" button to add a new connection.

In the Locations Inventory object, add a connection and select your Products object. In the following dialog box, you’ll see the default option is for a one-to-many connection.

After adding your connection, you’ll see the new connection field in your object and the connection represented in the Connections section to the right.

Repeat this process for the rest of your connections. For more detailed instructions on adding connections, see this article.

A connection between two objects is represented as a field in the object the connection is added to. This is called the connection field. From the connection field, you select the record(s) from the connected object you would like to connect the selected record to. You can rename your connection fields to make them more descriptive, by editing that object’s display field in the object 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 connecting the records in your database, see our guide Connecting Records Together.

Notes and Troubleshooting

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

I’m lost - can I get some help planning my connections?

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 need help figuring out how to connect your objects together, reach out to us at support@knack.com with a list of your objects and a description of what they represent.

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

On rare occasions, you may need multiple connection fields between two objects. 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 object, add two connection fields to the Guardian object, 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 object, add three connection fields to the Employees user role, renaming themSubmitter, Approver, and Manager.
  • The Warehouse Manager example shown in this 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 set up. You can hover over the connection in the right sidebar of your object to see the relationship type of each connection originating from or going to that object:

More Troubleshooting Tips

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

How did we do?

Plan Your Objects

Plan Your Connections: Simple Example

Contact