Knack uses Tables to store your data. In this article, you will find general information on using tables within the Builder.
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. Select a table to access the table's fields, records, and tasks.
What Should My Tables Be?
For most apps, figuring out what the tables are is fairly straightforward. However, for cases when it’s less clear, here are some helpful 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, and then group them into the most logical collections.
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.
Creating Tables
Create a table by clicking the green plus sign button at the top of the toolbox on the left side of the Builder:
Selecting this will bring you to a page with the choice of adding your table with three different options:
- By Import: Enables you to first enter in your table name, and then import your data from .CSV, .XLS, or .XLSX files.
- By Template: Allows you to select a template to populate a new table from a sample table provided.
- From Scratch: Enables you to input a new table name and then directs you back to the Builder to begin adding your fields. By default, a Short Text field is automatically created.
Create a Table From Scratch
This tab lets you create your table from scratch, and 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 correct version when needed.
After creating your new table, you can now start adding fields to the table. A default Short Text "Name" field (e.g. "Projects Name") is created automatically.
Create a Table by Import
If you are starting with an existing spreadsheet, select the "By Import" 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.
To learn more about importing spreadsheets, see this article here.
Create a Table by Template
Select the “By Template” tab to select from several 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 created.
Editing Your Table's Settings
To edit a table, select the dropdown arrow button 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 the table.
Table Name: The name of the table used throughout the Builder.
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, the display field will be the first field in the table.
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
A table and its fields can be copied by selecting the dropdown arrow icon next to the table's name, and then 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 a 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.
Connecting Tables Together
The right column lists any connections the table has and a green plus sign button to create 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:
- Displaying parent-child records in your app’s pages
- Running formulas to find the totals and averages of child records
- Showing records connected to the logged-in user
Click here to learn more about what is possible using connections.
Table's Records
You can access the Records view of your table by selecting either the Records button on the left side menu or selecting the Records view from the top. This will show you all the records stored in the table.
Click here for more on managing records.
Deleting Tables
You can delete an table by selecting the dropdown arrow icon next to the table's name and selecting the "Delete" option:
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.
You'll be asked to confirm this action: