Build a Contact Management System

Video Synopsis

• First Steps
• User Table
• Adding Users
• Contacts Table
• History Logs Table
• Define the Relationship using Primary and Foreign Keys
• The Views

Speaker

Ned Pajic, Technical Evangelist, Caspio

• linkedin 

Transcript

Ned Pajic:Hi, welcome to part two of this video guide on how to build a contact management application with Caspio. In this video, we will learn how to create database tables, relationships using primary and foreign keys, and views to filter data from tables. Let’s take a look once you are logged inside Caspio you are going to be taken to the console where you can build and manage applications. To begin building your very first application all you need to do is click on this link new app, and then you are going to see this prompt on how you can begin. You can start by importing data. If you have some data offline in an Excel format or maybe access database, you can bring that data into Caspio and then build your applications on top of your data. We are not going to do that today in this video guide. I am going to build this application from a blank template.

First Steps

Let’s begin this way and the next step is very easy. You just have to give your application a name. You are going to have your own naming convention. It really just depends on what type of application you are hoping to develop. If you need to build a CRM, you can call it CRM. If you need to manage inventory, you can call it inventory management, and so on and so forth. The application that we are going to develop is called contact management. From here all you need to do is click on finish to save your changes. Then you should be able to see that application listed on your homepage. From here, you need to click on open. As soon as you open up the application, you are going to see this panel on the left-hand side. These are the objects that you will be using to build the application that you are currently in.

We are currently inside this application called contact management. By default, you are always going to be taken to the overview screen every time you open up the application. This window here is mostly informational for you to keep track of your progress and add some notes. For example, I can add a note to say today you are going to build our first app in Caspio. Then here I can save my note. You can also leave notes for other employees that are logging into your Caspio account. Caspio gives you unlimited developer seats. You can have more than one person logged in simultaneously to help you build and manage applications. You can do so by going under account and clicking on access permissions. This is very helpful. If you have other people in your department or maybe you have an IT team overseas who needs to access your account. You do not want to share your own credentials.

You can just add them as a user and now they can log into your account to help you build and manage these applications. Right here in the middle, you see some metrics as well. Here we can track number of tables that our application has, number forms and reports that we have created, views, users, records, files, and also storage space. The first place where you want to begin building your Caspio application is always going to be the table’s object. Tables are the foundation of any app that you develop inside Caspio. This is where all of your data is going to reside. This specific application that we are creating is going to have three different tables. It’s going to have a user table, a table that’s going to store all the contacts, and a history log table. So let’s very quickly build all three of these tables.

User Table

I am going to click on the link here at the top, new table. What you need to do next is list all the fields that your table is going to have. The first field that you should always add to your table is a unique field. Some unique identifier that’s going to identify each row uniquely inside your table. I am going to start with the user table. I am going to call this field User_ID. Under data type, you are going to be able to see a lot of options on how you can modify that specific field in terms of what kind of data you want to capture in that field. If you choose text 255, that means you can store up to 255 characters inside that field. It’s usually helpful if you have like a first name, last name, a field that doesn’t require too much text.

If you go with text 64,000 in this field, you can actually add up to 64,000 characters. If you have a comments field description, maybe you want to paste a cover letter, text 64,000 will usually suffice. If you go down a little bit more you are going to find that we give you four different ID types in Caspio. I am going to choose Random_ID and you can see how Caspio immediately flagged that is a unique field. This is going to automatically generate an ID each time we add an entry to this table. You will see what that looks like in just a few minutes. Let me add the remaining fields first. Then I’ll explain why I am making all the changes. Let’s have the name.

We need to have a name inside our table. That’s self-explanatory. We need to be able to capture the name of each employee. For my email field, we are going to flag that as a unique field as well because you are never going to have two different employees with the same email. Emails are always unique. For my password field, we have a special data type called password. What it does is it encrypts the password on a table level. When you open up the table, you are not going to be able to read the password. It’s going to be completely encrypted. Again, you will see what that looks like in just a minute. We need to have a role field inside a table because this field is going to be used to differentiate our managers from our employees. For my Login_Status we are going to go with a checkbox. It’s a yes/no.

If you watch the first part of this video guide, when I did my demo of the application, I was able to use a checkbox method to make employees active or inactive. This is the whole purpose of this field to be able to quickly make users inactive so they are not able to log into the application. Last but not least, we have Date_Created. I am going to flag this as a timestamp so that we can automatically stamp the date and time when we add a user to this table. Now you can have other attribute fields here as well if you need to. Again, it just depends on what kind of information you want to track. You could also have title, department, phone number, and address, it’s completely up to you. When you are done, all you need to do is click save and let’s give this table a name. I am going to call this cm_tbl_users.

Adding Users

There’s my very first table. I now need to build two additional tables, but before I do that, I am going to open up my user’s table and very quickly add three different users. So later on when we build the application interfaces we have some information to test the application with. Let me quickly add just a couple of users here. Let’s start with maybe Sarah Smith, sarah@company.com. I am going to make this email field expand this a little bit so we can see the email, password, role manager, and active status. When I click on the row below you will see how the timestamp stamped the date and time. Let me quickly add two more users to my table.

Now my table has three different users. You do not have to add in directly inside a table the way I did. You can actually build a form later on to accomplish the same thing. Just by looking at our data, you can see that every single user in my table has a unique ID. That’s only going to belong to that specific user. We have the names. All the emails are different, they are unique. Because we used a password data type on this field, you can see how all the passwords are now encrypted. Because we used the role field to differentiate our managers and employees. You can see now in my table, how based on that role field, we know who the manager is, and we know who our two employees are. At the moment they are all active, which means they are going to be able to log in. We know when they were added to the table because we are using a timestamp. Let’s go back out to the table’s menu and let’s build the remaining two tables very quickly.

Contacts Table

For my second table, we are going to build a contacts table. Now this table is going to track all the contact information. Once again, your very first field in the table needs to be some kind of a unique ID. And I am going to call that Contact_ID. I will use random ID once again. Very simple. Let me list down all the fields in my table first. Then I’ll explain why I am adding the fields that I am adding. I need a User_ID field here.

All right. In this table, I have a User_ID field. I need to have a foreign key inside this field because what we are going to ultimately hope to accomplish is when the user adds a contact to the application, we want to be able to stamp that user’s ID inside the contacts table. This is how we are going to know which user is adding the contacts. For my full name field, I am going to change this into a formula data type because I want to concatenate both the first and last name into its own field. It’s very helpful to have the first name as its own field and last name as its own field. We also want full name to combine both of those data values into one field because when you are sending an email or SMS alert, sometimes you just want to use the first name. When you are looking at reports you want to be able to see both the first and last name because you could have multiple people with the same first name.

It’s not going to be unique enough. Once you choose the formula data type over here to the right you can click on edit. Inside this window, Caspio gives you a lot of different combinations that you can use for your calculations. We give you a lot of math functions. For those of you who have an Excel background, this is going to look very familiar to you. You can also do text for string manipulation, date, and time, and finally, you can insert the fields from the table that you wish to use as parameters. In this case, I am trying to join the first and last name together. All I need to do is insert the first name, put the last name next to the first name like this, and inside this window a little bit of syntax is needed here. You cannot just add space between the first and last name because that’s going to give you an invalid formula. So to create that space we need to use a plus sign, apostrophe, space, one more apostrophe sign, and another plus sign (+’ ‘+). This is how you create a space. 

When you verify the formula, it looks good. Hit apply to save your changes. The only two other changes that I want to make to this table is the text 255 for my date fields. I want that to be a timestamp. When you are done, the last step is to save the table. Let’s give it a name, cm_tbl_contacts. What’s going to happen is when the users log in later on, you will see how we do this in part four of this video guide. When users log in and they wish to add a contact, we are going to be stamping the user’s ID from this table into the contacts table. This is how you relate the users to contacts using the primary key and the foreign key.

History Logs Table

The final table that I want to build in this application is the one that’s going to store all the history logs. We build a brand new table here. Let’s list all the fields. The very first field that’s mandatory that you should add to every single table that you will create is always going to be the primary key (the unique identifier). I am going to call that field Log_ID inside this table. I am going to use Random_ID once again. Now in this table, we want to be able to track two foreign keys. I want to be able to track the Contact_ID because I need to know which contact this log belongs to. It’s a one-to-many relationship type. Each contact is going to be linked to multiple logs. Let’s say you are dealing with somebody whose name is Mike and you have had multiple conversations with Mike. You want to be able to stamp Mike’s ID into the log table so that you can track the history of all of the conversations. The second foreign key that I need inside this table is a User_ID. Because I want to know which one of my employees is adding this log. What are the other fields that this table needs to have? Let’s have Date_Added and I can quickly change this into a timestamp.

Let’s also have Log_Type. Log_Type is going to be a very simple dropdown that allows me to select if we had a phone conversation, email, or some other method. Last but not least, we have the comment itself, and let’s change this to text 64,000 because we want to be able to store up to 64,000 characters. Now, let’s say you also had an attachment that you want to be able to submit with that log. For example, let’s say the customer emailed you back the invoice, a signed contract, or some other type of document. You could actually add that field to this table as well. Let’s call it Document. For my data type, we have something that’s called File. This allows you to attach a document on the form and add that document to a database. When done, save your table. Let’s call it cm_tbl_logs, and click finish to save your changes.

Define the Relationship using Primary and Foreign Keys

Now we have all three of our tables created. What is the next step? The next step for building our application is to define the relationships using primary and foreign keys. To do that in Caspio you are going to go into the relationship screen. For those of you who have an access background or an understanding of how traditional database systems work, this screen is going to look very familiar to you. This is where we can define our schema and understand how our tables are linked using the primary keys and foreign keys. These are my tables inside the application. I am going to include all of my tables inside this window and then you can move these tables around however you want. It’s completely up to you. You can also make tables expand or you can collapse the table. Looking at our tables, how do we link them together?

New Speaker:

If we know that when the user logs in, we want to be able to stamp the user’s ID in the contacts table so that we know which user is adding the contact. When you drag this line from one table to the other, you let go, you are going to get this popup window and Caspio immediately identified that as a one-to-many relationship. Something else that you can do here is, let’s say, you wanted to build a report based on this table, but you want to be able to see the user’s name instead of the ID. You can choose Display Value Name and you can display that value on the data page. Now, when you click on create, you will see that line between those two tables. One user can be linked to multiple contacts. Same thing with these two tables. One contact can be linked to multiple logs.

Drag the live from Contact_ID to Contact_ID, let go. It’s also a one-to-many relationship. I am just going to choose the name, which is the full name of the contact and display that on the data page as well. Click on create. If later on I build my report based on this table, I want to swap out the ID with the name from the parent table. There’s one more connection that we can make here. I can just move this table down and drag the User_ID field to User_ID so that we know which user is creating the log. Let go. Once again, I want to be able to display the name of my user on the data page and click on create. For this application, this is the database structure. We have three tables. We understand the relationships.

The Views

We have three one-to-many relationships here. One user linked to many contacts. One contact linked to many logs and one user linked to many logs. Three one-to-many relationships. Let’s save our layout and let’s move on to the last topic of this video, which is the views. We want to be able to filter out the users based on the role field and the account status or login status checkbox. We are going to go down to views and let’s set up our first view. All I want this view to be able to do is let’s call it cm_filter_active_managers. That’s all I want the view to do is to filter out the users that are active and then have a role equal to manager. We move the user’s table to the right. This is the table that we need to use. We click next and then using the criteria tab, you are going to see these drag-and-drop elements that you can snap in place like Lego pieces. Like this.

Depending on how you define these pieces that you snap in place, this is how you are going to tell the view what information you want to see from that user’s table that we selected in the previous screen. If you want to start over you can click on the reset link or you can just drag and drop over the trashcan. Which is more fun if you ask me. This is the original anchor, you can never move this. One always has to be in place. Now, how do I set this up to only filter active managers? I move the field element. I select my field role and I want that role field to equal to manager. Since I am looking for active managers, I need one more field element. I need my login status field to be selected. I want that to be checked. If both of these two conditions are true, if role equals to manager. If the login status is checked, once I click finish, this view is going to filter out just Sarah. Let’s set up the second view to filter out our employees who happen to be Raj and Kelly. We go back out to the views object, click on New View. Let’s call this cm_filter_active_employees. From the same table, move that to the right, click next once again in the criteria tab. Hopefully, by now you know how to set this up. I need my role field to equal to employee this time. I need my login status to be checked.

Let me click finish here. Let’s open the view and there you have it. The last view that I need to create is to filter everyone. The reason why we do this is sometimes Raj and Kelly will have access to certain data pages, certain forms, and reports to be able to see different functionality. The reason why I have the view that’s filtering managers is because managers are going to have access to certain functionality. The last few that I am about to create, filters everyone. Let’s say everyone has access to be able to view the logs or be able to submit the form. I want all three of my users to have access to that. Now we are using views to limit who is going to have access to what data and what functionality. Let’s set up that very last view. Click here at the top.

Let’s call this cm_filter_all_active. Same table. Next. This is actually a very simple configuration. We just need one field element. We do not care about the role field because we want to be able to filter everyone from that table. The only thing that we care about is that they are active. When I click finish you will see how the view filters all of my users because they are all marked as active inside the main table. In Caspio we use the views to filter out based on a different role. This is telling Caspio you are going to give certain users access to some functionality and some data at a later time. You are going to see how we do that in part four of this video guide. Thanks for watching part two. I hope that you enjoyed it. I hope I will see you on the next video where I teach you how to set up the login screens for each one of these user groups. Thanks for watching. And I will see you there.