Working with Tables

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 can access your tables by clicking on the "Data" button in the top left of the Builder:

 

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

  

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

  

• Table Catalog: Allows you to select a pre-built table to your app. You're able to add and change the fields later.

  

 

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, you can 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.

 

Create a Table from the Table Catalog

You can select the “Table Catalog” 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.

 

Understanding Table Relationships: Parent, Child, & Grandparent Tables

Our support articles refer to "parent", "child", and "grandparent" tables due to the use of connections. Here's an explanation as to what each of those terms mean:

Parent Table

• Definition: A parent table sits on the "one" side of a relationship, containing data that multiple other tables (known as "child" tables) reference. Imagine a tree structure, where the parent is the root and the children are branches stemming from it.

• Example: Imagine a company with multiple employees. The "Companies" table would be the parent, holding information about each company as a single entity. Each employee, connected to one company, would be in the "Employees" table, the child table.

• Key characteristics of a parent table:

  • Holds display field values for each entity (e.g., company ID).

  • Typically, doesn't contain a connection field to another table (except for self-referential relationships).

  • Serves as the central point of reference for related child entities.

Child Table

• Definition: A child table resides on the "many" side of a relationship, referring back to a single parent table through a connection field. They hold data specific to each individual entity linked to the parent.

• Example: Continuing with the company example, the "Employees" table would be the child. Each employee record is a separate instance linked to its corresponding company in the parent "Companies" table.

• Key characteristics of a child table:

  • Contains a connection field (often called a foreign key) referencing the parent table's display field value. 

  • Holds information specific to the child entity (e.g., employee name, salary).

  • Can have multiple records for each parent entity.

Grandparent Table

• Definition: In complex database structures, some child tables might be linked to another parent table, forming a three-level hierarchy. The parent table of a child table becomes the "grandparent" of that child's child (a grandchild table).

• Example:  Imagine a company with departments and employees. The "Companies" table would be the grandparent, holding information about each company. Each department within a company would be in the "Departments" table, a child of "Companies." Finally, each employee within a department would be in the "Employees" table, a child of "Departments" and grandchild of "Companies."

• Key characteristics of a grandparent table:

  • Similar to a parent table, holds display field values for its entities.

  • Not directly connected to the grandchild table (only through the child table).

  • Serves as the ultimate source of reference for all related child and grandchild entities.

Why do I need to know this about my tables?

It's important to understand why we need to know about parent, child, and grandparent tables in database design. Here are some reasons:

• Efficient data organization: Understanding these relationships helps you structure your database efficiently by minimizing redundancy. Instead of storing the same information in multiple tables, you can keep it in a single parent table and reference it from child tables, saving storage space and ensuring data consistency.

• Accurate data retrieval: Knowing the relationships between tables helps you write queries that accurately retrieve the information you need. You can easily navigate through the parent-child connections to find specific data points, like all books written by a particular author or all products within a specific category.

• Improved data maintenance: When you understand how tables are linked, it becomes easier to update and maintain data. Changes in one table can be automatically reflected in related tables, ensuring data consistency and reducing the risk of errors.

• Future scalability: Building a database with clear relationships makes it easier to expand and adapt in the future. If you need to add new data or functionalities, you can easily extend the existing structure without compromising its integrity.

• Collaboration and communication: When everyone involved in managing the database understands the relationships between tables, it facilitates clear communication and collaboration. This can be particularly helpful when working with teams or sharing data with different stakeholders.

In short, understanding the concepts of parent, child, and grandparent tables is essential for designing efficient and data-driven databases. By clearly defining these relationships, you can improve data organization, simplify queries, and ensure data integrity.

 

 

Editing Your Table's Settings

 

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.

 

 

Connecting Tables Together

The right column lists any connections the table has and a 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

 

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 menu. This will show you all the records stored in the table.

 

Deleting Tables

You can delete an table by selecting the dropdown arrow icon next to the table's name and selecting the "Delete" option:

 

You'll be asked to confirm this action:

---