Table of Contents

Working With Tables

Lesley Updated by Lesley

About Tables

Knack uses tables and fields to define your data.

Tables are used to separate your data into common groups. You can think of an table like a spreadsheet or a database table.

Fields are used to define specific attributes of an table. Think of a field as a spreadsheet column. You'll want to add a field for each attribute you want to store for a given table.

You access your tables by clicking on the "Data" link in the top left of your Builder:

Your app’s tables are listed in the Data section of the Builder on the left. Click on a table to access the table's settings.

What Should My Tables Be?

For most apps, figuring out what the tables are is fairly straightforward. But for cases when it’s less clear, here are some good ways to think about your tables:

  • Consider your fields. It can help by starting at the bottom. List all the fields your app will need to display and manage, then group them into the most logical collections. These collections are your tables.
  • Think about your pages. If you're adding forms, what inputs will those forms contain? If you want to display a table of records, what will the columns be? Ask yourself if each of the inputs or columns sound like they belong to the same group. If not, you will want to store them in separate tables.
  • Singular or plural? If you have data that can be plural combined with data that is singular, then you may want to break up the plural aspects into a separate table. For example, let's say you want to track job applications (single), but each application can have a job history of previous jobs (plural). In this case, it makes sense to make the previous jobs a separate table that would connect to the application table.
  • Do you have repeating data? If you are repeating the same information over and over again in your records, you may want to separate that info into its own table and connect it to your original records instead. This way you will only have to update it once.

Add Tables

Add a table by clicking the green plus button at the top of the toolbox on the left side of the Builder:

This will bring up a pop-up to add your table with tabs for 3 options.

  • From Scratch
  • By Import
  • By Template

Add a Table From Scratch

This tab lets you add your table from scratch:

This means you'll need to add all the table’s fields yourself. This is the most common way to add a table if you are not starting with an existing spreadsheet.

First, give your table a name. We recommend you use the plural version of your table name (e.g. Companies instead of Company). Knack automatically creates a singular version and will use the right version when needed.

Click the "Add Table" button and your new table will be saved and added. You can now start adding fields to this new table. A default "Name" field (e.g. Company Name) is added automatically.

Add a Table by Import

If you are starting with an existing spreadsheet, click the "Import a Spreadsheet" tab:

Knack will create a field for each column in the spreadsheet (or each column you choose to add) and import each record into the table.

Click here to learn more about importing spreadsheets.

Add a Table by Template

Click on the “By Template” tab to select from common table templates:

All the fields listed for each template will be added to your new table. You're free to update, delete or add new fields after the table is added.

Edit Tables

To edit an table select the “more options” menu (the “...” icon) next to the table's name. Then select the "Settings" option. Here you will be able to edit the name, display field, and default sort order of that table.

Rename Table: The name of the table used throughout the Builder.

Set Display Field: The display field is the field that represents this table. This field is used throughout the application when these records are displayed in connection fields for this table. By default, this will be the first field in the Table.

Make sure the identifying field is something that is easily recognizable, like a name field. Auto Increment fields and ID fields are not usually good options since you are less likely to know a record by its numerical code.

Set Default Sort Order: The default sort order is used to order the records when no other sort order is given. This defaults to the order the record was added, but you can set it to sort by any field.

Table sorting preferences do not apply to records displayed in a view in any manner. Sorting on a table only applies to record sorting in the Builder and for drop-downs used with this table in a connection field. 

Copy Tables/Fields

An table and its fields can be copied by clicking on the more options menu (the “...” icon) next to the table’s name and selecting the "Copy" option:

You'll be given the option to copy the fields to either a new or existing table.

If you choose a new table, you'll be asked to name the new table. If you choose an existing table, you will be asked to select which existing table. Then you can select which fields to copy:

When copying a User Role Table to another existing User Role table, be sure to NOT copy the five user required fields: Name, Email, Password, User Status and User Roles. If you do, you will find that you are unable to delete the duplicate fields since they are required. 
Records on an table are not copied. If the original records are needed on the copied table, this will require exporting the records from the original table into the copied table.

Connect Tables Together

The right column lists any connections the table has and a green plus button to add a new connection:

When you add a new connection it creates a special connection field in that table.

These fields can be used like any other field throughout your app. If you add a connection field to a form, it will operate like a "look-up," where you can select a record to connect to from the connected table.

Connections open up powerful functionality, such as:

Click here to learn more about what is possible using connections.

Table Records

Above the list of fields in the Data section, you can select the database icon which will show you all the records stored in this table. This can also be accessed for all tables on the “Records” tab from the left-hand menu.

Click here for more on managing records.

Delete Tables

You can delete an table by clicking on the more options  (the "..." icon) next to the table's name and selecting the Delete option:

You'll be asked to confirm this action.

Deleting a table is destructive. Deleting a table will remove that table entirely, including any records and wherever it may be used in your live application. If you have any views displaying records from that table, those views will be removed as well.

How did we do?

Plan Your Connections: Complex Example

About Connections