Digitize Asset Management - Building App Interfaces (Part 4) | Caspio

Video Synopsis:


Ned Pajic, Technical Evangelist, Caspio. 


Ned Pajic:

Hi, welcome to this ultimate video guide on how to build an IT resource management or asset management application with Caspio secure an easy-to-use online database platform. My name is Ned, and in part four of this video guide, I will show you how to create all the forms, reports, charts, and some JavaScript, to redirect users based on their user role. In addition, we’ll take a look at a script that also changes the color of the status, depending on the selection. Let’s take a look. To begin let’s go over to data pages. Data pages are application interfaces that your end users are going to be interacting with. Before I show you how to create a data page, let’s go over folders. Folders are a great way to stay organized and how you keep track of all of your data pages. And the way I want you to use the folders is every time you have a user group in your application, you should create a folder for it.

So for example, we’ll have admin-level user, we’ll have managers, and we’re also going to have a low-level employee user. If you have some public-facing data pages that don’t require a login interface, then go ahead and create a folder for that as well. In my root folder, I’m going to show you how to create our very first data page. And this data page is going to have a little bit of script to redirect the users based on the user role. So imagine if you’re already admin, I want to be redirected to a URL that contains all the forms and reports that only an admin-level user can see. Conversely, if I log in as a manager, I would like to be able to see different functionality that only pertains to managers. So let’s build our very first data page and we’re going to create an HTML data page, click next, give this data page a name.

Building Data Pages

Let’s simply just call it the login screen. And then you can use a different style for the aesthetics. This is for the look and feel of your applications to match, to look and feel of your brand. So we give you a couple of styles that you can select from, or you can fully customize your own style by going to this object at a later time. I’m going to go with blue and I’m also going to change the localization to English. This deals with regional settings, so you can change the date format, currency, et cetera. You also want to restrict access to all access login. These are the four authentications that we created in the prior video. But initially, when somebody logs in, we want to give everyone access via this login screen, but upon redirection, that’s where we’re going to apply a different authentication that corresponds to what somebody will be able to see.

Click next, go ahead and disable the HTML editor. The next thing that you want to do is add a line of script. I’m going to go ahead and paste it on my end. So what I want you to do is pause the video now and go ahead and copy this code that you see in front of you. A couple of modifications that you’ll have to make is you’ll have to replace this field. If it’s not the same field as I used in the video, you’re going to have to go ahead and delete that field in between the quotes and insert your very own field access level. And then once you selected click, okay. Now I’m going to go back and just bring my own field. Notice what this basically means, if you’re the admin of the application, I’m going to redirect you to the CR call admin slash dashboard dot HTML.

Admin, Manager and Employee Folders

Now, if we look inside of my directory of all my web pages, you’ll notice that I have three folders here. I have the admin folder, the employee folder, and also the manager folder. I also have my login page where eventually we’re going to deploy our login screen. But as soon as the admin logs in, we go inside this folder noted here by admin and to dashboard dot HTML, that’s my destination URL as soon as the admin logs in. Else, if a manager user level logs in, we want them to go to manager slash all dot HTML. So go back and then click on the manager folder. And you’ll see this page called all dot HTML. And finally, if you’re an employee-level user, we go to the employee folder and to this page called, assigned to me where you can note that over here inside my code.

So essentially that’s what this script is doing, depending on who you are in your user role, you’re going to be taken to a different URL on the website. Once you’re done inputting the script from here, you just need to click on finish. And here’s my very first data page, and all this data page is going to do is redirect the users upon login. In this video, I’m going to show you how to deploy four data pages, just so that you can get the feel for how data pages are deployed onto a website. And in the final video of this guide, I’m going to show you how to deploy the remaining data pages. Let’s go ahead and put this login screen onto our website. I have a sample webpage created, and I want to position that login screen underneath my login to continue header. So let’s go back to Caspio, click on deploy, make sure you enable deployment status, and then copy the Caspio code.

How to Deploy Data Pages

Open up your HTML document. I have my login dot HTML page opened up in notepad-plus-plus. For those of you who don’t have a web background and don’t know how to build a website using HTML, there are many tools available nowadays that are compatible with Caspio. So if you’re using GoDaddy or Weebly, Yola, 1&1, Webflow, even Squarespace. All of those CMS providers are compatible with Caspio, and you can deploy the Caspio code into one of those easy to use drag and drop website builders. Now, when you find your placeholder, just replace the text with the Caspio deploy code, save your changes. And once you publish your website, let’s take a look and refresh. You should be able to see that login screen now seamlessly embedded. Now, remember, there’s a script that’s running behind the scenes here. So when I log in as a specific user, it’s going to redirect me to that specific URL.

So let’s go ahead and log in as an employee. In this case, that happens to be Shelly B at company.com password test and upon login. It should take me to a webpage titled assigned to me dot HTML, and immediately you see this directory employee and that webpage called assigned to me dot HTML. So this is the dashboard that the employees will be able to see. And from here, what we’re going to do is build the remaining data pages and deploy them onto these web pages. Let’s go back to Caspio, close this screen, and let’s build a data page that’s going to welcome the user once they log in. So imagine when you log into your portal, you get to see welcome and then your name so that you know exactly who it is that’s logged in. And that’s an easy data page to build. Just click on new data page link here at the top.

Building a Welcome Data Page

We’re also going to be using the HTML data page for that as well. Click next; let’s give this a name, welcome name. We’re going to use the same style, blue and same localization of English, and we want to restrict access to everyone. Click next, and the only thing you have to do here, I always like to disable the HTML editor, and then all you need to do is insert the authentication field, full name, click okay, and click finish. What this is going to allow you to do is every time a user is authenticated or logged in, it’s going to display their name. So let’s go ahead and deploy this data page. Same way as before, grab the deploy code, open up our HTML documents. And then, let’s go ahead and repeat the process for all three of these web pages for change password. Paste; make sure you remove the branding as well so that you don’t see that Caspio online database by Caspio branding. Profile page, same thing, paste.

Let’s go ahead and remove this branding once again and save that page. And also the very last page assigned to me. Go ahead and paste and just remove the two div tags that are underneath the script and then save. And let’s take a look. Now, now when I refresh my website up here in the upper right corner of my page, I should be able to see welcome Shelly Boil, but that’s also repeated on all the other web pages. So if I go to profile, you’ll see the same thing. If I go to change password, once again, same thing. So the next data page that I want to build is the ability for employees to be able to update their password. So let’s go back to Caspio, and inside the employee folder now, we’re going to build our new data page.

Employees to Change Password Data Page

And this time, I want to build a single record update. This interface is going to allow the end users to update their password. Click next; let’s use the employee’s table because that’s where all the credentials reside, along with the password. Let’s give it a name, update password. Once again, I’m going to use the blue style and English localization and restrict access to everyone because every single user will be able to update their password. That includes admin-level user, manager, and also employee. In order for the user to find their password, we have to enable record-level security. And we do this using an employee ID to employee ID. To verify that the user that’s logged in it’s matching their unique ID. So once they log in, it’s only displaying the information pertaining to that specific user’s ID. Click next, and the only field that we want to update is the password field.

So let’s move that to the right. Click next, and then you have some settings here on how you can configure the password field. It’s going to be a required field. You can show the password strength if you want. You can have a minimum strength required if you need a more complex and more complicated password. I’m not going to do that. Let’s just go ahead and save these changes as is, click finish. And here’s my third data page. All I have to do now is click on deploy, enable deployment status, grab my Caspio code, go into my HTML document once again, which I call change password, scroll down where I find my placeholder and replace this text, save it. And let’s go to our website now to see what that looks like refresh. And here’s the data page that allows the employees to update their password.

Building Data Page for User Profile

Next, let’s go ahead and build a data page that allows users to be able to see their profile. Once again, go back to data pages, click on new data page. I’m going to go to forms and click on single record update, click next. Once again, my data source needs to be my employee’s table, and we’re going to call this view profile. I’ll use my same style and same localization and restrict access to, once again, everyone. Because every user will be able to look at their profile, click next. Once again, we want to find our profile through a unique ID, which is the employee ID. Click next, and now what information do you want the end users to be able to view or edit? We want them to be able to see their first and last name. We want them to be able to see their email, title, department location.

And that’s about all the information I want to display. Click next, and I’m going to leave all of my fields display only, in other words, read-only. If you want the end user to be able to update their name, you can change that to a text field or any of the other form elements that we provide. Once you’re done, go ahead and click finish to save your changes. And here’s your fourth data page. From here, click on deploy, enable deployment status. Once again, copy the Caspio code. Go into your HTML document. Here’s my profile page. Scroll down to your placeholder, replace this text, save, and let’s see. I should now be able to view Shelly’s profile. And now, she’s able to see all of the information that pertains to her unique profile. The last data page that I’m going to build and show you how to deploy on the employee side of things is the ability for them to be able to see all the items or assets that have been assigned to them by either a manager or an admin-level user.

Building Data Page for Employees to see Assigned Items

So let’s build that data page. Once again, under the employee folder because everyone’s going to be able to see what items are assigned to them. We want to go to reports, tabular format, click next. The data source for this data page needs to be the inventory table because we want to know what item is assigned to that specific user. Let’s give this a name. We’re just going to call this assigned to me. Again, I’m going to use the same style and same localization, and I want to restrict access to everyone. Click next. I don’t have a need to search all of these assets because I’m not only going to have maybe five or six items assigned to me. So there’s really no reason to build a search interface. Let’s go out and filter the data. Let’s enable record-level security because we need to find the items that are assigned to us.

We’re going to use the employee ID in the field and the authentication. And then, you also have a field in the current data source, which is the inventory table. And you want to select assigned to ID because once these two IDs are matching up correctly, then you’re going to be able to see all the items that are assigned to that specific user. Click next, we’re not going to filter based on any one of these fields, and the information that we want listed in the results page is the device type, manufacturer, and also model. We don’t really care for any of the other fields. We just want to be able to see this information in order to view what items are assigned to our specific location. Next, we’re not going to have the ability to edit. We’re not going to make any changes on the results page fields. Let’s show 25 items per page, even though, more than likely, we will never exceed 25 items. And we can go ahead and disable the details page and click finish.

Final Data Page

Let’s deploy this final data page, enable status, copy the code, open up our HTML file. Find that placeholder replace it, and let’s take a look. You’ll notice that we don’t have any records because at the moment inside our inventory table, it’s an empty table. So it’s not pulling any information from it. So let’s take a look. I’ll open up my tables in a new tab, move that here to the left. Let’s open up the inventory table and you will see that it’s a blank table. There’s no information inside it. If I had some data inside this table, there was assigned to Shelly. Well then here on front end, Shelly will be able to see that information. And later on in the video, you will see how we populate the data in the table so that you can see all the records visually on the front end. For now, let’s keep it as is. And let’s continue building the remaining data pages for the admins, and also for managers. I’m also going to build an additional folder, which I forgot to build at the beginning of this video. One more folder for admins and managers.

I’m going to have some data pages that are going to be viewed by both. So I want to group those data pages inside this folder so that we can find them easier. Okay, the next thing that we’re going to do is build out all the data pages for the admin-user level. Once I build a few of these data pages, I will deploy them onto a website so that you can see what they look like. But before we build out a data page, let’s open up all the HTML files for the admin folder. So here I am inside my admin directory, and all you have to do is highlight these pages, right-click, and edit them in notepad-plus-plus. You can download this template. It’s going to be available in the description of the video if you wish to use this template. But you can also build out your own webpages if you don’t like the design that I’m using in this use case.

Admin able to Add New Inventory Items

So let’s open up all the pages, and as you can see, notepad-plus-plus grouped all the pages together in different tabs. The first data page that I will build is for the admin to be able to add new inventory items. So let’s go to Caspio; let’s go ahead and close the employee folder now. Open up the admin folder and click on new data page to build our input form. So that’s going to be a submission form we’re going to hit next. And we need to build this form on top of the inventory table. Because right now, we’re going to be adding items to this inventory table. So that needs to be my data source. Let’s call it, add new. And again, I’m going to use the same style and the same localization. Let’s restrict access, and this time not everyone is going to have the ability to add this item.

In this case, I want to use the admin login because the input form for the admin is going to be slightly different than the one that the manager is going to be able to see. This is the reason why we’re going to have to build two different input forms. If it was the same exact form for the admin and manager. In that case, we will be able to use admin manager login, but because they’re different, we’re going to use admin login; click next. For now, let’s go to include all the fields. The only field that we’re going to exclude here is last updated by. We don’t need to have this field inside the form, so let’s continue. And now, let’s configure this form. Couple of fields that I’m going to move it to very bottom are going to be my date-created field and also created by ID. I’m going to actually group these two fields inside their own section.

So above the created by ID, you want to insert a section, and then you’re going to see how both of these two now belong to section two. Created by ID, this is going to be a hidden field and we want to use the authentication field of the person that’s logged in. And to stamp that ID inside the inventory table. This is going to help us track who is the person that’s adding this inventory item. Date created is going to be a timestamp because we want to know when this item was created inside a table. Then I’m going to move the assigned two ID above section two and group that in its own section, like this. So now you can see how this field is inside its own section. Then I want the status and location to be in its own section. So let’s move the location down, and let’s go find a status field and move that down right above the location. For status

let’s go ahead and turn this into a dropdown. We’re going to use both custom values and look up table because, under the custom values, I’m going to say select status. Make sure you delete the value because you don’t want this to be stored in your table. And then, for the lookup table, we have a table that contains all the statuses. So simply just link your dropdown to that lookup table. And that’s all you have to do. Next is the location. Let’s do the same thing; we’re going to turn this into a dropdown. Once again, we’re going to use both because we want to say select location, for example, delete the value. And then lookup table, we have a table that contains all the locations. So simply just link that drop down to that lookup table. What you’re doing here, basically, as the admin-level user, when the inventory item arrives, admin should have privileges to be able to assign this item to whatever location that item has arrived to.

And now, for assigned to ID, this is actually going to turn into a cascading dropdown. The reason why this turns into a cascading dropdown is based on the location that we select. In this dropdown, we want to filter out the employees just for that specific location. The parent field is going to be the location field. We’re still using the employee table. We’re going to filter based on location, and we want to display the full name. But once you select the full name in the table, we want to store that as an ID. My status in my location are also going to be in their own section so let’s go to add one more section above. And now, how you rearrange these other fields, it’s completely up to you. What I’m going to do is simply just put all these fields in their own section like this. Put the label position on top of the fields and have it be in three different columns.

Applied Rules To Last Form

The last thing that we’re going to do to this form is apply some rules so that we can show in hide elements based on prior input. The first rule that I’m going to create is based on the status field. So let’s go out and include that conditioned to be on field name, status. And if the status does not equal assigned, which is one of our status options. What action do you want to perform? I would like to make a section completely hidden. In this case, that’s going to be section three because we don’t want to assign this to a specific user if the status does not equal assigned. Also, if the status does not equal Tempus, so once again, we select status does not equal Tempus, which stands for temporary use. Again, we want to make the section three hidden once again. And the second rule that I want to create here is if the status does equal assign.

So if it is assigned or if the status does equal to Tempus. We want to make sure that we require the assigned to ID. In other words, if the status equals assigned, or if the status is in Tempus, we definitely want to assign a specific employee to that item. And one thing that I forgot to do for rule number one we want to make sure we set it to end. So if there’s not equal a sign or does not equal to Tempus, we want to make sure that the section three is hidden. So let me show you how this form is going to behave now on the web. Once I click to save my changes, here’s my submission form. I’m going to go ahead and deploy this form now; click enable. Once again, we’re going to copy this code, and inside our HTML pages, here is the add new form. And all I’m going to do is find my placeholder same way as before. Paste my Caspio code.

Save it. And let’s go ahead and log in now as the admin. In this case, that happens to be Ken. And once I log in as Ken, you will see how it takes me into the dashboard. We haven’t created these other data pages yet. One thing that I want to make sure I explain here. Before you can actually see that data page, make sure you go back to your authentications. And for each of the authentications, click on edit, and there’s one important thing that you need to enable. Under the advanced settings, you want to make sure you check this box for cross-application login. Once you do that, then go ahead and click on save. I’m going to hit cancel, and now when you go back to your website when you go under inventory, and if I click on add new, you should now be able to see that submission form that allows the admin to input this inventory item.

We can fill out all these fields if you want to. And now you can see how the conditional logic works as well. If the status equals defective, you can see how we can still set the location, but if it equals to assigned, you can see how we can set the location, but you can also see how all the employees are cascading based on the prior input of location. If I set it to a different location, you can see different employees. Also, if the status equals the Tempuse, same thing happens. We can set our location and we can also choose a different employee to who we are assigning this item to. And this is how easy it is to build a form in Caspio. Now let’s go back and continue building the rest of the data pages for the admin-level user. The next data page that I’m going to build is a search interface for the admin to be able to quickly pull up all the inventory items.

So let’s build a new data page. We’ll go down to reports; click next. Your data source needs to be the inventory table. Let’s give it a name, click inventory search. Once again, I’m going to use a same style and a localization. We’ll need to restrict access to admin-level user; click next. Let’s go ahead and display the search above the results. This way, we can have a search interface and be able to quickly filter out data directly underneath that search form. So let’s continue. As for my search fields, I would like to include location, perhaps device type. Let’s have status assigned to ID, and let’s also have created by ID. And the last field that I’m going to include is the model. So these are all the different fields. I’ll be able to search my data on. Let’s continue; the first change that I would like to make is put all these fields on the same row.

As you can see, I have six different fields. So I’ll go up to section one and say the number of columns, six. And I would like to position the label on top of my fields. Now, most of these fields are going to be dropdowns, so let’s configure these dropdowns very quickly. For location, as I said, it’ll be a dropdown. Let’s have both because I would like to be able to search any location. Make sure you delete the value because this doesn’t exist in your table. And then, for the lookup table, all you need to do is link this dropdown to your location table. Go down to device type, same thing, and just repeat the steps. Search any device type, delete the value, and then link this to your device lookup table. Then we have status, same thing.

Search any status, and then link to your lookup table for all the statuses. Assign to ID is also a dropdown. You can see how it’s automatically configured for me. I didn’t have to make any changes because if you go into the relationship screen and your tables, and you define your relationship between your two fields. Caspio will wire this up for you automatically. The only change that you might want to make here is under source, select both. And under custom value, once again, search any and delete the value. But look up table is already linked to my employee’s table. It’s going to display the name of the employee, but behind the scenes, it’s always referencing the ID. Same thing with created by ID; let’s once again, do both. Search any, delete the value. And finally, we have model this is going to be auto-complete. Auto-complete works like Google search. If you type in a letter underneath that field, you’re going to see options that you can select. And let’s say it contains, so there looks for all the letters inside that whole entire string. Let’s continue; in my results page, I would like to include device type. Let’s have purchase date.

Let’s also have purchase price, location. Also, we’re going to have A assigned to ID and also created by ID, and also date created. So my results page, I would like to see some information about each one of these assets. And I would like to be able to see who this asset is assigned to, who initially created this inventory item in the application, and when it was created. Then let’s continue. We’re not going to have any editing options. Let’s move on. And now, in the results page, we’re going to have to make a few modifications. The very first field, I would like to position my location as the very first column. Then I’m going to include two HTML blocks. Just go ahead and insert two HTML blocks underneath the location field. In the very first HTML block, go ahead and go into the advanced tab and disable the HTML editor.

Creating a Hyperlink

And inside this window, you’re going to create a hyperlink to redirect a user to a different webpage. And in the midst of redirecting the user to a different webpage, you’re going to pass a unique ID that belongs to that inventory item. To write a hyperlink, you’re going to write href=, and for now, just put two quotes. Close the bracket, and then instead of giving your link a name, all I’m going to do is include a model field to become my clickable link, and I’m going to close my <a>. So basically, when you click on the model name, it’s going to take you to whatever destination URL you specify in between the quotes. And on my website, I call that page details dot HTML. And to pass the information into the URL, you’re going to add a question mark to initiate the string. Now my field in my table is called item ID.

I’m just going to abbreviate that as IID, which stands for item ID. This is my parameter name, and you can call this whatever you want. Then you add an equal sign, and then you’re going to insert that field that you wish to pass. In this case, it’s going to be the item ID, and you can see how Caspio adds that field in between the quotes. The reason why we pass the ID to the details page when we click on that model name is so that later on, we can actually link the history of that item. Anything that took place with that item, If somebody made a change to it, we want to be able to tie that item to all the changes. Next, going to your other HTML block. Once again, the HTML editor go back to the standard tab. And for right now, all I’m going to do is paste this long JavaScript code that you see.

It’s actually very easy to manipulate this. I’m going to explain how this is done, but first, let me explain the very first line. All I did here is insert the status to be displayed as a column inside my results page. So this is the field for status, and I’m basically assigning a unique ID to each row inside a results page, and I’m flagging it as row. And then, I insert the item ID as a field. So now this is going to be a unique identifier for each row. And all the script is going to do is if the status, so if the status field equals to assigned. I’m using some JavaScript here in order to apply a different aesthetic to that status. And you can see that my text is going to be color white, which is defined by FFFFF. The font-weight, which is how heavy the font is going to be.

I have 500, the background color. So behind my text, I’m going to have a specific color code for that. Then I’m also using some padding around the text. I have a border radius to be three pixels. Width is going to be automatic based on the width of the report. I also want to adjust the width of my status. Then display it as a block. I want the text to be aligned in the center font size to be 13 pixels. And I have some margin here applied as well. Let me give you a live example of what this is doing. I’m going to show you my live website. Let’s go ahead and log in. When I go over here to all, you’re going to see how we apply a different color to our status column. So that’s essentially what we’re doing with this JavaScript. Now, the easy way to do this, instead of you having to write all of this out, basically, if the status equals a sign, apply this color and all of these other attributes. And then all you have to do instead of rewriting this completely again, you just need to go ahead and copy this and paste it directly underneath.

And then the only changes you need to make is just to input else, if, and then change the status to in transit. And then, you apply a different background color. As you can see, you can also define that as actual text instead of using the code. And just continue copying and pasting. So if status equals available, apply a different background color. Conversely, if status equals defective, you may want to apply a background, color of red, and so on and so forth. Just continue to copy and paste until you have all of your statuses with different colors. At the end, close your script tag, just the way I have it here. Now, depending on the status, you’re going to be applying a different color and also some different attributes to your status.

Next thing I’ll do is I’ll just move and reposition some of my other fields. Perhaps assigned to can be underneath my HTML block or underneath that status. Then we’ll have device type that’s okay. Purchase date. I thought I included the purchase price as well; maybe I didn’t. Let me go back two screens and include the purchase price as well inside my results page. And I will move the purchase price directly underneath the purchase date. Then we can have created by ID, date created, and let’s also do some calculation here by inserting a totals and aggregation. And inside this, we’re going to include purchase price to the right as our selected field. Then all I’m going to do is apply a sum function to the purchase price so that we can get the grand total underneath all of our results. Let’s continue. Let’s go ahead and display 500 records per page; continue.

Inserting Data into the Inventory Table

And then, I’m just going to go ahead and disable the details page and click finish to save my changes. Now, if I were to deploy this data page to my website, I’m not going to do that, I will continue building the rest of the data pages, but if I click on preview and we log in as Ken, who happens to be our admin-level user. You can see how we have this nice ability now to search based on each one of these fields. I can also input the model, and then when I click search, the results underneath will filter. Now, remember we don’t have any data inside the inventory table. So let’s fix that very quickly so that you can see how this works. So here is our website that we started developing. Here is my add new form. So let’s quickly add some data inside this form.

We’ll just say for device type; we can say iPhone, Apple, model seven, maybe some random serial number. It’s not a CPU. There’s no Ram storage space. Maybe 32 gigabytes. Screen size purchase date can be today. Purchase price can be 7.99, no remarks, recovery key if you have one. And for status, let’s go ahead and have this be assigned. Set the location to maybe Toronto and then assigned this to Shelley Boil. Let’s click submit. Your submission was successful. Now in that preview, when you click search, we should be able to see that very first entry that’s being pulled from the inventory table. And you can see that JavaScript, the color that I used. You can now see how that item has been assigned to this specific employee. And we have some basic information that pertains to that inventory item. Later on, once we deploy this data page into our website, you will see how it’s; it’s going to be nicely positioned on this webpage, where at the moment, I don’t have any content. But eventually, this preview that you see on this tab will be nicely embedded inside this window.

Building a More Advanced Search

And it’ll look exactly like this example right over here. All right, let’s close the preview now, and let’s continue building the other data pages. This time, what I’ll do is I’ll build a more advanced search. And instead of now building a brand new data page from scratch, all I need to do is make a copy of the one that I just created. And I will call advanced inventory search because sometimes you may want to have the ability to search based on more fields. The one that we just created is a very quick search interface that only gives me access to maybe four or five fields. But this time, what you’ll notice is when I edit this duplicate, we already created all of these steps. Let’s continue, continue. And this time, I’m going to have my search and results in two separate screens so that we can include more search fields.

And aside from the ones that we have selected in the prior data page, now I’m going to include additional fields. So the easy way to do this is to include all the fields initially and then exclude the ones that you don’t want to search on. So, for example, item ID, I don’t want to search on that. I don’t want to search on the remarks field last updated by ID and last updated. All the other fields we can now search on. So let’s continue. And then what you need to do is very simple is just go ahead and configure the rest of your field in order to be able to search your data. You can see that the location is already configured device type status assigned to created by ID and model. They are already configured because all we did was made a copy of the prior data page, and we don’t have to make those changes again.

Now we just need to modify the rest of these fields, and I’m going to lead the modification to you. If you don’t want to bother with modifying your own search fields, you can just download this application and you can see how it was created. But what I will do is just; click next. Inside my results page, I’m going to keep the results the same. Remember how we have the HTML blocks? We also have some calculations, so I’m not going to make any changes to my results page; click next. Let’s leave this the way it is. And once again, we’re going to say no details page and click finish. Once again, if I hit preview, what you’ll notice now is we have a search interface. But I am going to modify my search fields because I would like all of these fields to be in a single column.

So let me close the preview. Edit my advanced inventory search. And all I’m going to do for this section, instead of having six columns, I just want a single column, and I want the labels to be on the left. Click finish again. And now, when I preview, you will see how all the search fields are going to be positioned to the left in a single column. Just like in my live example that you see here on the search, here are all the fields. Again, I’m using three different columns, I guess, for my search interface that’s already live. If I wanted to replicate the same exact behavior, I can go ahead and do that by going to my data page once again for the third time, hopefully for the last time. And under section one, we can just say three columns with all the labels on top; click on finish.

Building a Pivot Table

And now, when I hit preview again, you will see what it looks like, which is very close to what my live example looks like. Okay, let’s close the preview, and let’s continue building the rest of our data pages. This time I’m going to build a pivot table so that we can see all of our data in aggregate across locations and also for different statuses. And once again, all I need to do here is make a duplicate of the existing data page and let’s give it a name. We’re going to call this pivot report, click save, click, edit. By duplicating these data pages, you’re saving yourself a lot of time, you don’t have to build these data pages from scratch. Because if the layout in how you position your fields is going to look very close to what you’re building next, just make a copy of your data page and make a few tweaks. So pivot table, click next. I don’t need to make any changes here. Let’s continue. Let’s filter data because I don’t need to see a search interface; click next. I’m not going to filter based on any one of my fields. And now, let’s configure this pivot table. For my columns, I would like to have status, so let’s find a status field. For my rows, I would like to display locations.

And for my value, you can actually include any one of these fields if you want. I’m just going to do a device type, and I’m going to do account. And I’m going to format this as a number, and I don’t really have a need for any digits after the decimal. So I’m just going to set that to zero and click, okay. Next, you can choose to display your grand totals in the row and also in the columns. That’s completely up to you. I’m just going to go ahead and disable that for now. And let’s also be able to drill down on each one of the items inside the pivot; click next. And you can see how I don’t have to configure my results page because I made a copy of the prior data page. Now everything is already configured for me. Once again, it includes both of those HTML blocks and all the other information that we had before. From here, we can click on finish to save our changes.

And now, when I preview this data page, you will see a pivot table, but it’s only listing one location. And you can see how many items are assigned into that specific location. Once you click on that one, we can drill in, and we can now further see who that item is assigned to and what location. Who it was created by and all the other information. If you want to go back to the pivot table, click on the link again. And once again, you’re back into pivot. If you want to see what this looks like as a live example. Here on this page, under the dashboard, here is my pivot table. So eventually, it’s going to look something like this, where we can track, based on location, all the different statuses and how many items are in each location based on status. Next, let’s build these four charts that are going to display our data in a visual manner.

Building Charts

So let’s begin with the inventory by status. So I’m going to close my preview, go back to data pages and click on new data page. Okay, so let’s go down to charts, and let’s go and find a pie chart. So here it is; click next. I need to select my inventory table as my data source. And let’s give this a name; inventory by status. I’m going to use the same style blue. Same localization, once again. And let’s restrict access to our admin and click next. No need to search data; let’s filter data. I’m not going to filter based on any fields because I would like to be able to see all the data from the table inside my pie chart. Let’s continue. And now, let’s go ahead and configure this chart. For category, I’m going to go ahead and include status, and for value, we’re going to go with device type, and all I’m going to do is select count.

And I would like to count how many items instead of seeing a percentage; this is completely up to you. At this point, you can even click preview to see what this looks like, and you can see how we have one item that’s assigned based on status because there’s only one row inside the inventory table. Imagine if you had additional data inside that table. Well, now this pie chart would reflect that. You will be able to see different items based on different status. Let’s close the preview and let’s save our changes. And there’s your chart. The next chart that we’re going to build is inventory by device. Once again, that chart is going to be very similar to the one inventory by status. So all you need to do is make it duplicate, and let’s call this inventory by device type, click on save. Let’s edit the chart to make a few modifications.

And all you need to do is just skip forward until you get the chart settings and for your category, what you need to do is select device type. You can lead the value as is. We’re going to just count how many device types we have. If you click on preview, what you’re going to notice now is that you have one iPhone, which displays from that inventory table once again. Close the tab, click on finish to save your changes, and here’s your 11th data page. We have two additional charts to build for the admin users, so let’s go back to this tab. Let’s now create a bar chart, which is inventory by location. Back to Caspio, click on new data page, select charts, bar chart; click next. Once again, that’s going to be the inventory table, items by location. Use the same style, same localization, restrict access to admin level users, and click next. No need to search.

Let’s filter data. We’re not filtering on any field. And now, let’s configure this chart. For data source settings. We’re going to use separate fields for each series. For your Y-axis, you want to be able to display location. Okay, just to look at the live example in comparison, you can see that my Y-axis are locations, and we want to get to total. How many items are assigned for each location? So let’s go back to Caspio. For your X-axis, you’re going to leave this as is. You can also format this if you wish to have it be as a number. And then, once again, specify zero digits after the decimal. And for the value, once again, you’re just going to do the count. So device type, let’s do the count. You can hit preview, and you can see how it’s going to save one for Toronto. So let’s close the preview tab, save our changes, and let’s build one more data page here for the admin.

Create Chart for Spent by Location

And the last chart that we’re going to create is total spent by location. So we want to try how much money are we actually spending on all of these inventory items. So we’re going to make a copy of that one once again because it’s a bar chart. Total spent by location, and let’s edit. Click next. And let’s go all the way to chart settings. Y-axis will remain the same, that’s based on location. For your X-axis, it’s going to remain the same. The only modification that you may want to make here is under formatting this time. Instead of number, you want to display currency. You can choose to have digits after the decimal; we’re just going to round that up. Click, Okay. And then, for your value, make sure you select purchase price. And instead of count, you want to get the sum aggregation. Click next one more time so that you can sort based on the sum. Now, when you hit preview, once again, it’s only going to show Toronto. But you can see how much money it was spent, which was 7.99 for that iPhone seven. Close the preview click on finish.

Data Page for Admin to Edit Existing Items

And this is how quickly we’re able to build out all of these functionalities using Caspio’s point-and-click, Low-Code Platform. The next data page that we’re going to build for the admin-level user is when we look at the inventory and we go to details for this model, let’s just say I need to build out this data page now in order for the admin to modify existing items. So let’s go back to Caspio. Build a brand new data page, go to reports, and select the details data page type. Click next. Based on the inventory table, let’s call this item details, same style, same localization restrict access to your admin level user. Click next. You want to make sure you filter data based on predefining criteria. You want to allow parameters, and you want to make sure you allow bridge and external parameters. External parameters is the information that’s being passed in the URL.

So if I go back to my results page, and if I click on that model, once again, you will see how we’re passing the model ID or that item ID to the details page. And now my details view needs to receive this ID. This is common industry practice. Almost every database-driven application passes values in the URL in order to create relationship between data inside a database. Now, in order for me to display the details for that item, I need to receive that item ID so that I can properly display the right information in the details view. So this is why we need to receive the value now. We’re receiving item ID, so let’s include that as our filtering field, and you have to configure the advanced app correctly in order to properly receive that value. So received the value externally, and my parameter name was called IID. So, if you remember from that URL that we created a couple of minutes ago? When we clicked on that model to go to details, dot HTML. In the midst of all of that, we passed the IID as a parameter name.

We now have to receive that, and value must be required. The reason why is in order for you to display the right information, you have to receive the right value. So let’s continue. And now, in the details view, we’re going to include all the fields with the exception of item ID, date created, and last updated. The other fields we can include in our details view. Let’s continue. From here, all you really need to do is make all of your fields editable. We’re not going to do that in this video in order to save on time but play around with it right now. When I click preview, it’s going to ask for that ID of that item. I don’t really know it off the top of my head. If you go to your table, you can pull up that item ID, click on apply, and you should be able to see it.

But if I click apply, now it’s going to say no records found. So, in other words, by default in the details view, as you can see, everything is displayed only or read-only. But if you would like to be able to modify, just change the form element to one of these other elements that we provide in the dropdown. So, for example, serial number, you can turn that into a text field in order for the admin-level user to be able to edit the serial number. However, this application will be made available for download. So you can import this application into your account, and you can see how I configured it, the original application. For now, I’m just going to click finish to save on time, as I said. And we have two more data pages left to build for the admin-level user, and that is to manage all of the employees.

Data Page to Add New Employees

If you look at the live example, under users, we can add a new employee, and we can also manage all the employees, as well. So let’s go back to Caspio and let’s build the data page to add a new employee. So we need a submission form for that. For my data source, that’s going to be the employee table. Let’s give it a name, add new employee. Again, we’re going to use the same style, as usual, restrict access to admin-level user. You’re going to want all of your fields on the form. Let’s continue. First name, you can probably make that a required field, same thing as last name. Then you have the email, which is immediately required because it’s unique inside a table. You can set the password on behalf of your employees, at least initial password, and then they’re going to be able to update it once they log in. You have title. You have department, location.

Now, these are all going to be dropdowns. But again, in the interest of time, I’m not going to configure each one of these fields. Hopefully, by now, you already know how to configure each one of these fields to turn them into dropdowns if you want. We have access level as well to specify if the user’s going to have employee level access, manager, or admin, just like in my live example, if you look at the forum. Here, we have the ability to set access level, choose a location, and also who that employee reports to. Now for that field., it’s called manager ID inside my table, but my label says reports too. This needs to be a dropdown, so I will show you how to configure this. So this needs to be a dropdown select both. And if for display, for custom values, we’re going to say select manager, perhaps. Delete the value and an under lookup table, you select your employee’s table. You select the full name, and then you stamp the employee ID inside a manager ID field.

This is how you’re going to be able to set who this employee is going to report to. And an account status, maybe by default, you can have this be hidden and checked to give them access immediately. In my live example, you can see how I have a display. So let’s do the same thing. We’re going to have this be a check box, and let’s have some display text. Check to enable login access, click finish to save our changes. The last data page we’re going to build for the admin-level user is for them to be able to manage all the employees. And that’s a very easy data page to build. Let’s go down to reports, click on tabular, click next, select your employee table. And let’s give it a name, manage employees. Same style and localization restrict access to admin-level user because only the admin-level user will be able to manage everyone.

Data Page to Manage All Employees

Let’s have a search above the results. For your search fields, just for the interest of time, let’s just include the first and last name and click next. And for my two search fields, I’m going to use the comparison type contains, which is more of a partial keyword search. So if I type in the letter a, it’s going to return all the first or last names to contain that letter. For your results page, you can choose to include some fields here as well, maybe title, department, and location, and maybe who they report to. You can also include access level. This is completely up to you. Next, next, next, next. Let’s go ahead and include the details page. In the details view, you can choose to display everything if you want, aside from the full name, because we already have first and last names. You don’t even have to display the employee ID if you don’t want to.

And finally, on the details page, you’ll notice again, by default, everything is display only. All you have to do is modify these fields to make them editable so that the admin-level user can make those adjustments. Now I’m not going to do that; we’re not going to spend the time. Again for the interest of time, so all we’re going to do here is click finish to save our changes. But if I click on preview, you will now be able to search and manage all of the employees. Let’s close the preview tab, and we have six more data pages left to build. We have completed all the admin data pages. Let’s close out that folder, but you can see how quickly and easily we can build out all of this functionality in Caspio. This is the power of Caspio. Low-code to no-code development, or as it’s sometimes referred to, rapid application development, all you’re doing is pointing and clicking through the data page wizard to build a functionality.

Data Page Both Admins and Managers can Access

And eventually, you’re going to deploy that onto a website. Now let’s build the data pages that both the admins and the managers are going to be able to access. So let’s open the folder, and we only need to build two data pages here. The first one I’m going to build is because they both have the ability to manage devices. So if they want to input a new device or edit that device, they’re going to have that ability. So, if you look at my live example here, under inventory, manage devices, you can see how I can input a new device, or I can edit an existing device or I can delete it.

So we’re going to go with tabular format. Click next. That’s going to be a device lookup table as my data source. Let’s call this managed devices, blue, English, restrict access now to both admin and managers. Click next. Let’s filter our data: no need to search, no need to filter based on any fields. We’re going to include the device field inside a results page. Let’s have it be the ability for inline insert, inline edit, and also inline delete. Let’s have the ability to edit that needs to be a required field, and let’s have it be a text field. Continue, let’s display 25 items per page. Let’s disable the details page. And now, when I hit preview, you’ll notice, that as the admin-level, user or manager, depending on who’s logged in, they’re going to be able to input new devices and manage existing devices. Close the preview, click finish to save your changes, and you’re done.

Second Data Page: History Log

And the second data page that we’re going to build is a history log or item history. So we can keep track of all the changes for each item. So build a data page once again, reports tabular format, click next, and your data source for this one needs to be the history log table. Let’s give it a name, history log. Again, we’re going to use the same style, restrict access to both admins and managers. Click next. We want filter data. You want to allow parameters, and you also want bridge and external parameters. The reason why, if you look at the live example and we go back to all, if I click on this model in the midst of me clicking on this link. We’re going to pass that model ID and the URL. As you can see, my details form is receiving this ID and filtering the data based on that item.

But if I scroll down, this report is also receiving the same ID in order for me to view the history for that specific item. Back to Caspio, click next. We’re going to filter the history log report based on the item ID, go into the advanced tab, receive the value externally as IID. Once again, same parameter name value must be required. Click next. Let’s move all the fields that are right. And the only ones that we’re not interested in seeing, or the item ID, purchase receipt, and last updated. The other ones, we would like to be able to track and see. Click next. No need to edit, no need to make any changes here. Let’s display 25 records per page. Let’s disable the details view and click finish.

So now the idea here is when you actually go to the details of that item, you’re going to be able to see this report as well, and it’s going to display all the changes for that specific unique item ID. Okay, we have four more data pages to build to complete this application. So let’s close out this folder and let’s build the remaining data pages inside a manager folder. And these four data pages are going to be actually very easy to create because they’re very similar to what the admin-level user can see. So, for example, managers are also going to have the ability to add new items, but instead of me building that form from scratch, all I need to do is expand the admin folder. Make a copy of this one, click save, and let’s go ahead and move this data page now. Inside a manager folder like this, let’s close the admin folder.

Here’s the same exact data page. First thing I’ll do is I’ll just go ahead and rename it. So we remove this underscore in one. Then I’m going to click on edit, click next. And very important here is to change the authentication to manage your only. Click next. We’re going to keep all the same fields the way we have it here. Click next. And what’s very important here is if you find a location field, just go ahead and move that all the way down. You don’t have to move it all the way down, but these are all of my fields that are being time stamped and also hidden based on who’s logged in to be able to stamp the user ID date created and for location. We also want to make this field hidden authentication field and grab the location from this dropdown. This way, when the manager is logged in, if they’re in Toronto or if they’re in a different country, we’re automatically stamping the location inside an inventory table.

Remember how an admin-level user was able to specify in what location we can add this inventory item? Well, a manager, if they actually only work out of the Philippines office, it’s going to stamp the location, Philippines, inside an inventory table. As a manager-level user, that’s been assigned to a specific location. We shouldn’t have the ability to add something for the U.S office, for example. Another change that you want to make under assigned to ID under security here, you’ll also want to enable location as well. So you’re only filtering employees that belong into your specific location. I don’t want to be able to see the employees inside a U.S office if I work out of a Philippines office. So that’s the idea behind using a security tab here to further limit values based on user identity that’s logged in. And then, once you’re done, go ahead and click on finish.

Those are the only two modifications that you need to make. We have three more data pages to build, but I’m not going to show you how to build the last three data pages. Hopefully, by now, you know how to do it yourself. All you need to do is go to the admin folder. Make a copy of the item details, make a copy of the advanced inventory search and also make a copy of the quick inventory search. So make a copy of all three of these data pages move them into your manager folder. And very important, once you move those data pages is to edit each data page and change the authentication from admin to manager. And you also have a few more modifications to make on the search form and also on the details page. Hopefully, by now, you know how to apply those changes yourself.

If you don’t know how to do it, this application is available for download. You can import it into your account, and you can see how I completed the entire development. Once it’s all said and done, you should have a total of 22 data pages to complete this entire application. Thank you for watching part four. I hope that you enjoyed it, and hopefully, I’ll see you in the final video of this ultimate video guide, where I show you how to deploy most of this application into a website. Thanks for watching. And I hope to see you there.


Next Steps

See why low-code developers are growing 3x faster than traditional developers.
Have a vision for an application? Talk to a Caspio product expert and we’ll help you achieve it.
Want to see if Caspio is a good fit for your needs? Choose a date and time for a personalized demo.