The Caspio Low-Code Platform
All the Tools and Support You Need
Unlimited Users With Every Plan
Ned Pajic:
Hello. Welcome back to this video guide on how to build an IT Helpdesk application. In part two, we will learn how to create tables, field data types, and how to link tables using primary and foreign keys. Let’s have a look.
Once logged inside your Caspio account, it’s very easy to begin building an application. All you have to do is click on this new app link. Then you will have two options on how you can begin. You can either import data. If you have some data offline, let’s say in an Excel document or access database. You can import your data into Caspio and then build your applications on top of your data or you can begin using a blank template. Let’s start this way. The next step is to give your application a name. Now you’re going to have your own naming convention.
It really just depends on the type of application that you plan on developing. If you want to build a CRM, you can call it CRM. Let’s say you are building an inventory management application, you can call it Inventory Management. So on and so forth. The application that we’re going to build is IT Helpdesk. The entire premise of this application is for employees to be able to log in, submit a request to the IT department. IT will take a look at that request and provide comments and close out the ticket. Then you have the admin level user that can log in. An admin level user sees a holistic view of all the tickets and all the transactions that are happening between the employees and the IT team. When done, click on finish, and you should be able to see that application listed right here on the home page. From here, what you want to do is click on open.
Once you open up the application, you will be able to see this panel on the left hand side. You will be able to click on each one of these objects to build your application. The most important place where you want to begin inside Caspio is always going to be the table’s object. This is always the starting point. Tables are the foundation of any app that you develop inside Caspio. This is where all of your data will reside. This particular application is going to have a total of three tables. We will need one table for users, we will need one table for tickets, and we are going to have a very simple lookup table for “Department.” Let’s begin with our user’s table. We click on a new table link here at the top and then let’s add all of our fields to the table.
The first field that I’m going to add is “User_ID” to uniquely identify each user inside this table. And for my data type, I’m going to choose “Random ID.” “Random ID” will automatically assign a unique random ID to every single employee that’s inside this table in a database environment. This is also referred to as a primary key. Underneath that we’ll have “Name.” Let’s also have “Department.” Underneath “Department” we will have “Email.” Underneath “Email,” let’s have “Password.” We want to have the password field inside this table because we’re allowing our users to log in into the application to be able to submit requests, review requests, and be able to oversee all of the transactions. Underneath “Password” let’s have “Account_status.” Underneath that we will have “Role.” Finally, last but not least, let’s have “Date_added.”
A couple of more changes I need to make to my table. For my email field, I’m going to turn this into a unique field because you can’t have two different employees with the same email. The email is always unique to each employee. For my password field we are going to change this to a password data type, so it’s encrypted on the table level. Account status will turn into a checkbox (yes/no) simply because I want the admin level user to be able to make users active or inactive. Instead of deleting somebody from the table, we can quickly deactivate somebody’s account by unchecking the box and this user will no longer be able to log in. The role field is going to be used to differentiate all of our admins, from the IT users, and all of the low level users that can log into the app. Finally, “Date_added,” let’s turn this into a “Date/Time” data type so that we can know what date and time this employee was added to the database. When done, click on save to save this table. Let’s give it a name. I’m going to call this “ith_tbl_users.” ith for IT Helpdesk, tbl for table, and then users because this table will contain all of my user related information. Let’s hit finish. From here what I like to do is open the table and quickly add some fictitious data to my table so later on we can have some information to test the application with. Let me quickly add three users to this table.
Now you can see in my table, every single employee in my table has a unique ID. We know what department they belong to. Every single email is unique. All the passwords are encrypted. At the moment they are all active. Based on the role field we know who the admin is, we know who the IT department employee is, and we also know who the user is. Of course, your table will probably have many more employees. Each employee will be tagged with its own specific role. Last but not least, we have the field for date added and we can see what date each employee was added into the table. I recommend that you open up the table as well and add some dummy data to it so that later on you can have some information to test with as you are testing out the interfaces on the web. Let’s go back out to the table menu and let’s build our second table that’s going to contain all the ticket information.
We need to build a new table here. Again, the very first field I’m going to add is a “Ticket_ID” to uniquely identify each ticket inside this table. Once again, I’m going to choose “Random ID.” Let’s add all the fields to this table. Let’s have “Summary.” We’re also going to have “Description.” Underneath “Description,” let’s have “Assigned_to_by_ID. We need this field inside this table because we want to know which one of our users we are signing this ticket to. This field is needed. Let’s also have “Priority.” Underneath that let’s have “Attachment” just in case we want to attach an image or a document as we submit this ticket for review. Let’s have “Ticket_Status.” Let’s have “Created_by_ID.” We want to know which one of our employees actually created the ticket. That is also important. Underneath that, we will have “Date_created.”
Let’s also have “Comments.” Last but not least, let’s have “Date_closed.” For my description field we want to change the data type to “Text (64000)” because we want to be able to store up to 64,000 characters inside that field. “Assigned_to_by_ID will be “Text (255)” because we want to stamp the “Random ID” from the user’s table into this table. The correct data type needs to be “Text (255).” For my attachment field, we are going to change the data type to “File” because we want to be able to attach a document. “Date_created” will be a “Date/Time” data type. “Comments” will also be “Text (64000).” “Date_closed” will also be “Date/Time.” Once you’re done modifying all of your fields and each of the data types, go ahead and hit save. Give your table a name “ith_tbl_tickets.” You should now be able to see two tables.
Let’s build our very last one. It’s a very simple lookup table for departments. It only has one field. I’m just going to call it “Department.” Hit save. Let’s also call this “ith_tbl_department_lookup.” The last thing that you want to do is simply open the table and list down all of the departments that your organization has. I’m going to list a few here. Let’s have “Marketing” and also “IT.” Once you’re done inputting all of your lookup table values, go back to the tables.
The last thing that I want you to do is click on the relationship screen and connect your two tables together. The two tables that this application contains are the user’s table (ith_tbl_users) and the tickets table (ith_tbl_tickets). We don’t need to connect the department lookup table (ith_tbl_department_lookup) because we’re not going to be stamping any primary and foreign keys. With this screen, you can move around the tables however you want. If you come from an access background, this screen will look familiar to you. You can rearrange your tables however you want. This screen is very important because it helps you understand the schema and how all of your tables are linked together using primary and foreign keys.
You can also expand your tables if you need to be able to see all of the fields. The way these two tables are linked together, we are going to be stamping the “User_ID” into this field because we want to know which user we’re assigning this ticket to. When you let go, you are going to get this popup window. All I want you to do in this dropdown is select “Name” and click on this checkbox to use this display value on the data page. The reason behind that is later on when you build a report based on this table of tickets. You want to be able to swap out the ID with the actual name from this table. By connecting the two tables together and checking this box, you will be able to accomplish that. The last thing that I want you to do here is just enforce referential integrity. When you do this it prevents you from deleting a user if that user has related entries in the child table. Because if you don’t enforce referential integrity and you delete a user, and this user has linked tickets. Now you are going to be leaving a lot of records orphaned. They are not going to be linked to any of the parent table values. When you are done, click on create.
One more connection we need to make is drag the “User_ID to “Created_by_ID.” Let go again. And same thing, just select “Name,” click on display value, enforce referential integrity, and click on create. Now, based on this connection we know that when a user submits a ticket, we are going to be assigning that ticket to a specific user from this table. When the user submits a ticket, we also want to stamp the user’s ID in this table so that we know which user created that ticket initially. Once you’re done, go ahead and save the layout of your table connection.
This completes the tutorial and how to build tables, how to assign and modify each of the data types and fields, and also how to connect tables using primary and foreign keys. Join me in the next video where we talk about views and authentications so that we can properly assign permissions to what users can see when they log into the application. I’ll see you in the next video.
Share this post: