Understanding Tables in Knack

A guide to understanding and managing data tables in your Knack application.

Understanding Tables

Planning Table Structure

How to Create Tables

Table Connections and Relationships

Connection Types

Table Settings

Overview

Tables are the foundation of your Knack application's data structure. This guide will help you understand how to create, manage, and optimize tables for your specific needs. Whether you're building your first app or expanding an existing one, mastering tables is crucial for success.

Understanding Tables

Tables in Knack function as containers for your data, similar to spreadsheets or database tables. Each table represents a distinct category of information in your application.

Key Concepts:

• Tables: Groups of related data (like "Customers" or "Orders")
• Fields: Individual data points within a table (like "Name" or "Email")
• Records: Individual entries in your table (like a specific customer)

Planning Your Table Structure

Here are some effective approaches to help you determine the right table organization:

• Use plural names for tables (e.g., "Companies" instead of "Company")
• Group related fields together in the same table
• Avoid duplicating data across multiple tables by using linking with connection fields
• Consider how data will be displayed in your Live App

When to Create Separate Tables

Start with Your Data: Begin by identifying pieces of information your app needs to handle. Map out all your fields first, then look for natural groupings among them.

This bottom-up approach often reveals logical table divisions.

Think about your Live App Requirements: Think about the pages and forms you'll create in the Live App. What information needs to be collected or displayed? The input fields and table columns can give you strong hints about how to organize your data.

If certain fields seem to tell a different story, they likely belong in their own table.

Look for Relationships: When some aspects of your data can have multiple entries while others are singular, that's usually a sign to split them into separate tables.

Take a job application system – while each application is a single entity, an applicant's work history contains multiple entries. In this case, previous jobs should live in their own table, linked to the main application.

Identify Redundant Information: If you find yourself storing the same data multiple times across records, consider breaking that information into its own table.

💡 By creating a separate table and linking to it, you only need to maintain that data in one place. This approach makes updates more manageable.

Table Creation

To create a new table, select the Add Table button from the left panel of the DataTable. A modal with several choices will appear next.

Method 1: Creating from Scratch

1. Select the Add Table button from the left panel of the DataTable
2. Select Blank Table
3. Enter the table name
4. Add and configure fields as needed

Method 2: Importing a Spreadsheet

1. Select the Add Table button from the left panel of the DataTable

2. Select "By Import"

3. Choose your file (.CSV, .XLS, or .XLSX)

4. Map the columns in the spreadsheet to new field types in the table

Method 3: Importing from a Google Sheet

1. Select the Add Table button from the left panel of the DataTable

2. Select Google Sheets 

3. Enter the Google credentials that have access to your Google Sheet

4. Select the Google Sheet to import

5. Map the columns in the spreadsheet to new field types in the table

Method 4: Start with a Pre-made Table

1. Select the Add Table button from the left panel of the DataTable 

2. Select Pre-made Table

3. Choose one or more templates

4. Select Create Tables. Add, remove, and customize fields as needed in the tables.

 

Understanding Table Connections & Relationships

ℹ️Learn more about Connections and Relationships --> here

With Knack, relationships created from Connection Fields can tie your records together. It’s those relationships that give your data meaning and make it useful. Those relationships unlock powerful features that can tell the true story of your data.

For example, with connections you can:

•  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 log in.

•  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.

💡Before creating connections, it is helpful to understand relationships first.

Types of Relationships

Parent Tables (also referred to as Source Tables)

• Contain primary records
• Referenced by other tables with Connection Fields
• Example: A Products table in a Retail Store app

In the example below, the Products table acts as a Parent table that other tables can reference through Connection Fields.

When tables like Sales and Inventory Alerts include Connection Fields linked to Products, they can access and display data from the Products table.

Think of it like the Products table is the source of truth, with Sales and Inventory Alerts "reaching in", with an Incoming Connection to grab the product information they need.

Child Tables (also referred to as Target Tables)

• Connect to parent tables via a connection field
• Contain related records
• Example: Sales table connected to Products

When we look at the Sales table, it contains a Connection Field that links out to the Products table.

Think of the Sales table as reaching outward to fetch data from Products - that's why we call it an outgoing connection. The Sales table is the "child" because it depends on and references data from the "parent" Products table.

This means each sales record can automatically pull in relevant product details without duplicating that information across tables. When data in the Product Info field is updated, that information updates in the Sales table too.

Multi-Level Relationships

Often, connections are created between tables like a chain, where information flows down from the top. Here's an example:

Products → Sales → Suppliers

In this setup, Products sits at the top, Sales in the middle, and Suppliers at the bottom. When you add a connection field to Suppliers that links to Sales, you can access both Sales data and Products data (since Sales is already connected to Products).

It's similar to a family tree - information flows from grandparent (Products), through parent (Sales), to grandchild (Suppliers). This lets you display or use data from any level above in your chain when working with Supplier records.

In the Live App, this structure is helpful when you need to:

• Show Product details on a Supplier page
• Filter Suppliers based on Product categories
• Display combined data from all three tables in a single view

Here's how connections reveal their true power in Knack's Live App pages, using our Products, Sales, and Suppliers example:

When Building Your DataTable: When you first set up your tables, you create a few simple connection fields - like linking Sales to the Products table, and Suppliers to Sales. Connection fields appear as just one field in your DataTable.

The Live App is where connections are powerful. When you're building pages and adding elements, that single connection field opens up access to ALL the fields from both Sales and Products. For example:

Let's say you're creating a Supplier Details page:

• You started with just a connection to Sales
• And now when adding elements (like forms), you can display any Sales information you want: sale dates, quantities, prices, customer details
• And since Sales connects to Products, you can also show product names, categories, descriptions, inventory levels
• You're not limited to just one piece of information - that single connection lets you tap into everything in both connected tables

It's like having a master key that unlocks all the data in your connected tables. While the initial connection is simple to set up, its real power comes alive when building your pages. You can:

• Choose which Sales and Products fields to display
• Create filters based on connected data (like showing only Suppliers for active Products)
• Sort using information from connected tables
• Show or hide elements based on connected values

Connection Types

One-to-Many

In a One-to-Many connection between Products and Sales, each Sales record connects to exactly one Product, and a single Product can be referenced by many different Sales records.

1. In the Sales table, add a Connection field that connects to Products
2. You leave it as a one-to-many type of connection
3. In the Sales table, you're able to select one Product from the connection field

Then, when building Live App pages:,

• You can show all Sales related to a Product 
• You can filter, search, and sort based on that connection

Many-to-Many

In a Many-to-Many connection, using a Students and Courses example, an individual Student may be enrolled in multiple Courses. 

And, in a Course record, you can see all the Students connected to it

Then, when building Live App pages:

• You can show all Courses a Student is enrolled in
• You can show all Students enrolled in a Course
• You can filter, search, and sort based on these connections

 

Table Settings

Customizing table settings in the Builder allows you to control how your data is displayed and organized. This guide covers key settings and provides tips to ensure your tables are optimized for usability.

Accessing Table Settings

To access table settings:

From the Tables side panel, click on the ellipses and select Table Settings

In the Table, click on the ellipses next to the Table Name and select Table Settings

Key Table Settings

1. Table Name

The table name is used to identify your data table throughout the Builder.

• Why it matters: Clear and concise names make it easier for you and other builders to locate and work with the table.
• How to update: Click on the name in the settings menu and edit as needed.

2. Display Field

The display field is used to identify individual records. If another table connects to this table, values from this field are displayed in that connected table.

• By default, the display field is set to the first field listed in the table's Fields view.
• Change the display field that reflects the primary identifier for your records to streamline record selection if needed. 
  💡 Learn how to change the Display Field 
  💡 Learn more about Display Fields