The Caspio Low-Code Platform
All the Tools and Support You Need
Unlimited Users With Every Plan
Ned Pajic: Hi, welcome to this ultimate video guide on how to build a resource management application with Caspio secure and easy to use online database platform. My name is Ned. And in part two of this video guide, I will show you how to begin building an app in Caspio, how to set up all the database tables and how to relate your tables using primary and foreign keys. Let’s dive in. When you log into your Caspio account, you’re always taken to the home screen where you see the list of all of your applications and to begin building your very first application in Caspio. All you need to do is click on this link new app. That’s going to launch this window. And from here, you can build your applications by importing data, or you can begin building your applications as a blank template. Now I already have my application created.
Go ahead on your end, click on this button, give your application a name. You can call it whatever you want. In this use case, we’re building an it resource management application. Ideally you want to have this same name and convention. Once you’re done click on finish, and you should now be able to see that application listed on your homepage from here. All you need to do is click open. And once you open up the application, you’re gonna be taken to the overview screen. And the overview screen is mostly informational for you to keep track of your progress as you’re building your applications inside Caspio. Once you’re done with the overview screen, the most important place where you want to begin is going to be the tables. Object tables are the foundation of any app that you develop inside Caspio. So it’s critical to have the tables built initially in order to have the right structure and also the right schema, which will serve as a blueprint for later on when you’re building your forms and reports now to speed things up in this video guide, I have already built all of my tables, but I will show you all the field names of each table.
This way you can pause the video and then copy the same fields inside your tables as well. Now, the main table that I want you to start off with is the employees table to build your table. All you need to do is click on this link here, new table. And once you do that, you’re gonna be able to see a window that looks like this. Now you’re gonna have zero fields inside your table. At this point, what I want you to do is just go ahead and pause the video and copy the fields that I have on my screen, along with the appropriate data type and also flag all the fields that are meant to be unique. The fields that this table will need is the employee ID. This is going to be the primary key for this table to identify each employee with a unique ID, you can see how Caspio immediately flagged it as a unique field.
You also have other options here, so you don’t have to use auto number. You can also use GUID or random ID. Then you have your first name, last name, and a full name for full name. I’m using a formula data type. Once you select a formula data type over here to the right click on the edit link, and you’re gonna get this popup window. And all you need to do is insert the first name side by side, next to the last name. And then in between the two fields you want to input some synt tags here to create a space in between the first and last name. You can also verify your formula. Once you done click on apply. Then we have email, which I also tagged as a unique field because you’re never gonna have two different employees with the same email.
Email is always unique to each employee. We have our password field title of each employee department. They belong to in locations. Now, in this use case, I’m gonna be using different locations. Perhaps your organization has employees across their different regions. So you may wanna flag those as different locations, access level to identify who’s gonna have what privileges and user permissions manager ID so that we can track and know which employees report to which manager account status is a checkbox. Yes or no, which basically allows a higher level user to activate or deactivate a certain employees. If they no longer want them to log in. And then we have date created, which is a timestamp. And we also have date updated, which is also a timestamp, but make sure that you also hear on the right stamp on update and stamp on insert for date created.
Once you’re done, save your table, reopen that table and under data sheet tab. I want you to input a few employees inside your table. Notice that every single one of my employees in this table has a unique ID. The full name is concatenated. Thanks to the formula data type. Each email is unique. All the passwords are encrypted. We know their titles departments, what location they’re in access level. You can see, we have employees. We have admins, managers, employees, et cetera. And thanks to this manager ID field. We know who reports to who. In other words, notice that Susan Smith, she happens to be the CEO of this company. And her ID is number one. Karen Riley and Jason Barnes, both report to Susan Smith. So what we do is we take Susan’s ID and we stamp it inside these two data cells this way. We know that Karen and Jason report to Susan later on. I have other employees here, for example, VP of sales also reports to Susan Smith. And down here below, we have Adele who is a technical writer, also reports to Susan Smith. Now I did this manually inside a table, but you can later on build a submission form to accomplish the same thing. Once you’re done with this table, go back out to the table’s menu.
And now I want you to create the inventory table. So I’m gonna click on design at this point, go ahead and pause the video and copy all the fields that you see in my table inside your account as well. Now, if you’re not tracking all of these different assets, for example, CPU, Ram, depending on what type of inventory your company manages, you don’t need to include all of these fields just include relevant fields that pertain to your organization and the types of items that you wish to track. So each item will be identified using a random ID assigned to ID. We wanna know what items are assigned to what employee. So you want to use an integer data type in order to properly stand to auto number from the parent table inside this child table created by ID. So when an item is shipped and gets delivered to the office, we want to know which manager is submitting this item into the database and last updated by ID.
I would like to know which employee last updated this item. So if you make an update on a specific asset, you wanna be able to stamp the ID. Whoever’s making that update. The rest of the fields should be self explanatory. So we’re not gonna spend too much time on that. Once you’re done, go ahead and save your table, give it a name and you should now be able to see two tables listed in this menu. The next thing I want you to do is create the inventory history log table. However, you don’t have to build this table from scratch. All you really need to do is take the inventory table, create a duplicate. And once you create that duplicate, give it a name, go to design tab, pause the video now and just copy all the changes that you see in my screen inside your account as well.
You’ll notice that we don’t have a unique field. And I also change the data type to text 255 that got to the table’s menu. The remaining tables are going to be simple lookup tables. So they’re not gonna be very robust in terms of fields and you don’t have to worry about primary and flooring keys. So let’s take a look at each table. One at a time. This is a device lookup table. Again, click new. I’m gonna click on design and show you that this table only has one field, but then in a data sheet tab, these are all the devices that I’m tracking. You don’t have to add all of these devices. Just go ahead and input a few if you want. And when you’re done, go back out to the table’s menu and you should now be able to see four tables. Then we have a table for a location. Again, if you’re not tracking by location, you don’t really have a need for the field location in your inventory table, and you don’t have a need for a lookup table of locations. I’m gonna click on open and show you that we have four different locations. So if you want to copy the exact use case that you see in the video, go ahead and build this table, add your field and input all these data values.
Two more tables, one for status. Let’s open it. My field name is status, and these are all the different statuses that we’re tracking across all the inventory. So for example, if we purchase a new inventory item, I want to flag that one. As in transit. If the item is defective, we can change the status to that. If the item is available to be used for another employee, we can flag that item as available. And the very last table that I want you to create is a very simple access level table also has one field access level, and these are all the different access levels that our application is going to have. So an employee can be an admin manager or employee. Once you’re done, you should be able to see all seven tables listed on the menu. The next thing that I want you to do is create a trigger and all the trigger is going to do is when you insert something in the inventory table or make an update, I want that insert and that update to always be logged inside the log table.
This way we have a historical trail, and we know what happened for that specific item at any given point. So under the inventory table, click on more and then click on triggered actions. I already have it created. I’m just going to click on edit to show you my trigger. And this is what you have to recreate. You can pause the video. Now, the first thing you’re going to do is insert an action called insert into, and when you do that in this dropdown, go ahead and check for insert and update, which basically means you want to insert and also update the inventory history log table from the inserted table. And the inserted table is referring to this table up here, which is the inventory table. So let me explain this a little bit. It helps you, if you work backwards, when you add something to the inventory table, which is the inserted table, I want to insert an update, the log table.
And then what you have to do is map the fields from both of these two tables, basically all the fields that you wish to track. Just go ahead and copy those fields. If you need to insert an additional field element, you can go to data and just drag over the field element and connect it over here to your other field. We don’t have a need for that. So I’m just going to delete that. Once you’re done, go ahead and save and enable your trigger and give it a name. And you can even test this at this point. Let’s save it.
I also wanna track who created this item. So I wanna stamp the employee’s ID in this table as well. And I also wanna know who was the last person to update the items. So once again, we’re gonna be stamping the employee ID inside an inventory table. Now move over the same line into the log table, just like you did in the inventory table. And you’ll be done with setting up all the relationships between your parent and child tables for this use case. I hope that you enjoy part two of this video guide. Thanks for watching. And I’ll see you in the next video where I teach you how to set up all of the views to filter out users based on their permissions and how to create the login interface. So once users log in, they’re gonna be redirected to their own portal to view their own information. Thanks for watching. And I’ll see you there.
Share this post: