About Your Database

In this article, you will gain a comprehensive understanding of how to work with your database in the Data section of the Knack Builder.

How the Data Section Works

Defining your data is the initial and crucial step in the app building process.

To define the data your app will use and store it in a database, you can utilize the Data section of your builder. This section provides you with the necessary tools for this task.

To access the Data section, simply click on the "Data" tab located on the left side of the Builder interface.

 

Tables and Fields

Knack utilizes tables and fields to define the data in your app.

Fields need to be created for every data element that you wish to store and manage. For instance, if you plan on creating an app for contact management, it would be ideal to include fields such as name, email, and phone number.

Tables are used to group these fields together. Imagine a table like a spreadsheet, with each field representing a column in that spreadsheet.

An example of an Inventory Manager app is shown below, where the Products table is selected for editing.

 

To access the Data section, you will find a list of all your tables on the left side. Simply click on a table to edit its fields and settings.

If you need to add a new table, you can do so by clicking on the green "+" button next to the "Tables" label.

1. To the right, you will find a list of the fields for the selected table. To edit and format a field, simply click on it.

   1. To add fields, click on the “Add Field” button located above the field list, and then select the type of field you wish to add (such as short text or number).

2. The user roles in the app are stored in this section. They function similarly to other tables but have special fields for managing user logins, such as Name, Email, Password, User Status, and User Roles.

   1. User roles are optional and may not be necessary if your app does not require user login for access.

 

 

Connecting Tables Together

Many applications will require multiple tables. In such cases, these tables will often be interconnected.

Connections establish the relationships between records in different tables. Here are a few examples of the various connections you can create:

• Many employees can be connected to one company.

• One student can be connected to one application.

• Many managers can be connected to many projects.

The connections for each table can be found on the right side of the interface:

When you create a new connection, it generates a unique connection field in the table. These fields can be utilized just like any other field in your app.

If you include a connection field in a form, it will function as a "look-up" feature, allowing you to select a record to establish a connection with.

Connections will open up powerful functionality you can add to your apps:

• One of the powerful features of Knack is the ability to display parent-child records in your interface. For instance, if you click on a company, you can easily view all the employees connected to that particular company.

• You can utilize formulas to calculate the total and average values of child records. For instance, you can easily sum up all the employee salaries for each company.

• Displaying records that are linked to the logged-in user. For instance, when a company logs in, they can only view the employee records associated with their specific company, rather than seeing all the employee records stored for every company.

 

 

What should my tables be?

When it comes to defining tables for most applications, the process is usually quite straightforward. However, if it's not immediately obvious, here are some helpful ways to approach table definition:

• Consider the app's fields and the type of data it will work with. Create a comprehensive list of fields needed for displaying and managing data, and then organize them into logical tables.

• Evaluate if each input or column belongs to a unified category when designing forms or displaying tables. Consider allocating them to different tables if they do not fit within a single category.

• Consider breaking up plural items into a separate table if they can be grouped alongside something that is single. For example, if you want to track a company (single) and each company can have multiple employees (plural), it makes sense to have a separate table for employees connected to the company.

• Separate repeated data into a separate table and connect it to your original records to save time and effort by updating the data in one place.

 

 

Working With Records

After setting up your tables and fields, you are ready to store records for each table.

To access the records for each table, simply click on the Records tab located on the left side of the page.

Each record contains real data that corresponds to the fields you have added for that specific table. For instance, if you include an image field, you will have the ability to upload an image for each individual record.

 

You have the option to add records to the database directly in the builder by clicking the "Add Record" button or by importing data through files. Another method is to add records through the live application using forms.

It is important to note that certain apps are best populated directly through the builder interface:

• Record Searches

• Product Catalogs

• Business Directories

These apps are primarily designed for displaying or searching records, allowing you to directly add these records to the database.

In some cases, records will be added to the app when users fill out forms in the live application. For example:

• Parents may fill out a form to register their children for a camp.
• Job hunters may fill out a form to apply for open jobs.
• Employees may fill out forms to complete a project.

These applications utilize forms to effortlessly add and manage records, resulting in a database that is primarily populated by the application itself.

• Parents filling out a form to register children for a camp

• Job hunters filling out a form to apply for open jobs

• Employees filling out forms to complete a project

 

 

Importing From Spreadsheets

To easily import your data into your app, you can utilize the spreadsheet import feature. You have the option to import records into an existing table or create a new table.

To import into or update an existing table, simply select the table from the menu on the left side, then click on the "Records" tab and the "Import" icon located in the top bar:

To create a new table using the import feature, navigate to the Data tab on the left side of the screen and click on the green "+" button located above the list of tables.

 

Here, you will have the option to create a new table by import:

 

 

 

User Records

User records are a special type of records that represent users who have the ability to log in and access your app.

A user record contains required fields such as Name, Email, Password, and others that are necessary for managing logins. In addition to these fields, user records can also have additional fields and can be connected to other tables, just like normal tables.

To enable user logins, simply click on the "Enable Users" link:

 

 

How will my app's pages work with my tables and fields?

Your app's pages serve as the interface for users to view and update the records in your database. Each page consists of various views, such as grids, forms, and searches, which are visual elements that enhance the user experience.

When you add a view, you have the option to select the corresponding table that the view will work with. This allows you to access all the fields from that specific table and incorporate them into the view.

For example, if you choose to add a grid to display Company Records, you can easily include any of the Company Fields as columns in that grid.

---