The Caspio Low-Code Platform
All the Tools and Support You Need
Unlimited Users With Every Plan
Ned Pajic: Welcome back to this video guide on how to build a simple covid test tracking application with Caspio’s low-code, no code platform. In the second video, we are going to take a look at how we can build all the tables needed for our application. Let’s go to Caspio and let’s see how we can do that.
Once you are logged inside your account, the first thing that you need to do is set up a new application and you can begin building your application by importing data from Excel or access database if you have some data offline. Today, we are going to build our application from a blank template. We are going to give our application a name, let’s call it covid testing application. We are going to hit finish. Then from here, let’s open up the container and go directly into our tables.
Tables are the heart of any application you develop in Caspio. Tables are always going to be the starting point. That is where all of our data is going to reside. This application that we are building today is going to require four different tables. We are going to need a table of users, table of patients, tests, and we are going to have a very simple lookup table that is going to have all of our statuses.
Let’s begin by building our user table First. At the beginning of your list, always add your primary key. In this table, we are going to add a user ID field. For my data type, I am going to choose random ID. It is automatically going to generate theID. For every user that we add to this table in a database application, it is commonly referred to as the primary key. What other fields might we need inside a user table? We might need first name, let’s have last name, let’s have full name.
Let’s also have the email field and password field. Now you might have other fields inside this table. It just depends on what type of information you want to collect for your internal employees. For my password field, we are going to make this a password data type. It is going to encrypt the password on the table level. Email field is going to be a unique field because you are never going to have two different users with the same email. Emails are always unique. We are going to have the first name by itself, last name by itself. What I would like to do is concatenate both the first and last name together in its own field called full name. To do something like that, we are going to change the text 255 into a formula data type. When you select the formula data type, we are going to click on the edit link to the right, and inside is a popup window.
You can add math functions, text functions, date and time. And very simply add all of your fields that you wish to combine and use inside the equation. For us today, it is a very simple one. We want the first name, we want the last name next to it. A little bit of syntax is needed to create the space. You can not just add a space that is going to give you an invalid formula. The syntax that we need here is a plus sign, apostrophe, space apostrophe, and a plus sign. If you verify the formula, again, you are going to see a valid formula. Click apply. Now, let’s save our table. Let’s call this table covid underscore test users. Hit finish.
Here is my very first table. What I will do at this point, I am going to actually add some fictitious data to my table very quickly. Then when we build the application later on, we are going to have some sample data that we can test the application with. Very quickly here, we are going to say, John Doe email, johndoe@ company.com. Let me expand that so you can see the column. Our password can be test something simple for now. When you click underneath John Doe’s name, you are going to see how the formula field or the formula data type concatenated both of those two values together for us automatically. Now on this table, John has its own unique ID. We have his name, we have the email, and you can see how the password is going to be encrypted.
Let’s develop our second table. The second table, we are going to store our patient information. Again, let’s begin with the primary key. We are going to call this patient ID. Once again, I am going to use random ID as my data type. And let’s repeat some of the same steps from the previous table. We are going to need first name, last name, full name. Let’s also have email password. What other information might we want to have for our patients? Let’s have account login or account status. Last but not least, let’s also have date register.
Once again, just repeat the same steps for full name. We want to use a formula data type. We are going to click edit and just include the first name of the patient, put the last name next to it, and let’s add our little bit of syntax, syntax here, plus apostrophe, space, apostrophe, and a plus sign. We will hit apply. Email field is going to be unique. Password field is going to have a password data type.Then for account status, we are actually going to use the data type, yes or no. It is a check box. It is going to allow users to make patients active or inactive. If you no longer want the patient to log into the application, instead of deleting them from the table, you can uncheck the box and that patient will no longer be able to log in. For date registered, why don’t we have timestamp so that we can track the date and time when that patient signed up for the account. Let’s save our table and let’s give it a name. We are going to have the same naming convention, covid test patients. One table is going to store the users and one table is going to store the patients.
The next table that I am going to create is the one that is going to store all of the test results. Let’s have the new table. First field is going to be order ID, random ID. Next is going to be patient ID because we need to be able to track all of the orders for each patient because one patient can go in and do multiple tests and it is going to have a one to many relationship. We need to link our orders, our tests, back to our patients. That is going to have a text 255 data type. Let’s do test type. We can specify what type of a covid test this is going to be. Let’s have status of the test if it is negative, positive, or still pending. Let’s have date tested. Let’s have completion date, and ordered by ID. Okay, so test type is going to be text 255 status. Also the same thing. Date tested is going to be date and time completion. Date is going to be date and time. Ordered by ID is going to be text 255. I need to know which user ordered the test for what patient. Let’s save our table covid test orders.
The final table is going to be a very simple table, just a look up table that is going to display all of the statuses for our tests. Let’s create a new table. I will use a very simple ID field here, and we can choose the data type auto number for that. And then for my second field, let’s have the status. Here that is going to be text 255. Let’s save the table covid test status lookup. Inside this lookup table, we are going to create all of our options. Let’s have pending, let’s have negative, and let’s have positive. We are going to have three different results. The test can be pending if it is still inside the lab and we are waiting for the result. Once the result comes back, it can either be negative or it can be positive. Okay, so let’s go back out to the tables and those are all of the tables that our application is going to need. Join me in the next video to learn how to build all of the logging screens and all of the authentications needed to pass or protect our application. I will see you in the next video.
Share this post: