The Caspio Low-Code Platform
All the Tools and Support You Need
Unlimited Users With Every Plan
Ned Pajic:
Hi, and welcome back to this video guide on how to build a knowledge-based application. My name is Ned. I am a technical evangelist at Caspio, and in the second part of this video guide, I’m going to show you how to build all the tables and relationships as the foundation needed to build the rest of the knowledge base application. Let’s take a look. To begin building your application in Caspio, sign up for a 14 day trial. Or if you have a paying account already, you’re going to log in, and you’re always going to be taken to the homepage where you see the list of all of the applications that you have built as long as you have had your Caspio account.
Now to begin building an application in Caspio, it’s very easy. All you have to do is click on this link, and then you have two options on how you can begin. You can either import data from an external source, so if you have some data offline in Excel or Access Database, you can bring that data in to begin building your application, or you can begin building your application from scratch. We’re going to do that. The next step is to give your application a name. Now you’ll have your own naming convention depending on the app that you’re developing. If you’re building a CRM, you might call it CRM. If you have a need for project management, you might call it Project Management. The application that we’re going to build in this video guide is a knowledge base application. I’m just going to abbreviate that as KB App, which will stand for knowledge base application. When done, click on finish, and you should be able to see this application container listed on your home screen. The next thing that you want to do is open up the container, and you’re going to find these objects on the left hand side.
These are the objects that you will have to build in order to complete the application. The very first object that’s highlighted by default is the overview tab. This screen is mostly informational for you to keep track of your progress and also to be able to see symmetric. The most important place where you want to begin building your Caspio applications is always going to be the tables object. Tables are the foundation of any app that you develop. That’s where all of your data is going to reside. It’s very important and critical that you have the right foundation in place because it makes it a lot easier to add additional components at a later time if you wish to scale up or expand on the existing application. Now this app that we’re building is not going to be very table heavy. It’s actually only going to have four tables, one table that’s going to store all the user information. One table will have all of the article information table for feedback and just a very simple lookup table that’s going to contain all the departments. Let’s create all four of these tables.
We’re going to begin by clicking on this link at the top, and then you need to list all the fields for that specific table that you wish to collect. I’m going to begin with the user table, and as I always teach people when building applications, always remember to add your primary key first. It doesn’t have to be the very first field in your table, but it’s much easier to locate when you open up the table at a later time. I’m going to say user ID, and I’m going to select “random ID” from this dropdown. Now, Caspio does give you other options here. You can also choose to have auto number, prefixed auto number, and GUID.
You have a couple of different data types that you can use for your unique ID. This unique ID is very important in every single table. You should always have a unique ID in every table that you create inside Caspio. Because again, like I mentioned earlier, it’s a lot easier to scale up and add additional components because you’re trying to link this data to different tables. In order to retain integrity and to create one-to-many and many-to-many relationships. I’m going to choose “random ID”. Then, I’m going to have in my table first name, last name, and notice that I’m using text 255 as the data type because in that field, I’m actually collecting text, and I don’t believe that the first name or the last name is ever going to exceed 255 characters. If that’s the case for you, then you may want to opt-out to select 64,000 because this type of data type will accept up to 64,000 characters.
The next field that I want to add is full name, which is going to be a continuation between the first and last name. We’re going to combine these two values together into a full name because sometimes in an application, you might want to address somebody by their first name, or you might have a need to use both the first name and last name as a full name. Now for this data type, I’m going to choose “formula”. Over here, to the right, you’ll see a little edit link that you can click on. Now, how do we actually combine or merge the first name with the last name? We’re going to use this insert button to select first name, and a little bit of syntax is needed here. What you want to do is add +” “+[@field: Last_Name].
What this is going to do is create spacing between your first and last name. You can verify the formula, it looks valid, click on apply, and there’s my full name field. Next will be email. I’m going to flag that as a unique field. The reason why is because you’re never going to have two different employees with the same email. Every single time an employee is added to this table, they’re going to have their own unique email address. That’s why we want to flag that as unique. Password field should be self-explanatory. Let’s change the data type to password, so it’s encrypted. Because inside a user table, you need to have their credentials because we’re going to use those credentials to log into the application. Underneath password, we’re going to use a field called roll, and I’m going to use the text 255 data type. This field is going to be used to identify in the user table who’s going to have admin rights versus who’s going to have user rights, and you’re going to see how all of that functions as we begin to build the application. Let’s have account status. I’m going to turn that into a checkbox. This field will be used, think of a checkbox on the front end when you’re looking at somebody’s account. If you wish to disable it, you can uncheck the box. If you wish to enable it, you can check the box again.
And last but not least, let’s have date added. And for this data type, we’re going to use a timestamp because every time I add an employee to this table, I want to automatically stamp the date and time upon insert. Once you’re done with this table, click on save and give it a name. Let’s call it KB Users. Hit finish, and there’s my very first table. If you open it, you will see that it’s an empty table. We just built it from scratch, so there should be no data inside this table. If you wish to modify the field names, you can always go back to table design, and here you can rename a field to something else. You can delete a field, or you can introduce a new field to your table.
Let’s move on with our second table. And the second table. Let’s create the table that’s going to store all the article information. Again, remember to add your primary key first. Let’s call it article ID, and I’ll just use random ID once again. The field underneath that I’m going to add is called user ID. Now we just added a user ID in the user table, which is that unique key that’s going to assign an ID to each one of our employees. I’m also adding the user ID field in this table because what we want to accomplish is to store the primary key from the user table inside the article table. This is going to create a common value between two tables, and we’re going to know who was the user to have added that article to this table. It’s for tracking purposes and knowing who is the user that’s adding articles to the database. Imagine if I had John with ID number one, and John inputs an article. I’m going to stamp his ID number one in this table. This way, I have a relationship, and I know it was John who was the one who added that article. Department, title, article itself. For this one, let’s flag this as text 64,000 because the description of the article can be longer, and it’s definitely going to exceed 255 characters.
Attachment. When we deal with attachments in Caspio, it’s not going to be text data. It’s actually going to be a file type so that you can attach either a PDF document, maybe a Word file or Excel document, or any other type of file that you wish to input into your knowledge base. Count, I’m going to change the data type to a number. This field is going to be used to add an additional increment of one every time we look at an article. This is a built-in feature in Caspio. If I look at a details of an article, it automatically increases by account of one. Internal notes, this definitely needs to be text 64,000 because when we input notes, typically they will exceed text 255. Dates submitted, it’s going to be a timestamp. Date updated, also going to be a timestamp. However, for this one, I want to stamp on update, not on insert. And last but not least, we’ll add status. And this is going to be used when the user changes the status if it’s going to be a new article, pending, submitted for review, et cetera. Let’s save our table, and let’s call it KB Articles. That’s the second table.
Let’s build the remaining two tables. This time I’m going to build my feedback table. Let’s have feedback ID. That’s going to be our primary key to identify each row in the table. I also want article ID now and user ID.
The reason why you want “article ID” in this table is because we’re trying to see what feedback belongs to what article. As you stamp the article ID in this table, we’re going to be able to relate the feedback to that article. I also want user ID in this table because I’m trying to see who was the user to have submitted that feedback. That’s also something that we want to track. Underneath user ID, we’re just going to have a very simple question here for helpful whether or not that article was helpful. Comments will be text 64,000, and dates submitted will be a timestamp. When done, click on save, and let’s call this KB feedback.
Last table that we’re going to build is a very simple lookup table that’s going to contain all the departments. Remember to add your primary key once again. Department ID, and I’m also going to use “random ID” for that and the department itself. This is a very small table. Let’s save it, and let’s call it KB departments because this is going to be my lookup table. I’m very quickly going to open it and just add some information to it. My departments in my drop down eventually when I create the form can be sales, marketing, support, administration.
And last but not least, let’s professional services. When you’re done, go back out to the tables menu, and you should be able to see all four of those tables in literally just a couple of minutes. You can build all four of these tables, and this is going to serve as the foundation for when we begin building all of our forms and reports. But you must build the tables first because that’s where all of your data is going to reside.
The next thing that I want to show you is how do we create relationships between these tables. And to do that, you’re going to click on this link relationships, and very simply just insert all four of your tables by clicking on the check box. The department table is really not needed because it’s not going to have a relationship with any other table. It’s just going to serve as a lookup table.
These are our main three tables. You can move them around anyway you want, depending on your preference. If you’re coming from an access background, this should look very familiar to you, so you have the ability to rearrange the tables however you want. But let me show you now how we link these tables together. For example, we have the user ID inside our user’s table. When a user creates an article, we’re going to be stamping that user ID in the article table. When you let go, you’re going to see this popup. Now you have some functionality here as well that you can enable on your application, including referential integrity that enforces the integrity between your two tables. You can also look at information from the parent table. If you want to grab the full name from the parent table and display it on your report or data page, you can enable this checkbox.
Caspio immediately going to identify this relationship type as a one to many because a single user can be linked to multiple articles. When you click on create, you’re going to see this line between your tables. This means that one user can be linked to many articles. As users submit articles to this table, you’re going to see their ID repeating multiple times. Let’s continue when you stamp the article ID inside the feedback table, drag over the line. For this one, we don’t really need to display any additional information, so let’s leave this the way it is. You’ll see it’s a one-to-many relationship. Click on create, and now you can see that a single article can be linked to multiple feedback. And the last relationship type here is that we know that each user is also submitting feedback. We want to stamp the user’s ID inside the feedback table, and here it actually makes sense to display the full name.
If I want to build a report of all the feedback, I want to be able to display the full name of the user in that report instead of the ID. When you’re done, click on create, and there is all the relationships between the tables. Believe it or not, the feedback table becomes a joint table between these two tables. You also have a many-to-many relationship here, along with two one-to-many. This is a very simple structuring Caspio. I have actually seen applications that have over 20 tables that are linking back and forth, and it eventually starts to look like a giant spider web. But if you come from a database background, this should make sense. And even if you don’t, it’s very easy to understand what’s really happening with this application. We’re just tracking to see who are the users that are adding articles, who are the users that are submitting feedback, and we want to know what feedback belongs to what article.
Thanks for watching video number two. I hope to see you in the next one, where I teach you about views and how to create logging screens inside Caspio. Then in part four, we’re going to learn how to build the forms and reports. Finally, in part five, we’re going to learn how to deploy the application. I’ll see you there.
Share this post: