The Caspio Low-Code Platform
All the Tools and Support You Need
Unlimited Users With Every Plan
Ned Pajic: Hello, everyone. Welcome to part two of this video course. My name is Ned. I am a technical evangelist at Caspio. In this video, I’m going to teach you how to create your database tables. I’m going to also show you how to create relationships between your tables, where we join together, using primary keys and foreign keys. If you haven’t seen part one of this video course, I do recommend that you watch that video first to understand exactly the type of application. We’re developing in this entire course. So again, don’t jump into part two until you’ve actually seen part one. All right.
Let’s go ahead and begin to build the application in Caspio. All you need to do is click on this link new app, and then you have two options on how you can begin. You can either bring your data from an external source. If you’re using Excel or access database, you can import your data. But in this lecture, we’re going to start building our application from scratch. Next step is very simple. Just give your application a name. You will have your own naming convention for the type of application that you’re developing. My application is just simply going to be called service requests. And once you’re done, just go ahead and click on finish, and you should be able to see that application container listed right here on the homepage of your Caspio account.
The next step, what you need to do is just click on open. And once you open up the application, you will see this additional navigation menu. On the left hand side, these are going to be the objects that we’re going to be creating to build the entire application. The very first object that’s highlighted by default is the overview tab. This screen is mostly informational to help you keep track of your progress. So as you’re building your application, you can input some notes over here in the middle. We have some metrics to help us understand how many tables we’ve created, how many records we have inside our tables, et cetera. Once you’re done with the overview screen, the most important place where you want to begin to design your application is always going to be the tables.
Object tables are the foundation of any app that you develop inside Caspio. This is where all of your data is going to reside inside database tables. And this specific application that we’re creating is going to have a total of four tables. Let’s go ahead and begin building each one of our tables. The first table that I’m going to build is the customer table. And when you’re building a customer table, you want to ask yourself, what kind of information do you want to collect about your customers? In other words, what information do you want to store inside this table? You could have customer address. You might have the customer’s phone number, zip code. It is completely up to you what kind of information you want to store, but the most important field that you need to add to each one of your tables is always going to be a primary key, or a unique identifier. That’s going to identify each record inside your database table for the customers.
We’re going to call it customer ID. And then when you click on the data type dropdown, you’re going to see a slew of options on what kind of data type you can apply to that field. In other words, what kind of information do you want to store inside that field? If you want to store textual data, you’re going to select from text 255 or text 64,000, if you want to store numerical data. For example, like forecast or revenue, you might actually opt out for number into your currency. You can also have dates stored in the database. That’s self-explanatory you might have a check box, like a bullion yes or no. And then you’re going to find a whole lot of options here that you can select on how you want to capture information for that specific field. Now, as I said, I want this to be some kind of a unique ID Caspio gives you four different options for IDs.
I’m going to select random ID and you’ll see immediately how Caspio flags that as a unique ID. The next field that I want to add to my table perhaps is first name of the customer. Let’s have last name. I always like to actually have a full name field in my table. Not only do I want to store the first name and last name in two separate fields, I would like to concatenate these two data points into one field. This column is going to actually have both first and last name together. In order for me to combine the first and last name, I’m going to choose the data type for formula. If you look here on the right hand side, you’re going to find an edit link, click on that. Then you just have to insert your formula on how you want to combine those two data points to do that.
You’re going to click on the insert button and find the first name, field, click. Okay. And right next to that, we’re going to insert last name. Now, we want to create some space in between these two fields because you don’t want the first and last name to be without a space. In order to create a space, a little bit of syntax is needed here. What we need to add is a plus attribute and then a single quote, add some space, another single quote and another plus icon. This is how you’re going to create a space in between the first and last name. You can verify your formula looks good. Let’s click apply. Later on, when I add some data inside this table, you will see the behavior of that syntax, how it creates a space in between the reason also why I like to use the full name in my table is let’s say, you’re sending an email or you want to display the full name of the customer in a report. This gives us access to that field and we can very easily display the full name rather than just the first name, or the last name. Let’s have company name.
How about we also add email, password and let’s have date registered for date registered. I’m going to change the data type to a timestamp because I would like to know as the administrator, when this customer was added to the table, I think that’s an important data point. We should always be able to track dates when we make updates, or when we add something to the database, my password data type, we have a special data type called password. It encrypts the password on the table level, and for my email, we’re going to flag this as a unique field as well, because you’re never going to have two different customers with the same email. Each customer will have their own unique email address. I think these are all the fields that I want to have in my table. Of course you can add additional fields. If you’d like, I’m going to hit save, and let’s give this a name I’m going to call it Sr customer info.
Now I always like to add my prefix, which is an abbreviation of the application name because I call it service request. I’m going to abbreviate that as Sr and add that as my prefix to my table. Why do I recommend that you do that? Well, let’s say later on you want to export this data or you want to export the entire application. It makes it a lot easier to find a table. When you add a prefix and everything is uniformed throughout the application when you have that single prefix to identify the entire and all of the objects inside the app. When you’re done, click on finish, and within just a couple of minutes, you should have your very first table created. Let’s create three more tables. The next table that I want to create is the employees table. And this table is going to store all of the employee information. Let’s have employee ID. Don’t forget to add your primary key first. I’m also going to use random ID for this as well. And we’re going to repeat the same thing as we did in the customer table. I’ll have first name, last name, and then full name. Hopefully now you know how to set up the full name. We’re going to use the formula field data type can’t edit, and we’ll add the first name. We’ll add the last name. If you remember how we did this before, we add a plus icon, single quote space, single quote, and another plus icon we’re going to hit apply. That’s how you create a full name field.
Now, a very important field that’s needed in this table. We’re going to, I like to call it role. You’re going to have your own name and convention, but this field is going to be used to identify and to differentiate your approving managers from your service representatives. One field call it whatever you want. We’re going to use that to differentiate all the different users that we have inside our employee table. We’ll add email, which will also flag as a unique field. Let’s have password, which will be a special data type.
And I’m going to add a field called active. I’m going to turn that into a checkbox bullying. Yes or no. The reason why I like to add this field is it gives the approving manager additional privileges. As a manager, I have the ability to deactivate or activate in a service rep to be able to log in or not log into the application. Rather than deleting somebody from the table, all you need to do is uncheck a box. And this service rep will no longer be able to log into the app. Later on, if you rehire this person, you don’t have to add to the table. All you do is just reactivate their account. Once again, they’ll be able to log in.
When you’re done, let’s save the table and we’re going to call this again, Sr employee info. And there’s our second table. Now let’s create our third table. That’s going to store all of our service requests. I can’t stress this enough, but remember to always add your primary key first. We’re going to go with service request ID and for this data type, I’ll also use random ID. Now, I’m going to add a field called customer ID. Now, remember I already have a customer ID field in the customer table. That was the primary key for that table. But what I’m trying to accomplish here is what we’re trying to figure out here is who was the customer to submit that request? What I want to do is to be able to stamp the customer’s ID from the customer table. And, I want to stamp that ID inside this table. This is how you create a relationship in the database between two tables. Because if you have a common value between two tables, now, those entries are associated. We know who was the customer that created the service request. If we can stamp that customer’s ID inside the service request table. All right, and we’re going to leave that as text 255. The reason why, because I used random ID as the primary key in my other table, where we have to use text 255 in this table as the foreign key.
Next let’s have service type. Now this field is, if you remember from part one, a customer has the ability to do a cancellation troubleshooting or new install while this field will be used for a dropdown for us to be able to select that service type, let’s have service comments. Now, I could actually enable this to be text 64,000, but let’s limit the comments to text 255. This is completely up to you. If you want the customer to be able to add more comments, you can change the data type to whatever you want. Dates submitted. I’ll turn that into a timestamp. Once again, I want to be able to tell when the customer submitted that request. We want to capture that piece of information inside a database service status. This is also going to become a dropdown. This gives the approving manager and the service rep the ability to see a status in the dropdown where the service rep is going to be able to submit the request for approval and change the status depending.
And the approving manager is going to be able to approve the request, decline the request on, have it be resubmitted for approval. So you will see later on how this field actually links to a lookup table. Service rep ID. So this field here is very important in this table. Remember how our employee table has a primary key to identify each employee? Well, when the employee assigns the request to themselves, we want to be able to stamp their employee ID in this table. This is how you’re going to associate the request to an employee. We want to be able to link these requests to employees because we want to track who are the employees that are handling the requests.
Let’s have rep internal notes. Let’s leave that as text 255. Let’s also have appointment notes. And for this, maybe we can flag this as text 64,000 so we can add additional appointment notes. Let’s have appointment scheduled date. If you remember from part one for my live example, we’ll turn this into a date and time. We’re able to set the schedule, set the appointment, schedule date in advance. And when we are going to be visiting that customer, let’s also have approving manager ID. This field here is also very important, much like the service rep ID. We want to be able to tell who was the approving manager to app approve that request or decline the request. We want to track that in a database. Okay. Not only who was the rep who assign the request to themselves, we want to know who was the approving manager to finally approve that request. That’s very important information in this table. Let’s have manager comments.
We can leave that as text 255, and then last field that we want in this table is last updated. And we’ll turn this into a timestamp. These are more or less your important fields for this table for this workflow. Of course, you can customize this anywhere you want, depending on what you’re tracking, you can add additional fields. You can remove fields if you don’t have a need for them, but these are the fields I’m going to have. And now I’m going to save my table. I’m going to call this Sr and various just simple service request info.
And the last table that we want to build for this application is going to be the request status lookup table. Let’s build that really quickly. It’s going to be a very small table. I’ll have status ID for this one. Maybe, we can choose auto number. Let’s also have request status and role, and let’s save our table and we’re going to call this Sr request status lookup. I’m going to add some static values inside this table now, because it’s going to be served as a lookup table. We’re going to open the table now and just add some entries inside it. Now we need to ask ourselves, what kind of status are these employees going to be able to see in the application? A request can be a new request. Request can also be pending, review request can also have submitted for approval.
We can also have request approved, request declined, And the last one that we want to have in here is resubmit for approval. These are the types of statuses that we’re going to have in our dropdown, but depending on the role that’s logged into the application, they’re going to be able to see a limited or a filtered lookup. If I’m logged in as the service rep, the service rep only needs to see these two statuses. Whereas the approving manager doesn’t care about pending review or submit for approval. The approving manager only needs to see these three statuses. The first one is not going to be associated with any kind of a role, but the second two, we’re going to give this to the rep and the last three we’re going to give to the manager.
Once again, let me just explain how this work using record level security. If I’m logged in as the rep and the dropdown, I’m going to be able to see these two options. Conversely, if I’m logged in, as the manager, I’ll be able to see these three options in the dropdown. If you do recall from the live example from part one you can go to a specific part of the video in the details view, you would’ve noticed that as the service rep, I’m only seeing these two options, but as the approving manager, I’m seeing these three options and you’re going to see how we filter these statuses based on the role that’s inside this table. Let’s go back out to the tables and I want to do two additional things to my tables. I just want to list some dummy data inside my customer table and also some employee table. As we start to play around with the application, we can have some demo users logging in to look at some of the interfaces. I’m going to open up my customer table and let’s add one customer. We’re going to say John Doe company, maybe ACME soft, John, at test.com. We’re going to give John password of test.
And when I click on the second row, notice how the formula field works. Inated both of these data points into this field. And we have a space in between the first and last name. And my timestamp is showing me when this customer was added to the table. Now let’s go to our employee table and let’s add to employees. I’ll have, let’s have Jane Jones manager, Jane manager.com. Password can be test, and I’m going to make Jane an active employee. Let’s also have Ken Smith who will be the rep of our application. So ken@rep.com and will give in password of test with active status. Again, you can see how the formula field works correctly. Each one of our employees will have their own unique ID. And based on the role field, we know who the manager is, and we know who the rep is. Now that we’re done with our tables, let’s go into the relationship screen.
I want to show you how easy you can create relationships using primary keys and foreign keys to give you that nice schema in understanding how your data is associated. To do that in Caspio it’s very easy, click on the relationships link, and then include all the tables that you’re trying to link together. We have our customer table, we have the employee table, the lookup table is not going to link to any other table. We’re not going to include that. And I’m going to have my request info table. These are my main three tables that are linked together, and a lot of customers and a lot of people like to work top to bottom in terms of flow, or either left to right personally, my own preference. I like to work left to right. Let me go ahead and rearrange these tables the way I want. I’ll move my service request table right here in the middle. I can move my customer table here and let’s just move the employee table here to the right. Now we know that when a customer submits a service request into this table, we want to be able to stamp the customer ID. And we want to stamp that to the customer ID in the service request table, just let go. You’re going to get this popup screen. And Caspio is going to identify that as a one to many relationship. One thing that you may want to do here is very simply include the full name and use the full name in your data pages. What you’re doing when you select this is you’re actually going to display the full name inside a service request table instead of the ID. Because once you create this relationship, we can look inside this table and then we can display any one of these fields to replace the customer ID.
When I hit create, you’re going to see that very first relationship. And this is a one to many relationship. The reason why is because one customer can create many requests. This primary key ID can be stamped multiple times as a foreign key in the related table. Now here’s my employee table. Let’s link this table to the service request table. We know that when the employee assigns a request to themselves, we want to stamp that employee ID in the service rep ID. Once again, that’s going to be a one to many relationship. And again, I would like to be able to display the full name of the employee in my data page, like on create. And you have a one to many relationship. Why because a single employee or a single service rep can assign multiple requests to themselves.
But remember how on this table, we have this field called role to identify managers versus reps. And we want to be able to see who was the manager to approve or decline that request. Once again, we’re going to move the employee ID field and link that to the approving manager ID field. That’s going to be a one to many relationship. And again, I’m going to want to display the full name of the manager and use that data point inside the data pages click on create. And now you can see how this single employee ID, which can belong to either the rep or the manager can be stamped in two different fields in our related table. And just by looking at this relationship, this schema, now we understand how the data is linked, and we know who is going to be able to assign the request based on stamping this ID to this table.
We know who is the service rep. That’s linked to this request, and we know who was the approving manager to approve decline or have that request be resubmitted for approval. This is how in Caspio, you create relationships and we use this relationship screen in order to create our schema, understand this structure. That way we, when we have the right foundation in place, now we can build all of our interfaces very quickly and very easily. Thanks for watching part two. I hope to see you in part three in the next tutorial. I will teach you how to create your views to filter out users and how to create your login screen. So both the employees, customers, and the approving managers can log into the application. I hope to see you there.
Share this post: