The Caspio Low-Code Platform
All the Tools and Support You Need
Unlimited Users With Every Plan
Ned Pajic: Welcome back, my name is Ned. I am a technical evangelist here at Caspio, and in this ultimate video guide I will be teaching you how to create an expense reporting application using Caspio’s low-code visual application builder. In part two, I will teach you how to set up all the database tables and create relationships using primary and foreign keys. Let’s have a look.
To begin building your 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 proceed. You can either import data from an external source, so if you’re bringing data in from access database, maybe an Excel spreadsheet, you can bring your data into Caspio and then build all of your applications on top of your data. Or you can build your application using a blank template. There’s no right or wrong way to begin.This is really for convenience if you’re importing some data, but even if you begin this way, you can always go to tools at a later time and import data.
We’re going to build this application from scratch. The next step is to give your application a name. You’ll have your own naming convention, of course. It really just depends on what problem you’re looking to solve and what is the purpose of this application that you’re building. In our example, we’re going to call this application expense reporting, and once you’re done, go ahead and click finish to save your changes.
And immediately what you’ll notice is this application will be listed on your home screen. From here, what you want to do is click open and once you open an application, you’re going to find these objects on the left hand side and you’ll be using these objects to build your application that you’re currently inside. The overview screen is mostly informational for you to keep track of your progress and to add some notes, but the most important place where you want to begin building your application inside Caspio is always going to be the table’s object.
Tables are the foundation or the backbone of any application you build inside Caspio. This is where all of your data will reside and where you’ll have all of your field names along with their appropriate data types. To begin building your tables for this application, all you need to do is click on this link, new table, and once you open this view, you’re going to be able to list all of your fields here on the left, and you’ll be able to assign the proper data type for each one of your fields. Now I’m quickly going to populate this table with all the necessary fields for our first table, and then once I’m done, I will go ahead and explain all the field names and the appropriate data types.
But what I want you to do is I want you to pause this video once you see the fields and go ahead and copy the fields and the data types inside your application as well. And here I have all the fields listed along with the right data types. As I said just now, go ahead and pause the video and copy all the field names along with the data types inside your application. If you decided to build this application along with the video, notice that for my very first field I’m using the employee ID, I set my data type to auto number to automatically assign a unique ID to each employee inside a table, and that’s also referred to as the primary key to uniquely identify each row inside a table. I have my first name field, last name. I also have full name, notice that I’m using a formula data type for my full name.
The reason why is because I would like to concatenate both of these values into a single column. Now when you select a formula data type, over here to the right, go ahead and click on edit and inside this window you’re going to insert your first name as a field and then you’re going to add a little bit of syntax to create a space in between your first name and your last name. And you do that by adding a plus sign, single quote space, single quote, and another plus sign, and then you simply just insert the last name as a parameter inside this window. When you’re done, you can verify the formula, click on apply, and now every time you create an employee inside this table, it automatically concatenates those two values as a full name. Next, I have the user role field to identify who is going to have admin privileges versus manager, HR, and employee.
I have my email field. I also set this to a unique field. The reason why is because you’re never going to have two different employees with the same email. Password I set to password data types, so it’s encrypted on the table level. I have my title field for each employee, department they belong into, user photo if you would like to capture an image of each one of your employees, you’re welcome to do that. This field is not mandatory. In fact, many of these fields are not mandatory. But if you’re looking to track more information pertaining to your users, you’re welcome to add these fields as well. Manager ID, which later on you will see how we stamp the employee ID into the manager ID. This is going to allow us to know who reports to who. If I have a CEO and a CEO has its own unique ID, who happens to manage maybe five managers, I would like to stamp the CEO’s ID into the manager field for those specific managers.
And you’ll see how all of this looks in just a couple of minutes. Account status, I turned that into a checkbox, yes and no, so that the manager already admin has the ability to activate or deactivate certain employees if they no longer wish for them to be able to log into the application. And last but not least, I have date created. Always good to have a date field on the table so that we can keep track when data is added to a specific table.
Once you’re done adding all of these fields, next step is to simply save your table, give it a name. I’m going to call this table ER for expense reporting, underscore TBL underscore employees. Once you’re done, go ahead and click on finish to save changes, and you should be able to see that very first table listed under the table’s menu. From here, what I would like for you to do is open the table.
Now you’ll see it’s an empty table. Go ahead and now add a few employees to this table. I’m going to quickly populate my table so that you can see how the employee ID field and the manager ID relate to one another. And here I have populated my user table with some sample data. As you can see, John Davis has an employee ID of one. He is the highest level user in this application. I gave him a user role of admin, as you can see. His title is ceo. He belongs to his own department, which is ceo. And at the moment you can see we have an active status when this account was created. I’m not adding any user photos inside this application, but you could attach a photo to John Davis if you wish, or anybody else inside your company. We have our passwords, they’re all encrypted, and all the emails as you can see are different.
In other words, they’re all unique. Here’s Jesse Smith. Jesse happens to be a manager of this application. He’s a VP of product, belongs inside a product department, and you can see that he reports to ID number one, that happens to be John’s ID. So all you have to do is take this ID number one and populate it, whoever reports to who. As you can see over here in this example of Jesse Smith, as I mentioned, is a VP of product. Underneath that, we have Carly Jones. Carly Jones is a full stack developer also inside a product department, but Carly reports directly to Jesse. So we want to stamp Jesse’s ID inside this cell of ID number two and just keep going. For example, Stacy Fowler, Stacy’s a VP of marketing reports directly to John Davis, so we stamp John’s ID inside the cell. And lastly, we have Roberto, who happens to be a technical writer, and he reports directly to Stacy so that we take Stacy’s ID and populate it inside that cell and it’ll just cascade.
Just keep adding all of your employees to this table and make sure you have the right and appropriate manager ID stamped in the related field. I added all of this directly inside the table. Later on in video four or five, I’m going to teach you how to build a submission form so that you can quickly add this data using a form rather than doing a manual process directly inside the table. So again, pause the video and go ahead and add some of your own sample data so that you can get the feel for how this employee ID will stamp directly into the manager ID, depending on who is supervising who. Once you’re done, go back out to the table’s menu and let’s set up our second table.
Once again, I will click on new table and you’re going to want to add your fields once again. Now, this table is going to contain all the information pertaining to our expenses.
I’m going to quickly populate my table, same as before. And here we have all the fields for managing expenses. Notice that my very first field is expense ID and I chose data type random ID so that we can automatically assign a random unique ID to each expense inside a table. I also want to be able to stamp the employee that’s actually submitting the expense. So this becomes an integer data type. We have the category title description, which I turn into tech 64,000 total receipt. We want to be able to attach a receipt so that we can submit that for approval. And I chose file data type, which deals with attachments. We have the status, we have dates submitted so we know when this expense was submitted. We also have processed by manager ID, which is integer. So when a manager is looking at the expense, we want to be able to stamp that manager’s ID into this table because it was approved by that specific manager.
We also have manager rejection reasons so that if the manager rejects the expense, we want to input the reason why the expense was rejected. We also want to track when that expense was processed by the manager. Then we have processed by HR ID. Once the HR is done processing the expense, we want to know which one of our HR employees processed that expense. So we want to stamp that unique ID. We also have rejection reason for hr. We have a process date. And finally we have pay date when that expense is finally processed and paid out to the employee. When you’re done, go ahead and save your table. Once again, I’m going to call this er_tbl_expenses, and I’m simply going to click finish to save my changes. You should now be able to see two tables listed for this application. The remaining four tables are going to be very simple lookup tables that will be used inside dropdowns on our submission forms and also the reports.
Now I’m quickly going to populate the remaining four tables and just quickly show you all the values we have inside those tables. And here’s a list of all six of my tables. The remaining four tables, as I mentioned, are just basic lookup tables. All I want you to do is click on this link new table. Now since I already have my tables created, all I’m going to do is click on design to show you my field. So as you’re very first lookup table, give it a field name category, save your table. And then once you open the table, go ahead and list a few categories inside these cells. And as you can see, I have my categories listed. You can pause the video now to input your own categories. And when you’re done, go back out to the table’s menu. And all you’re going to do is repeat the process three more times, click on new table.I’m going to open up my department’s lookup table. Here’s my field name. And inside a data sheet tab, go ahead and input all of your department values. Again, pause the video if you need to, and then copy the same values as I have on the screen.
Back out to the table’s menu. Here’s my table of statuses. I simply called my field status. And inside a data sheet tab, I added four different types of statuses. We have a pending because an expense could be pending. We have an app approved expense, we have a processed expense by the HR, and we have an expense that can also be rejected. And the final table that I have is the user roles table. Once again, my field name is user role. And inside the data sheet tab, I added these four values because our application is going to have an admin level user, manager, employee, and HR.
When you’re done adding all four of your lookup tables, you should now be able to see a list of six tables. And once you have all of your tables listed, the next step is to go into the relationship screen to define how your data is linked between your tables. Now this application has two main tables. We have a table of employees and we have a table of expenses. The other four tables, as I keep mentioning, are just lookup tables. So they’re not going to be related using a primary key and foreign key. In this case, we don’t even need to include it inside this main screen, but go ahead and just expand your table so that you can see all of your fields. And this is my table that contains all the expense fields. And the way these two tables are linked is we know that each employee can submit an expense and we want to be able to track which employee is submitting that expense.
So all you need to do is drag the employee ID from the employee table and hover over the employee ID from the expenses table. And when you let go, you will see that Caspio defined that relationship as a one to many, meaning that a single employee can be linked to multiple expenses. Now you have additional settings here that you can enable, but in this use case, we’re going to bypass these and just simply click on create. And now you will see how a single employee can be linked to multiple expenses. Notice we also have a field called processed by manager ID. In this table, we have employees, we have managers, we have admins, and we have hr, and even the managers will have their own unique ID. So what you want to do is stamp the employee ID into the manager ID field, because once a manager processes the request, we want to stamp that manager’s ID into the expenses table so that we know which manager approved the expense or rejected the expense.
I also have HR ID. So once again, we’re going to grab the employee ID of the HR staff members and stamp it into this field as well. What that basically means is when HR is reviewing the request, we want to be able to approve or reject that request as HR. So this is the reason why we also want to stamp the employee ID into this table as well. And this is all you have to do. Most sophisticated database applications could have 4, 5, 10, 15 tables. And once you create these lines between your primary and foreign keys, it tends to look like a giant spider web. That’s usually how I describe it. So this concludes our video on how to set up the tables and how to define our relationships between the tables. I hope that you enjoyed it, and I hope to see you in the next video where I teach you how to set up your login screen and how each user is redirected based on their user role within the table. I’ll see you there.
Share this post: