Table of Contents

Special Tables

Lesley Updated by Lesley

In the following sections, we’ll cover common workflows and functionality you will need to consider as you group your data into tables.

The special tables covered in this guide are:

  • Category Tables: the records in this table represent categories used in other tables
  • Attachment Tables: attach multiple files to a project or assign multiple contacts to a company
  • Connector Tables: associate different tables with contextual data, like the quantity of a product in an order’s line item
  • Movement Tables: track the movement or transfer of people or goods, like employees being transferred to a different office
  • Transitional Tables: these tables are not static, as an order moves through the ordering workflow it shifts from a quote to an order

This is a companion article to our How to Plan Your Tables guide.

Category Tables

Category tables will allow you to create a more dynamic “multiple choice” type field in one table by connecting it to another table. The records from that connected table become your dropdown options. When you add the connection field, the records of the category table appear as the choices.

Category tables are used when you want to:

  • Create dynamic dropdowns in forms (ex. when preparing a construction estimate, narrow down the product selection by first selecting a product type - lumber, fasteners, paint, etc)
  • “Drill down” into different subsets of data (ex. Students can click on different programs to see the classes offered)
  • Use the same set of options across multiple tables (ex. the department category is used for both employees and managers user roles)
  • Give users the option to edit the categories (Live App users cannot edit multiple choice field options, but they would be able to edit category options. Ex. when you want your admin to be able to add new product categories)
  • Perform exportable calculations on the categories (ex. calculate the number of orders at each stage - quote, in production, delivered, invoice)
  • Enable group-based access levels or multi-tenancy (ex. location managers can only see and edit the inventory at their location)

Our Product Catalog demo app is a good example of when you’ll need a category table to allow your users to “drill down” through the categories in the Live App.

On the first page, your user can view the categories of products. In this case, it’s pet products.Then, your user selects a category to see the products in that category. If you select the Toys category, you will see our toy products - a chew toy, a rope toy, a frisbee, and so on.

In Knack, these categories are split into their own separate table.  

In the Product Catalog demo app, we have tables for Products and Categories. The Category table has only one short text field: Category Name. Each category record represents a category, so in this app our category records are: Toys, Grooming, Food & Treats, etc.

When your Products table connects to your Category table, you’ll have a connection field from which you can select a category for each product in your catalog.

The connection field functions just like a multiple choice drop-down. Whenever you add or edit a product record, you can select a category from the drop down. That is what controls which products show up when your user selects a category in your Live App.

Learn More:

Attachment Tables

There are some scenarios where you want to give your users flexibility in how much data they can append to a given record.

For example, our Real Estate Listings demo app has the flexibility to accommodate a custom number of photos for each Listing. One Listing may only have one or two photos. Another may have 20 photos.

You don’t want to add 1, or 2, or 20 image fields to your Listings table. If you do that, some Listings will have many empty fields and others may not have enough fields for all their images.

Instead, you create a separate table called Images and connect it to your Listings table, allowing you to have only as many photos as you need per product.

Some other examples of when you may want to add one, or multiple, data types to a record:

  • A job applicant has many previous jobs
  • A project has many files
  • A company has many contacts

In these scenarios, we need two tables:

  • Applicants and Employment History
  • Projects and Files
  • Companies and Contacts

You can perform calculations on aggregate data using attachment tables. For example, use a count formula in the Real Estate Listings table to count up the number of photos for each listing.

Learn More:

Connector Tables

Some tables exist to connect two different types of data while providing additional contextual data.

For example, in an ordering app you add products to an order. If you connect Products and Orders directly, you have no way to specify how much of each product you want. So we will add another table in between - Line Items. With that table, you gain a lot of flexibility. You can:

  • Specify the quantity of each product that you are adding to each order
  • Calculate subtotals for each product that you add to an order
  • Perform order-level calculations such as order total and number of products per order

You can see what it looks like to use connector tables in our Quotes and Invoices demo app:

Our Warehouse Manager demo app also shows a good example of a connector table. In this app, we have tables for Warehouses and Products. We want to know how many products exist at each location. However, that data doesn’t actually identify either Warehouses or Products. Remember - each table only contains identifying data. A location is not defined by the number of products stored there, and a single product type can exist in multiple locations.

We need an table that tells us how much of each product exists at each location. Here enters Warehouse Inventory. This table connects Locations and Products by telling us how much of a given product is at a given location at the current time.

Warehouse Inventory tells us exactly how much of each Product exists in each Warehouse.

It also allows us to store additional data relevant to that relationship, such as the Product’s exact location within the Warehouse . How it does that is via a movement table - Stock Transfers. See the next section to learn more.

Learn More:

Movement Tables

Some tables represent the movement of something, like products between warehouses. In this scenario, not only do we want to track the current location of the product, but we want to keep a history of that products’s movement.

Movement tables are similar to connector tables, in that you can track additional data like the product and quantity. The difference is that movement tables have an origin and a destination.

For example, your company owns multiple warehouses and you want to track the movement of your products through different warehouses. In this case, you will need a Stock Transfer table. This table allows us to track the key data points of the movement:

  • Origin
  • Destination
  • The product being moved
  • The quantity of the product being moved

By using this table to track movement, we can use formulas and equations to calculate the current stock of each location in real-time. We would not be able to do this kind of tracking without a movement table.

Other examples of movement:

  • An employee being transferred to another team or office
  • A truck being moved from one construction site to another
  • Whether a book or piece of equipment may be in stock

Learn More:

Transitional Tables

Sometimes you have data that may seem like it should be split in different tables, but carrying over connected data becomes a challenge.

For example, in an ordering app you may think of creating tables for Orders and Invoices. But they share a lot of connected data - the Line Items. The Line Items are the products added when a quote is generated. It’s important to associate a Quotes’s line items with the ensuing invoice.

In Knack, there’s no way to automatically transfer a Quotes’s line items to the invoice that follows. So, in this case, it’s easier to use just a single Quotes table here.

This Quotes table has a multiple choice field for status - Quote, Invoice, Paid.

When a new order is created, it is in the quote stage. Line items are added, and you just need to change the stage to invoice when the customer accepts the quote.

Using the data source of your views, you can create different views that display the Quotes records that fall into each of the three stages: Quote, Invoice, and Paid.

Another scenario where you can use a transitional table is with an applicant portal. Let’s say you want prospective students to submit applications with essays or other supporting documentation. You want that supporting documentation to carry over to the student’s account when they are accepted and enrolled.

You can create a Students user role with a status field for their stages - Applicant, Rejected, Accepted, Enrolled.

Learn More:

 

How did we do?

Connecting Records Together

Connections: Troubleshooting & FAQs

Contact