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 at Casipo, and in this ultimate video guide I will teach you how to create an expense reporting application using Caspio’s low-code, a visual application builder. In part four, I will teach you how to create all the functionality and workflows for this application, which will include forms, reports, charts, and pivot tables. Let’s go ahead and dive right in.
The very first data page that we are going to build is this unique data page that is going to allow us to redirect our users based on their user role. To build that data page, all you have to do is simply click on this link here at the top and this is going to launch Caspio’s point and click data page wizard. The type of data page that we are going to need to build here is an HTML data page, and this is the only data page that is actually going to contain some coding.
All the other data pages that we are going to build after this one will not require any coding, but this particular one is going to contain a little bit of script that is going to allow us to redirect our users based on their user role to a specific webpage. I am going to show you in this video how we deployed that log-in screen just for this video, but we are going to focus more in depth as far as deployment is concerned in the final video of this ultimate video guide. Let’s continue. I am using a specific style called Blue. I am going to call this data page log-in screen, and I am going to restrict access to all employee log in. Now, remember this authentication, as you can see here, is coming from this object over here. That authentication that is listing all the employees is built on the view that is filtering all active employees, which means anybody who is checked as active in my table is going to be listed on that view, which further means all of those users will be able to log into the application.
Your main log-in screen is going to have to give access to everyone, but upon redirection, which you will see later on, you will see how we limit what those user groups will be able to see. Click next. On this screen go ahead and disable the HTML editor. Inside this window is where you are going to have to add a little bit of script. I am going to go in and paste my script so that you can see it, but feel free to pause the video now. What you are going to have to do inside the script is modify it just slightly in between the quotes. Go ahead and remove my field and insert your own field because it is going to carry your own table name, user role as a string and go ahead and say equals to admin. If you used admin in your table, go ahead and just type that out inside this line.
What this basically means in the application is if the user that is log-in equals to user admin, I want to redirect that user to this specific URL. If you look inside my directory, I am going to have a main log-in page and upon logging in each one of these user groups will be able to log into a specific folder. I have an admin folder that contains its own webpages. I have an employee folder, I have an HR folder, and I also have a manager folder. If I log in as the admin, I am going to be redirected to this directory called admin. This is the reason why you see admin slash and then this URL where I want the admin to go to. As you can see, I have that webpage called dashboard. The moment my admin logs in, I want them to be able to go to this webpage as the very first thing that they see upon logging in.
As you can see from the rest of the URLs, if the user equals to manager, I want the manager to go to this URL. I happen to have that directory as well called manager. As you can see, I have a dashboard page if the user equals to HR. If in my table you are flagged in the HR user, you are going to be redirected to these set webpages as the very first one is going to be dashboard. Finally, anybody who is actually not an admin, not a manager and not an HR user will by default be redirected to this URL called employee. This is the lowest level user inside my application. Again, if you look at my directory, I happen to have a folder called employee. Inside that folder, I also have a set of webpages that the employee will be able to see upon logging in.
As you can see, the script is not very daunting, it is not very difficult to understand, it is just modifying the script a little bit. Just go ahead and replace all the field names that I have inside my script with your own. I am going to use this one for manager. Once again, we need a user field, and the very last one here is for HR. But again, remove that and insert your user field the same way. You just have to repeat that process three times. But hopefully you can see that it is very easy to understand depending on who you are inside a table, it redirects you to a specific webpage. Once you are done, go ahead and click finish now and you have your very first data page created. This is essentially the log-in screen that is going to redirect your people to a specific URL.
Now in this video I am going to show you one time how we deploy this data page, but the rest of the data pages, I am going to teach you how to deploy them in the final video. All you have to do is click on deploy, enable deployment status, grab your Caspio embed code, you can go ahead and close that window now. I am going to open up my script or my HTML document that contains all of my webpages for my employees. Here is the very first page that is going to have my log-in screen. Go ahead and find that placeholder where you want that log-in screen to be deployed. Paste your Caspio code, save your page. Then when you refresh the website, you should be able to see that log-in screen now seamlessly embedded and now upon logging in depending on who you are as a user. Let’s go ahead and log in as a sample employee. Now to retrieve that data, you can go back to your employee table. I am going to open that up in a new tab.
Open your table, and if you just quickly want to see the list of your employees, you can see we have Carly Jones. She is listed as an employee. If I go back to my website and I log in as carlyj@company.com. Password is test. Now you can see how Carly has been redirected to this webpage called dashboard.html inside an employee folder. Because she’s an employee level user, she will be redirected to this directory, this folder, employee with this webpage. Now Carly will be able to see additional webpages. But, we have to first build these data pages in order to be able to view all the functionality. As you can see, because we have all of these pages empty at the moment.
Now it is completely up to you what data page you want to build next, but for me, I always like to have a data page that is going to display the name of the user that is logged in. Now you can see I have some placeholder text here, so I am going to go back to Caspio. Now very quickly build that data page and it is very easy to build this data page. All you need to do is go inside the all access folder because this folder is going to contain all the data pages that are going to be accessed by three or more users. In this case, when everybody logs in, they should be able to see their name because it is going to be a shared data page. We click on new data page. Once again, I am going to use an HTML data page type. Click next. I am going to use the same Blue style. Let’s give this a name. I like to call it welcome name. You are going to have your own naming convention. Of course, let’s restrict access to everyone because everyone should be able to see their name when they log in. Click next. The only thing you have to do here, you can disable the HTML editor and insert the authentication field, full name as a string. That is all you have to do. When you are done, click on finish. Go ahead and once again, deploy that data page. Enable status. Copy this code. Close the window, and now open a different webpage inside an employee folder. For example, let’s take a look at the dashboard page.
Here is my page dashboard for the employees. I need to find that placeholder where I want the name to be displayed. Here is my placeholder. I am going to paste the Caspio code. Save it and then let’s go ahead and refresh our webpage. Go back to the dashboard page and you should be able to see Carly Jones. Now if you want to remove this branding, what you can do inside the HTML document is go ahead and just remove the div tag from your employee code like this because all you really need is the script line in order to properly embed a data page into a webpage. So once again, save, go back to your website refresh, and now you can see welcome Carly Jones. It does not matter now if you are logged in as the admin user or if you log in as an employee, HR or manager, everybody is going to be able to see their name because we created that data page inside an all access folder and we are using the authentication that is giving access to everyone.
What I am going to do next is build the remaining data pages for all the employee level users. The next data page that I am going to build is the ability to have them be able to update their password. In fact, when every user logs inside the application, they should have the ability to update their password. That is why we want to build this data page inside the all access folder. Let’s click on new data page.
I need to use single record update. This data page type allows users to update everything within a single row inside a table. We want to use a single record update and click next. My data source needs to be my table of all the employees. That is where we pull all the information from. Let’s call this data page update password. I am going to use the same style called Blue, and I want to restrict access to all employees. Why? Because every single employee from this table will have the ability to update their password. Click next. Now we need to apply record level security through a unique ID so the employee that is logged in, we are going to verify their employee ID and we also want to match that up with the employee ID from the existing data source. Let’s click next. The only field I need to include here is the password field because I want my employees to be able to update their password.
Let’s continue and let’s make a few changes here. The confirmation label will say confirm password. You can also enable some other options here to show password strength if you wish. You can also have a minimum strength required. If you want to have a more complex password for all of your users, something that is a little bit more difficult to decode or add additional security to it, you can have a minimum strength required. Be one of these three, but I am not going to do that. Let’s keep it simple. This is completely up to you, and once you are done, all you have to do is click finish to save your changes. Here is your data page. Now, I am not going to show you how to deploy this. We are going to save this for the very last video of this ultimate video guide. But the idea here is when Carly logs in, she’s going to be able to go to this webpage, click on change password and be able to see that update form so she can update her password.
The next data page that we are going to build is the profile page. Let’s go back inside Caspio and let’s build that data page as well. Again, everybody’s going to be able to update their profile. Once again, we want to include this data page inside the all access folder. Again, we are going to use single record update because we want our end users to be able to update their profile. Click next. The data source is once again the employee folder. Let’s give it a name. Update profile. I am going to use the same style and restrict access to all employees. Click next. Once again, we need to find this profile via the unique ID and now list all the fields you want the end users to be able to update. I would like for them to be able to update their user photo. Let’s have them be able to update their first and last name, perhaps their title, email and let’s show what department they belong to.
Again, it is completely up to you what fields you want your end users to be able to update. If you include the fields that I have, you can actually choose to have the fields be editable or display only. Even if you include all of your fields, you can make them display only or you can allow users to edit those fields. Let’s continue. For the user photo, let’s go ahead and display this as a file. Use the file form element. I want to display the image and you can set the width and height accordingly. It is up to you your own preference here. Okay, I am going to set my width of the image to be 200 pixels. going to leave the height empty because if you set a specific height in width, then the image might be skewed or disproportionate depending on the type of image that is initially uploaded.
You can also have a default image. If you have a default image, if your employees currently do not have an image, you can display a blank image or some kind of a default placeholder to replace the actual photo. I am not going to go ahead and do that. Let’s go to first name, have it be a text field and make it a required field so they can input their name, last name. We are going to do the same thing, title. Let’s also give them the ability to change their title if they want to. Again, you may not want to have this, it is completely up to you. And email. We have a special element called email. It validates the email that it is the right format, it looks for the @ symbol and the period afterwards. Finally, department. We can actually have this be display only. Once you are done, click finish. There is our third data page inside the all access folder.
The next data page that I am going to build for the employees under my expenses is the ability to see all the current expenses. Let’s go back to data pages. Click on new data page type, select reports, and I am going to go into tabular format. Let’s move on. My main data source table needs to be the expenses table. That is where all my information resides. Let’s give this data page a name and call it current expenses. I am going to use the same style called Blue, and I want to restrict access to all the employees once again because every single employee in this application is going to be able to see their current expenses. Let’s move on. I do not want to be able to search interface. I just want to be able to filter my data based on my predefined criteria.
In other words, just show me a list of expenses that I am filtering. I need to enable record level security and only show the expenses based on the employee that is logged in. Let’s continue. The three fields that we are going to be using to filter our data are going to be the status field, the process date by the manager, and the process date by HR. Let’s move on. The next thing that you need to do is include the criteria for each one of these fields. The status is going to have four different criteria, so go ahead and click on new criteria here and add two additional ones. Then click on the manager process date. This one is going to have two criteria, and the HR process date will also have two additional criteria. The next thing that you want to do is go into the logic tab, and we now need to properly configure the operators in order to filter out the current expenses.
We know that we are going to be showing expenses based on four different statuses. We are going to have approved, we are going to have pending, rejected, and processed. I am going to include the third criteria for status inside this operator. I am also going to drag the fourth criteria inside this operator. We are always going to be showing a status of pending, and we also always want to show the expense that has a status of approved because these two types of statuses require further action. If the expense is pending, it needs to be reviewed by the manager. If the expense is approved, it needs to be reviewed by the HR team. Set the main operator to or set the additional two operators to and. Now let me show you how to configure the additional ones. We also want to be able to display the status of rejected, but we only want to show the expense that has been rejected in the previous 30 days.
Here is how we do that. Set this to a previous X day and just input a number of 30. You also want the date to equal to today. What this filter is going to do is basically if this condition is true, if the status is marked as rejected and it passes the 30 days, it is no longer going to be shown inside this report because eventually we want to remove it because it does not require any further action. The fourth status, we want to say processed here. Once again, we want the date to be set to previous X days of 30. We also want today, because I only want to show the expenses that are processed within 30 days after the 30 days expires, I no longer want to be able to see this expense inside this report just to keep things clean and organized. Let’s move on. Now we need to include the fields on our results page.
Let’s have the status be the very first field. I am going to include dates submitted, title, description, category, and the receipt. Let’s also include a total field as well. Click next. Do we want any of the employees to be able to update any of the current expenses? The answer is no. Let’s leave these unchecked and let’s continue. Let’s group our data based on status. Let’s have a collapsible group and let’s have the data be expanded by default. For my receipt, I want to display this as a file because it is going to be an attachment, and I want to enable some custom hyperlink text to simply read or you can say download receipt or just receipt. It is completely up to you. I would also like to group my data and show a calculation in aggregate of all of these expenses. I am going to include totals in aggregation and under aggregate one, we are going to include total.
Let’s find that field in this list, move it to the right. All you have to do here is simply just apply a function called sum. The last thing that I am going to do is include a header and footer. Inside a header section, I want to include a hyperlink so when a user clicks on it, they are redirected to a page where they can add a new expense. To create your hyperlink, just write out the text, you can call it new expense, highlight your text, and then click on this icon here. Then what you want to do is enter your destination URL. Again, in other words, when people click on that text, it is going to take them to a new webpage. On that webpage you are going to have a form to add a new expense. Now my destination URL is going to be called new expense.html.
Click okay. Now you are going to see how we create this link at the very top. If you want to see the syntax, the actual HTML code, click on the source button and that is going to reveal the code. Now I want this new URL or this new webpage to be relative or in the same directory as my actual website. I am just going to go ahead and remove the http part. This way, now when I click on this link, it takes me to the same page in the same folder or same directory. Click next to continue. Let’s go ahead and display 25 records per page. You can include additional ones if you want. You have some other settings here that you can enable. For example, you can decide how you want to sort the data in the advanced tab. You can also include the download data in an Excel format, PDF or CSV.
Again, it is completely up to you and just be careful who you enable these features for because people are going to be able to download your data via the web. Let’s continue. Do we want to enable a details page on this screen? Yes, let’s go ahead and enable a details page, but I would like to have one of my data points become the details link. I am going to click on this radio button and have the title become clickable so when I click on that title, it is going to take me to the details view of that expense. Let’s continue. Now let’s go out and include all the fields except for the expense ID and the employee ID. We can move those back to the left, but in this details view, I’d like to be able to see all the information pertaining to that specific expense. Let’s continue.
Now you can rearrange these fields however you want. It is up to you how you want to order the fields, whichever ones are more important to you, you can place those at the very top. The fields that I am going to organize towards the bottom are going to be the ones that are more for internal use. For example, I want the status to be at the very bottom, not at the very bottom, but towards the bottom. I would also like to have the manager ID field be over here. Now I am going to rename my label to simply say process by manager. I am just going to remove the ID from the label and put manager in parentheses.
Let’s have the manager process date be underneath that. I am just going to rename my label to say date processed, also manager in parentheses. Then I will have the manager rejection reason and I will rename that to read rejection reason. Now, one thing that you may want to do here is go to the advanced tab and just simply say, hide if field this blank. In other words, if nobody provides a rejection reason, I don’t want to display that field in the details view. It automatically hides that field if there’s no text. Let’s continue. We have processed by HR ID, same thing, we are going to say processed by HR, and I am going to put the HR in parenthesis. Then I am going to have the process date by HR and I will rename that to say, date processed, so HR. Finally we have the HR rejection reason. I will just rename that to rejection reason.
Once again, go to the advanced tab and simply just say hide if field is blank. The last field that we have here is the pay date. When you are done, you can go ahead and click on finish to save your changes. Now we have a report that is going to display all the current expenses if they are approved, pending, rejected or processed. But the rejected and processed is only going to show within 30 days and then we can drill into details. Let’s continue building the remaining data pages.
Now let’s go ahead and create that form that allows everyone to be able to create an expense. New data page, we will select submission form, click next. We want to be able to store all of that data inside the expenses table. Let’s call it add expense. I am going to use the same style of Blue and I want to restrict access to everyone because all of the employees will be able to create an expense.
Let’s continue. The fields that I would like to include is the employee ID. The reason why I want the employee ID is because I want to be able to stamp the employee’s ID that is submitting the expense. That is critical because we want to be able to track and know who is actually creating these expenses. Category, title, description, total, let’s have the receipt status and let’s also have dates submitted. These fields are used for internal use, so we are going to exclude those from our submission form. Let’s continue. Now let’s go ahead and modify each of the fields. For my employee ID, I want to hide this field. I want to use the authentication field, and I want to look back into the employee table to stamp the employee’s ID. On the submission form itself, you are not going to be able to see this field, but behind the scenes or in the background, that field is going to populate the employee’s ID that is logged inside the application.
This is how we are able to submit and stamp that ID on behalf of each of the employees. Let’s have category and for category, we are going to turn this into a dropdown, and I am going to use the source both for custom values and lookup table. The reason why is because under custom values, I want to say something like this, select category. Make sure you delete the value because you are not really storing this inside a table as a value and in a lookup table, we already have a table that contains all the categories. All you have to do is link that lookup table to your dropdown and you are done. That is probably going to be a required field, so make sure you check this box. For title, let’s have that be a text field, make sure that it is a required description. We want this to be a text area. Let’s require that as well.
Total is going to be a required field. Receipt is going to be an attachment. You can specify the maximum allowed size you have up to 50 megabytes, I think 15 megabytes or less should suffice for status. Let’s actually make this a hidden field. Now let’s give it a default value of pending. Every time you submit an expense, it automatically is going to be flagged as pending. Now if a manager is looking at the expense, they can change from pending to a different status. Or if the HR is looking at the expense, they can also do the same thing and they submit it. We are going to have this be a timestamp, so it automatically stamps the date and time of that submission. Once you are done, you can go ahead and click finish. Now you have that submission form. Remember how on this data page we created that link called new expense? What’s going to happen is when you click on that link later on, it is going to take you to a webpage that is going to have this form deployed. You are just linking these two data pages via that hyperlink. But these two data pages are going to be deployed on different webpages.
The next data page or the next functionality I want to make available for the employees is to be able to search all the expenses. Because this report is going to be very similar to this one here, we do not actually have to build a new data page from scratch. All we can do here is click on more, click duplicate, and you can make an exact copy of that data page. Let’s call this one search expenses.
Click save. There we have it. Now I do need to make a few modifications to it. Let’s click edit, click next, click next again. We are going to maintain all of this information. Now the only difference between this report and the copy is that we want to enable a search form. Let’s now continue. Now let’s choose what fields we would like to include in the search form. I will go ahead and include category, field, title, status, and also date submitted. It is up to you what fields you want to include. In fact, you can include all of these fields if you wish. Let’s click next. Now let’s configure each one of the search fields. We have a list of categories. We can actually turn this into a dropdown, choose both because the very first thing that I want to be able to search is search any, it is going to have a null value.
Make sure you remove value because you are not really looking for this. In the lookup table, you simply link to your categories lookup table like this, we have the title. For title, you could actually do something more complex. You could actually have auto complete special element, which in a way works like Google search. If you choose filter by contains, now what it is going to do is when you start typing inside that field, it is going to list your options underneath the field of what you have available inside a table. As you continue to type, it eliminates other options. For status, we also have a look up table for this. Let’s create that to be a dropdown. Let’s do both. Once again, search any status, delete the value, and then just link your drop down to your status look up table. Finally, we have dates submitted.
Let’s create a date range. To do that you are going to insert what we call a new criteria and then you can see how it populates two criteria underneath your field. Set the logical operator to end. Then for criteria one, go ahead and just in your label you can save from and then you do greater than or equal two. Then your second criteria is going to be two. Then all you need to do is set this to less than or equal two. Let’s continue. For my results page fields, I am actually going to keep the same fields as under current expenses. Let’s keep the same fields inside of results and continue. I do not want anyone to be able to do any editing from the results. Let’s continue once again. The only change that I am going to make here is I do not really need to group my data based on status.
You can if you want to, but I am going to go out and disable that. If from this report you also want to give your end users the ability to add a new expense, you can go out and keep that hyperlink inside a header section of your data page. Continue. Let’s display 25 records. Notice how we still maintain the title as our clickable link to go into details because I made an exact copy of the other data page, it maintains a lot of the formatting and a lot of the settings. That way you don’t have to recreate that from scratch and it is most noticeable on the details page. Let me show you when we click next and we click next one more time. Notice how all of these fields it maintains my labels exactly how I created them before. This saves you a little bit of time without you having to retype all the other changes and configurations that you did before. When you are done, just go ahead and click finish to save your changes. Here we have an additional data page. Let’s continue.
Now I am going to teach you how to build a submission form and use the submission form as a search form. To do that, click on the new data page, select submission form, click next and ask for your data source. It really does not matter at this point what you use as your data source because the search fields are going to be virtual fields. Virtual fields do not actually submit anything into a table. They are just used to look at information from the table and gives us the ability to filter data from the results. I am just going to include any table in this case, I am going to use the categories table. Let’s give it a name. I am going to call this dashboard filter Blue style, and I am going to restrict access once again to all the employees and continue.
At this point, just go ahead and include one of your fields. Hit next. Then what you need to do is insert four virtual fields so one, two, three and four. Once you have all of your virtual fields included, you can remove the category field. Now let’s configure all the virtual fields. Remember, a virtual field does not actually submit anything into a table. What it is used for is to look at information from the table and it gives us the ability to filter data. My first virtual field, let’s go ahead and call this status because I would like to filter my charts based on the status field, and I am going to include this to be a dropdown. Once again, custom values will be both. My very first option under custom values will be search any, delete the value. Then we are going to go to the lookup table and select the table that contains all the statuses.
The next thing that we have to do for this field called status is go into the advanced tab. What you will need to do is you will need to receive the value of parameter, and you will also need to pass the field value as a parameter because when you have searched this status, you need to pass that status to the chart in order for the chart to filter out the correct data. Let’s give our parameter name. I am going to call this S, which is going to stand for status. The same thing down here, just call it S. We need to repeat this process three more times. Let’s go down to virtual field three into the standard tab. Then for this label, we are going to call this perhaps category because I also want to pass the category value to my charts in order for the charts to filter out the data based on category. Once again, drop down, search any, delete the value, and then look up the table of categories, go to the advanced tab, receive value, pass the value, and let’s call this C for category. You can call your parameter whatever you want. This is the naming convention that I am going to use. My final two virtual fields are going to be used for date range. Back to the standard tab.
I will call this date from, enable the calendar popup, go to the advanced tab. Once again, we want to receive the value parameter and also pass it, make sure you receive it externally and give it a name of maybe DF, which is going to stand for date from when you pass the field as a parameter. Let’s call it the same exact thing, DF which is going to stand for date from, and this is going to be day two. Let’s go back to virtual field one, standard tab. Let’s give it a name, enable the calendar pop up, go to the advanced tab, receive value, pass the value externally, and I am going to call this DT for day two. And it is the same down below on the subsequent screen. Go ahead and just have the form refresh. You do not need to see a confirmation message because when you quote submit this form, you just want to pass that information to the charts in order for all three charts to receive those values and filter out the data. When you are done, click on finish.
The final three data pages that we are going to build are charts for all the employees. Let’s build our very first chart. I will have this be a pie chart, so just scroll down a little bit. Select pie chart, click next. My data source needs to be my expenses table. Let’s give it a name. We will call this Expenses by Status, restrict access to everyone. Click next. This time you want to filter your data. You also want to make sure you allow parameters and bridge in external parameters because the chart also needs to receive those values that you are submitting via those virtual fields. If you submit a status of pending, the chart needs to receive that value in order to filter the data for that specific status. Let’s enable record level security. Once again, you want to select employee ID because if I am filtering my own stats on the dashboard as the employee that is logged in, I want to make sure that the chart filters out my specific data. Continue.
The fields that we want to include are going to be the category field status and also dates submitted. These are the values we are going to be receiving inside a chart. Let’s continue. Now let’s configure this, and it is going to take me just a minute to configure all three of these fields for category. We now need to go to the advanced tab, receive the value externally, and I called my parameter lowercase c, go ahead and say if empty, ignore criteria, meaning if you leave it empty and you search any, it is going to return everything on the chart. For status, same thing externally, I called it lowercase s. For dates submitted, you want to include your criteria. Set the logical operator to end, and then your criteria one, go to the advanced tab, receive the value externally. We call this DF. Under the standard tab, just simply say greater than or equal two.
Then criteria two, go to the advanced tab, receive the value externally, we call the DF back into the standard tab and just say less than or equal to. Click next. Now let’s set up the chart. The chart configuration should be very simple. You have some options here to change the width and height according to your own preference. Under category, I am going to select status and under value, I want to select my sum, which is the total. I want to do a sum and I do not really want to display this as percentage. I do want to see the actual values and I am going to edit this and change my localization to currency. You can choose to have however many digits you want after the decimal. I am just going to say zero. Click okay. That is all you have to do. Let’s click finish.
Now what is going to happen is when you filter the data using this submission form and you click search, this chart below is going to filter the data based on that input. Let me show you a live example. As you can see, we have these dropdowns to filter our data. If I send pending as a parameter to my chart and I click search, you will see how it filters out the chart based on the value that we set. In the final video of this ultimate video guide, you will see me deploy all of these data pages, but I just wanted to give you a live example so that you know what to expect.
Let’s go back to Caspio, and now let’s build an additional chart to filter based on category. Again, you don’t have to build that chart from scratch. All you need to do here is just make a copy of your chart and let’s call it expenses by category and click save and then go ahead and edit.
We just need to make a few changes to it. Click next. Next again, one more time. And once again, once you reach the category field here or element, you just want to change from status to category and you can apply a different value if you want, but I would like to be able to group my data and get a sum for all the categories. Once you are done, go ahead and click finish to save your changes. You can see how quickly I was able to build a second chart. Once you have a data page created and you need to build a similar data page in the future, you do not have to build it from scratch, you can always make a copy, make a few adjustments, and then save your changes.
The final chart that I am going to build for the employees is the ability to see total expenses by month. Once again, I am going to make a copy of my chart and let’s call this expenses by month. Save.Edit.
Let’s go ahead and find the line chart and let’s navigate all the way to the configuration screen because we do not need to make any more changes here. Once you reach this wizard, you will be able to see a couple of different changes through your settings. If we go to data source, we want to apply our data using separate fields for each series. Let’s leave this radio button selected. Click on the X axis. And on the X axis I want to be able to display the date. Let’s select dates submitted. Let’s group the date based on a date roll up and I want to roll my data into a month. You can also provide a title for your X axis if you wish. I am not going to go and do that for my Y axis. Let’s lead this the way it is. You may want to format your settings to include currency and for value one, all I am going to do is use my total field, apply sum, and I can also choose a different marker.
Let’s go with a diamond shape. Click next. Once you are done, go ahead and click finish and you will now have all three of your charts created. And this concludes the building of all the data pages for all of the employees.
At this point, I went ahead and created the remaining data pages. If you look inside my admin folder, I have those data pages also for my admin/manager. I also have HR data pages and I also have manager data pages created just to speed things up. I have created all the data pages in advance. By now you should feel comfortable building these data pages, so we are not going to build them all from scratch, but what I will do is show you how they were all created. I will click edit so that you can see all the screens and understand all the settings that I applied.
Now what I would like for you to do is go down to views and set up a sixth view where you join two tables together. In this case, we are going to be joining the employees table with the expenses table. Go ahead and click on new. I am going to click edit just to show you the interface. Notice I am including the employee table and the expenses table to the right. Go ahead and move those two tables over and click next.
On this screen you want to have just a single join where you have the employee ID joined with the expenses employee ID. To do that, if you do not see this created on your end, all you need to do in the first dropdown is select the employee ID from the employees table and you want to select the employee ID from the expenses table. Once you select those two options, click on the plus sign and then you are going to be able to see the inner join. Also, what you want to do is select your expenses table from this dropdown, because later on we want to have the ability to edit the expenses table from this view. Once you are done, click finish. Now you should be able to see that view listed with all of your other views. Once you are done, go back to data pages and let’s start building all the admin data pages.
Notice how the admin also has a form to add an expense, same as inside the all access folder at expense. The difference here is these are two different types of forms. Employee level users, which are going to be our employees, our managers and HR team will have somebody above them that will need to approve their expense. However, an admin level user is the highest level user, so when they submit the expense, we should automatically stamp the status as approved because nobody is above them to approve their expense. Once they submit it, all the HR needs to do is process that request. Instead of building this form from scratch, all you need to do is just make a copy of this one. Once you make a copy of this form, move it inside the admin folder. Once you have it inside the folder, I am going to click edit to show you how all the screens are created. It is still a submission form. Hit next. The only change you need to do on this screen is move it to the admin log-in only because only the admin level user will be able to see this form.
The two additional fields that you will add on this screen is the process by manager ID and process date. Go ahead and select the status field and over here, just make sure your default value is set to approve. Again, when the admin submits the form, by default, we should approve this expense. There is nobody above us to change the status from pending to approved. The manager ID, you should use the authentication field and grab the employee ID to stamp the ID. The process date is simply going to be a timestamp. Once you are done, all you have to do is click finish, and there you have it, your very next data page inside the admin folder.
The remaining data pages for the admin are going to be the dashboard data pages. Let me show you a live example. Here we are going to be building this form to filter data just like we did in the all employee folder, but for the admin, they are going to have company wide data to be able to search on so we are going to need to build a bigger search form.
We have a few charts, and underneath that we have a pivot table where we can drill into details to see additional information pertaining to each employee. Let’s go back to Caspio and go ahead and click on new data page. I am simply going to click edit on my filter data page. This will need to be a submission form hit. Next. You can include any table as your data source because we are going to be once again using virtual fields to search our data. Make sure you apply the admin level authentication and then continue. Move your category field initially. Hit next and then go ahead and include six virtual fields. Then you can go ahead and delete your category field. For virtual field number one, you can use name, go ahead and use dropdown and then select both custom values and lookup table.
Under custom values, just have search all and under lookup table, we are going to select our employee table, select the full name and behind the scenes for our value is going to be the employee ID. The advanced tab, you are going to once again receive the value of a parameter, I am calling it N, and then pass a field value as parameter. I am also calling it N, which stands for name. Let’s go to virtual field two. This is going to be department once again, drop down under lookup table, go ahead and link your department lookup table. And in the advanced tab, just pass lowercase d, which is going to stand for department. Virtual field three is going to be status. Once again, dropdown lookup table for status, and then pass lowercase s. Virtual field number four is going to be a category lookup table of categories. In the advanced tab you can pass lowercase c.
Finally, we have our two date fields, which is going to be date from Be Sure to enable calendar popup in the advanced tab, just select DF as your parameter name for both receiving and passing. Also for virtual field six. As you can see, this is day two calendar popup. In the advanced tab we have lowercase dt for day two. One more thing that you can do here is under section, go ahead and create six columns. We can move all six of these virtual fields in the same row, just like in my live example here that you can see. Then let’s have the label position be on top again. Let’s go back to the live example so that you can see how my label position is on top of my fields. Click next and just have the form refresh. When you are done, go ahead and click finish.
I am going to go ahead and click cancel, and you should now be able to see two data pages inside the admin folder. Now let’s create some charts. Let’s begin with the expenses by department. I am going to click edit. I am going to use the bar chart. Click next. Now what you need to do is select a view that is a combination of your employees and your expenses. This time we are going to be using the data source of the view that we created just a few minutes ago. Give it a name, also, admin log-in, click next. Make sure you select the filter data. Allow parameters and bridge and external parameters continue. Include these five fields as your filtering fields. Let’s continue. Now you need to configure your filtering fields for the employee ID in the advanced tab. Go ahead and receive the value. We gave it a lowercase n if empty.
Ignore criteria. Department, same thing. Go to the advanced tab, receive the value lowercase d for categories, Same thing for status, same thing. Then for the expense date, go ahead and include new criteria. Set the logical operator to end. Then for criteria one in the advanced tab, go ahead and receive the value SDF, which is date from in the standard tab. Use greater than or equal to. For criteria two, in the advanced tab, select DT for two. And in the standard tab, just make sure you select less than or equal two. Then continue to configure your chart. Under the data source settings, use the bottom radio button for your Y axis. You want to select your department field for your X axis, leave it as this. Just make sure you change the formatting to include currency. Then for your value, use the total field and then the aggregation of sum.
Then on the following screen, go ahead and just sort your data based on the sum field from highest to lowest. When you are done, you can click finish now to save your changes. You should now be able to see three data pages. The remaining charts are actually very easy to build expenses by the employee. All you need to do is just make a copy of this chart that you just created. Click edit. And the only change that you need to make here, if you go to the configuration chart options under Y axis, go ahead and just use the employee name instead of department. When you are done, click finish. To save your changes, you should now be able to see four data pages expenses by status. Once again, just make a copy of your data page, Click edit.
Go ahead and select the donut chart. This time, your filtering should also remain the same. You do not need to make any changes here. In the configuration screen, under category, select your full name as the category field, and then subcategory is going to be status. For value, you once again want to use your total field and get the sum aggregation. When you are done, click finish.
That is your fifth data page and a total by month. Make a copy of your data page. Once again, I am going to click edit. The only modifications you need to make is choose a line chart. Your filtering is still the same, and in the configuration under X axis. Go ahead and use the date submitted field. Do a date roll up based on month. The Y axis is still the same. Just make sure again to the formatting if needed.For your value, again, we are going to use a total field. Here at the bottom you can choose a marker of your choosing. And when you are done, click finish. You should now be able to see six data pages.
The final data page for the admin level user is this pivot table. If you look at my live example, a pivot table will display data. On the left, you can see all of my employee names. Across the top we have the months. Now I don’t have very much data inside this pivot table, but if I had August, September, October, you will be able to see additional months as columns. Let’s learn how to create this pivot table. I am going to click edit. You are going to have to build a new data page this time. Select pivot table, click next. Once again, you are going to use the view as your data source.
Apply your admin level log-in. Make sure you are filtering data. Once again, you will need to use the same filtering fields as on the chart. Apply the same modifications, so go to the advanced tab and receive all of your values as parameters. Click next. Then you are going to reach this screen where you can configure all the data page elements for your columns. Go ahead and include the date submitted field, and once you select your date submitted field, do a date roll up based on month. In your rows, you want to select your employee’s name, so make sure you select the name of the employee. You do not need to make any modifications here. Maybe just change the label to name. For your expenses, just go ahead and include your total field inside the values, and then select some for your aggregation. Then you can say show values as no calculation, or you can do a percentage of total if you wish on the following screen.
You can enable both totals for row and column. You can also choose to enable to download data in any one of these formats. In my example, I am actually enabling drill down reports. If you want to do the same thing you can. You do not have to, it is up to you. I enabled it. These are the fields that I wish to use in my results page. You can use the same fields if you want, or you can include additional ones. I am not going to make any changes here. In my results page, I just modified the labels because I am using the view. I do have to modify my labels to just say status for all the fields that you see in the results page. If for aggregate, you can go ahead and include that as well. Totals in aggregation. For aggregate one, all I did was include my total field as my selected field, and I am applying my sum function to get the total for all of the expenses. I chose to display 45 records per page.
This is also, again, completely up to you. I do want to have a details page. I am using my title field as the details link. In the details view, I chose to include the following fields. Again, you can copy the same fields as I have. Go ahead and pause the video here if you wish. Then all you need to do at the end is just modify each one of your fields. You can see I have dates submitted, name, title, description, category, receipt. For receipt, I chose to have this be display only as a file, and I am allowing users to download the receipt. For my status is display only. This is processed by manager ID field. As you can see, process, date by manager, rejection reason. Now, if it is a rejection reason, if there’s no data inside that field, you can go to the advanced app and just say, Hide the field blank.
This is processed by HR. Process date by HR. Rejection reason by HR. Once again, if there is no text inside this field, go to the advanced tab and just hide that field of blank. Finally, we have the pay date. When you are done, click finish and you should now have all of your data pages completed for your admin. If you look at the expense reporting, now my live example, you are going to be able to see all the data pages deployed. Once all the configuration is set up correctly. Now you are going to have the ability to select your employee click search, and all the charts, including the pivot table, will filter the data based on your selection in this search interface. If I choose a different department, let’s just say for example, marketing. If I click search now, you will be able to see all the employees for the marketing team, all the charts are filtering the correct data.The pivot table will now show you all the employees where you can now drill in and be able to see detailed results for that specific employee that you have selected. From here, you can also click on the title and inside there you will be able to see all the additional information pertaining to that specific expense.
Let’s go back to Caspio. Now, at this point we can close the admin folder and we can open up the admin and manager folder and build these two data pages. Next, let’s begin with this data page where a manager, when they log in, they’re going to be able to see all the expenses that are pending for their specific department. Let’s click edit for year. You are going to build a new data page. I am going to click edit, use the tabular format and continue. Once again, you want to use your view as the data source.
That is a combination of both the employees and the expenses table. Make sure you use the authentication for admin and manager and then continue. In this view, go ahead and filter your data, allow parameters and use bridge and external parameters. You also want to enable record level security. In the authentication field, use the employee ID and the matching field In the current data source. You want to use the manager ID. This is how a manager, when they log in, they’re going to be able to see all the expenses for their department because each one of their employees is going to have that employee ID stamped as the manager ID in the employee table. Just to show you an example of that, let’s look at our table really quickly. What notice here is that Jesse, his ID is number two. Anybody who reports to Jesse, you will see how we stamp Jesse’s ID as the manager ID. As long as you match the employee ID to the manager ID now, whoever is logged in as the manager will be able to see all the employees that belong to that specific ID. Let’s go back and let’s continue. Use the status field as your filtering field. On the following screen, all you need to do is just enter a value pending because the manager will only be interested in seeing the expenses that are currently pending so that we can approve or reject that expense.
Go ahead and use these fields as well in your results page and then continue Now going to make any changes here. Let’s move on here. You can modify your labels if you want. Just go down a list of all of your fields and make your modifications. Under aggregation, you can once again include your total field as the selected field and apply the function called sum. Continue. Here you can sort your data however you want. Let’s go and enable the details page. I am going to include the fields for my expenses table to the right, not all of them. Just take a look at my screen, pause the video if you need to, and just move over these fields and then continue. Now you need to make a few modifications. Date submitted will be display only, title, description, category receipt and total are all going to be display only.
However, as a manager I should now have the ability to update the status. When I click on the status field, go ahead and create the dropdown and add these values inside this window. My first one is pending because when the expense is first submitted, it is going to have a default value of pending. This is the reason why that will be the very first option in the dropdown, but a manager has the ability to approve the expense, so as your display go ahead and type out approved, but as your value, you want that to be stored in a table as approved because our lookup table is going to contain that same value. Reject, same thing. Then as your value, go ahead and say reject it. For the rejection reason field, go ahead and move this field in its own section the way I have it here.
Wrap that field in its own section. Then in section three you want to have the process by manager ID where you are going to apply your authentication field to stamp the employee’s ID that is processing that request. Then we also want to do a timestamp for the process date. The last thing that you want to do on this data page is go to the rules tab and create two very simple rules. Rule number one, let me click edit for you. You are going to have to click on the plus sign to added rule and then you are going to click new. I am just going to click edit. I am doing this based on the field name condition. You will see that if the status does not equal rejected in my dropdown, I want to make section two hidden. Let me explain. That text area for rejection reason, if the status does not equal rejected, we are hiding that text area.
Conversely, if the status does equal rejected, so this is our second rule, we want to make that text area required field. Come back over to the elements. For your rejection reason, make sure you choose this to be a text area. In the advanced tab you can add a little placeholder. Let me explain this just one more time. If the status equals rejected, I want to make the text area required. However, if it is pending or approved, I am hiding the text area. That is the conditional logic that we have applied on this details page. Once you are, all you have to do is click to save your changes. I am going to cancel out of it. Any manager that logs in, they are going to be able to approve or reject all the expenses.
Now for team search, we should be able to build this one very easily. You can make a copy of this data page. I am going to click edit. It is going to have a tabular format. Once again, we apply the admin manager log-in. Let’s continue this time. You want to build a search interface, but once again, we want to apply the employee ID to manage your ID because we want to be able to see expenses just for our department. These are going to be my search fields. On the search form, you want to make some modifications. For the employee name, I am using a dropdown. I am doing a lookup table to select the full name of the employee. My category is just simply going to look into the categories lookup table. We have done all of this before. For title, I am using auto complete, which works very much like Google search. We have done this already in the employees folder. Status, once again, we are going to use a dropdown and then we have a nice date range where you set the logical operator to end and we do a greater than or equal two or less than or equal too.
Then you choose the fields you wish to have in the results page and then you continue. You can group your data based on the full name if you want to. I also have the aggregation included here so we can get a sum for the total field. You can sort your data, you can enable the details page and in the details view, these are all the fields that I am including. Go ahead and pause the video and copy the same fields in your application too. Then from here, everything is going to be display only. As you can see, all of my fields are display only. I don’t have the ability to make any modifications from this report all the way down until pay date. When you are done, go ahead and click finish. I am going to cancel a lot of it. You now have two data pages built for both the admins and managers. Let’s close this folder.
Let’s go into the HR folder and let’s build these four pages. These are going to be very quick to build. As an HR level user, I should have the ability to add a user. Go ahead and click on new data page. I am going to click edit, use a submission form. I am using the data source employees because that is where we need to store all the employee information. Make sure you use the HR log-in only because only the HR will have the ability to add a user. Include all of your fields to the right, and then you need to modify all of your submission form fields. My first name will be a required field. Last name will be a required field. User role, I am using a dropdown. One thing you want to make sure you do here is make sure you remove this value.
This is going to be my very first option in the dropdown. Then my lookup table is simply just looking into that lookup table of all the user roles. Email is automatically required. We have the password field, title field, department is also a dropdown. Because we have a lookup table that contains all the department information, user photos so that we can attach an image of each one of our employees. Then we have something called manager ID. Now I am using a label here reports two. Once again, you want to use dropdown, select both for your source, and then your lookup table is going to link into the employees table and you want to grab the full name and then stamp the ID as the manager ID. When you add your employees into the table, account status will be a very simple checkbox. Then we have data created as a time stamp.Once you are done, go ahead and click finish. We will once again, cancel out of my data page.
Then let’s take a look at this data page to manage users. Not only should we have the ability to add a new user, we should also be able to manage. Let’s edit tabular format. Your data source is the employee table. HR login. Let’s filter our data. Let’s include a department field actually to the right because I want to show you that you want to set the comparison type to not equal CEO. As an HR, I don’t really want to edit the CEO’s information. The CEO should be able to just edit their own information if needed. But for anybody else, all the management, maybe all the employees, I should have the ability to edit their information. Basically all this filter does is from this table. It excludes the CEO from showing up in the report.
Include these fields as your results page fields if you want. Click next. Let’s go ahead and continue. You can also group your data based on department. Let me show you a live example. Now that I am logged in as an HR team under settings, I can go to manage users and you can see how I am grouping all the users based on department. Let’s go back to Caspio and continue. Let’s display 25 and then move on. Let’s enable details page. At a details page, you can choose to include these fields if you want. Much like the submission form, your date creative field is going to be display only, but we should have the ability to edit the name, last name. We should also be able to change the user role. If you want to promote somebody or demote somebody to a different user level, you can do so in the details page. I am going to show you a live example here really quickly. Looking at our marketing team, if I wanted to promote Stacy to manager level, I can go to details and using the user role field, I can simply change that to manager. Now Stacy will be able to have different privileges when she logs into the application.
We have email, title, department is also a dropdown, user, photo, manager ID. If I want Stacy Nower to report to somebody else, let me show you a live example. I can change Brian Jones to somebody else if I choose to have Stacy reporting to a different manager and account status where we can activate or deactivate a specific employee. Once again, when you are done modifying, click finish. Let me close this.
Then let’s build this data page now to process all the expenses as HR. This is the final step in our workflow. When the employee submits the expense initially it goes to the manager. Manager is going to approve the expense. Once the manager approves the expense, now the HR will be able to process that expense. Let’s click edit. Tabular format. I am using my data source expenses table. Make sure you use the HR log-in.
Let’s go and filter the data and let’s filter the data based on the status field. But this time you want to filter based on the proof, so it is not pending because we are waiting for the manager to approve the expense. Once it is flagged as approved, the HR should only be able to see expenses that are flagged as approved. Let’s continue. These are my fields in my results page. Let’s keep going. I am grouping my data based on the employee ID. As you can see on their aggregation on once again using the total, Let’s continue. Let’s include a title field as our details link. These are going to be my fields and the details view. Go ahead and pause the video here if you need to. Let’s configure each one of these. These are going to have some rules as well. Notice that my employee ID is going to be display only all the fields up until the status is going to be display only.
In the status, we now also have the ability to see all the approved expenses, but we can reject or process the expense now. Process by manager ID will be display only. Process date will also be display only because as an HR employee, I want to be able to see who was the manager that processed that request and at what date. Then process by HR. If you are logged in as the HR user, you want to automatically the idea of the HR employee into the expenses table. The process date will be a timestamp, but the HR rejection reason in its own section. Then you have the pay date via text field. Let’s take a look at our rules. We are going to have four different rules here. Rule number one is if the status does not equal to processed, I want to hide the pay date field.
Rule number two is if status is not rejected, I want to hide the rejection reason. Rule number three, if the status does equal to process, I want to make the pay date field the required field. Rule number four is if the status is rejected, I want to make sure that the rejection reason field is required. Let me show you a live example. Let’s go to all expenses. Process requests. Let’s go to this one by Ashley Davis. What you will notice now is that as HR, I can reject the expense or process the expense. If I reject it, notice how I am exposing the rejection reason and I am making this field required. If I say process, now the pay date field is showing and it is making that field required. This is what the rules allow us to do inside data pages is to create this conditional logic very quickly so that we can change the behavior as we select inputs in prior fields.That is all you have to do. Once you are done, go ahead and click finish. I am going to go ahead, cancel out of this.
The last data page here is company search. What you can do now is actually open up the folder where it says Team search expenses and make a copy of this data page and move that data page inside the HR folder. Once you rename it, I am going to go ahead and click edit. Next. Make sure you are using the HR login and make sure it is also using the same data source. Employee expenses. Have a search form selected. No need for record level security because HR should be able to see all the expenses, a history of all the expenses. These are my search fields by now, you should know how to configure each one of the search fields. I am not going to go through those steps.
Include fields as your results page fields. Not going to make any changes here. These are my fields for the results. You can sort your data, you can enable the details, view and in a details view these are the fields that I chose to display. Once again, in a details view, everything that you see here will be display only. In other words, you don’t have the ability to make any modifications. If you look at the live example, when we go to search expenses as HR, we have the ability to search, view the results, and then we can go into details to see additional information where everything is display only. Once you are done, go ahead and click finish. I will once again click on cancel. This completes all the data pages for the HR folder.
The last one that we have left is the manager folder. This one will have four data pages. These data pages should be very easy to build. You do not have to build this filter form from scratch. All you need to do is simply just make a copy of this one. Once you make a copy of it, move it inside the manager folder. Let me show you how to edit this data page.
Make sure you use the manager log-in. Click next. Click next. You should now be able to see six different virtual fields because you made a copy of the data page from the admin folder. Go ahead and just remove the department field. Notice how you will see name, status, category, date from, and date to, the admin has the ability to search based on department, but our managers do not really have the reason to do that because once they log in, they just need to see information for their department. You do not need to make any changes here because it maintains all the settings from the copy data page. Even if you go into the advanced tab, you should be able to see these configurations. Click next and just have the form refresh. Let’s cancel out of it.
Now let’s build the remaining data pages. The last three are going to be just very simple charts. Expenses by employee. I am going to click edit, show you that I am using a bar chart. My data source is the view. I am using the manager log-in. This time for this chart, you want to enable record level security to only show you the data for that specific department. You click next. These are my filtering fields that I am including. Then you configure the filtering fields the same way as before, where you receive the value and then you configure the chart. All you have to do is select the bottom radio button for your Y axis is going to be your full name of the employees. Then we have our X axis, and then we have the value one, which is the total field.
You want to grab the sum. Then on the following screen, what you want to do is just simply sort your data based on the total field from highest to lowest. When you are done, click finish. I am going to go ahead and cancel out of it and then make a copy of this data page. All you need to do is just modify two changes here. Once again, using my bar chart. Once you reach the configuration screen, use the bottom radio button for your Y axis. You want to select status, make sure you have the right currency applied here in the formatting. For value, you want to grab the sum for your total field. When you are done, click finish. I am going to go ahead and cancel out of this. One last data page left to build is total by month. A total by month basically is going to be a line chart.
Let’s click edit, select the line chart, click next, manager log-in. Once again, you want to filter the data based on department, include your filtering fields. And once you reach the configuration screen, your X axis will be the date field and you want to do a date roll up based on month. Your Y axis is going to remain the same. For the value, you want to use the total field. Once again, you can apply a specific marker if you want. Once you are done click finish. I am going to go in and glance at a lot of this and you should now have all 28 data pages completed inside your account. This application is going to be made available for download, so you can either follow along and replicate what we do in the video, or you can quickly import this entire application into your account so that you can see how all of the data pages were built.
One more modification that I forgot to do under filtering. If I click edit here, which is very important, once you reach the virtual fields under security, you also want to make sure you select limit lookup values based on user identity. You want to select department for both. Because when a manager is filtering, these dropdowns should only filter out based on the department. The manager should not be able to see all company wide data, they should only be able to see values for their department. Just go through each one of these virtual fields and make sure that you have the right security applied. Not all of them will have it, I believe it is just a very first virtual field. It is just the top virtual field when you are looking at the names of the employees from the employee table, you just want to be able to filter the employees based on your department. Once you are done, click finish. I am going to cancel a lot of it. If you manage to build the entire application in tandem with the video, that is excellent. Thank you for doing that. Hopefully we’ll get some good comments from you in the comments section. I hope you enjoy the building part of the data pages in part four and in the last video of this ultimate video guide, I will teach you how to apply this entire application to a website. Thanks for watching. I hope to see you there.
Share this post: