The Caspio Low-Code Platform
All the Tools and Support You Need
Unlimited Users With Every Plan
Ned Pajic:
Hi, and welcome to this video guide on how to build a knowledge-based application. My name is Ned. I am a technical evangelist at Caspio, and in part four of this video guide I’m going to teach you how to build all the forms, reports, charts, and some advanced functionality using SQL in order to power the dashboards.
Let’s take a look. Now that we have our foundation in place, we have four tables, we have our two views to filter out users from admins and we have our logging screens created. It’s time to begin building all the forms and reports, but before we actually begin building these data pages, let’s go ahead and talk about these folders that we have here at the top. Folders help you stay organized inside your account, and the way I want you to use the folders is very simple. For every user group that your application has, you should create a folder for that user group. So for example, in this application we have admins and we have users. So I’m going to create one folder for admin and one folder for users. And inside those folders is where you’re going to be moving these data pages into because if you don’t use the folders, you’re going to end up with a long list of data pages and it becomes really difficult to find the one that you need to edit. So I definitely recommend that you create your folders first before you begin building data pages.
Now don’t be confused by this term data pages. Data pages are application interfaces that you will be deploying or embedding onto your website. It’s what your end users are going to be interacting with. If I go to my live example, you will see this login screen is deployed on my website, but the end user is interacting with this data page. So let’s go ahead and begin building all the data pages for users first. And then once we’re done with all those data pages, we’re going to move on to the admin side.
The very first data page that I’m going to build is a very simple HTML data page. This one here. You’re going to hit next. And let’s give this data page a name. I’m going to call this user login redirect. What this data page is going to allow you to do, once we configure it, is to allow the user to go from one webpage to another webpage. I’m going to use a specific style that I have for this application and I’m also going to use a specific localization. Of course, you’re going to have your own option here, what you can select. You can also fully modify your style and localization if you want to change the aesthetics or the regional settings. For restrict access, you’re going to enable this checkbox and you’re going to select user login.
This authentication that we built in the prior video is coming from this object here. And once you actually apply that authentication to this data page, it’s always going to have that login screen on top of the data page. On the following screen, personally, what I like to do on this screen is disable the HTML editor and input my own code. Now we’re going to write a very simple one line code that’s going to redirect the user from one webpage to another. So it begins with script. Just go ahead and type this as well inside your account and you’re going to write a very simple line here that reads window dot location equals sign. And then in between the quotes, you’re going to put your destination url. In other words, when the user logs in, where do you want them to be redirected? And in my template that I’m building, I call that page dashboard dot html.
Close your quotes, put a semicolon, and then here at the bottom, just write script like this. Okay, so when you’re done, go ahead and click on finish and you should have that very first data page created. Once you embed this data page into your website, the effect that you’re going to have, or the behavior, is this, the login screen is going to be deployed on a login dot html page, but as soon as I log in as a sample user, you will see how it redirects me to a page called dashboard dot html. The benefit of doing this is you get to hide the main navigation menu from the login screen. Let me log out one more time. You can see how we don’t see the navigation menu until we have actually logged in. And again, as I said, it’s a very simple script. All it does is redirect the user from one webpage to another.
The next thing that we’re going to build is to have the users be able to add the article into the knowledge space. Just to give you a live example, this is the form that we’re going to be creating next. Now as we build these forms in this video guide, I’m going to show you very slowly how to build one form, but once you get the hang of it, we’re going to speed things up just for the interest of time so that we can get through all the data pages as quickly as we can. But feel free to pause if at any point you get stuck, you can always go back if you need to. So let’s build a new data page and this time we want to build a submission form. So select that option, click next. And as for your data source table, you want to select the articles table because when you submit the form via the web, you want that information to be stored inside the article table.
Let’s give it a name, add new article for example. I’m going to use the same style and the same localization. And once again, we want to apply our user login because when a user logs in, only then will they have access to submit that form. The fields that I need to have on this submission form is the user ID, department, title, attachment. I don’t have a need for count, I don’t have a need for internal notes, that’s for the admin, but I do have a need for the status field. So here you have the option to choose whatever fields you want to include on the submission form.
On this screen is where you’re going to modify each of your fields. So you can select the field here on the left hand side and you can make modifications on the right side. So the user ID field, what we need to do with this field is basically hide it and use the authentication field user ID. What this means is when you log in as a specific user, it’s going to verify your ID and it’s going to grab that ID from the user table and stamp that user ID in the article table. This is how you link the articles to the users. This is how we keep track of who is the user to submit that article because now we can stamp that ID from the user once they log in and upon submission we can automatically stamp that ID in the related table of articles.
For the department field, we have a lookup table that contains all the departments. So what we can do is select dropdown, let’s do both custom values and lookup table. Under custom values, I’m going to say select department, but make sure you delete the value here because you don’t want this to be stored inside your table if the user accidentally leaves this as the option when they hit submit. For your lookup table, we have the table of departments, so select that and then just make sure you select a department name instead of the ID in these two dropdowns. Title can be a text field. I’m going to make this field required, which means they have to fill in some information before they can submit the form. You don’t want the end user to submit a blank form, it’s just going to create a blank entry inside the table. For the article, let’s change that to a text area instead of text field.
If you leave it as a text field, let me show you the live example. This is your text field and this is your text area. Text area is essentially a bigger box where you can actually see what you’re typing if you have a lot of text. Now one additional thing that I want to create for my text area, go to the advanced tab, enable a rich text editing toolbar and select standard. Now you can use simple or advanced. I’m going to pick the standard one. And when you enable this option in the advanced tab, what you’re essentially doing is creating this toolbar here on the top. So as the users are typing some text, you can apply some aesthetics to it.
As for the attachment, you have up to 15 megabytes that you can attach for any given file. I’m going to keep it at 15 megabytes. That should suffice. For the status field, I’m going to turn this into a dropdown and very simply I’m going to list two options down below. One can say “in progress” and the other one can say “submit for review.” Now for submit for review, this is what the end user is going to see on the front end when they’re looking at the dropdown option. But when you actually submit the form, I would like to stamp the value as submitted for review and I will explain why once we start to develop admin data pages. So we’ll take a look at this and come back to it at a later time. For now, just go ahead and rewrite that as “submitted for review.” But here on the left side under display, it should say “submit for review.”
The next thing that we want to is add some conditional logic on the form to add some dynamic functionality. To do that, we’re going to go to the rules tab, and here you’re going to click on the plus icon to create your very first rule. Click new here to create the criteria. And we’re going to simply select the field status here and we want that to equal to submitted for review. After that, we need to create the action and the action is simply going to say, go ahead and require the department field and also require the article field. When you do this, what’s going to happen on the form is let me show you the live example. If you leave it as in progress, only the title field is required. However, if you select submit for review now you’ll see that both the department field and the article field are required.
And that’s essentially what you’re doing by creating the rule here and enabling this action to happen as you select something in the prior field. Once you’re done, go ahead and click on next to move on. Here, you can customize your confirmation message if you want. If you’re happy with this confirmation message, all you need to do is click on finish to save your changes, and you now have two data pages created under users, one that allows us to redirect when we log in. And the second one is to simply add or create a new article in the article’s table. You can preview the form to see what it looks like. Now I’m seeing the form because I’m already logged in. If you see a login screen here, just login as a user from your table. If you forget the credentials, go back to your table, open the table and find out who your user is.
And then once you log in, you should be able to see the same form and take a look at the functionality. If I leave it as in progress, only the title is required. However, if I change the status, now you see that both department and the article field is required. So let’s make this submission. Let’s test out the form, just see what this looks like inside our table once we submit the form. So we’ll select department, maybe sales, we’ll say title, new pricing. And for the article text, I’m not going to spend too much time here, I’m just going to say we have rolled out a new pricing plan. You might add a website here or link to show people what the new pricing is. And let’s just say, in progress. I’m not going to make any attachments. Hit submit. Your submission was successful, you can close the preview tab and now when you go inside your table and open up the articles table, you should be able to see that submission inside a database. Let’s go back to data pages.
Next thing that we’re going to do is very quickly create four different types of reports. Let me give you a live example. We’re going to build these three reports in progress, submitted for review, resubmit for review, and also unpublished. So these are four different data pages. Let’s go back to Caspio, click new, and this time I’m going to build a report. And let’s go at the tabular format. Hit next. Your data source table needs to be the articles table because you’re trying to output the information from this table.
Let’s call this in progress. Same style, same localization, and once again, make sure you enable authentication and select your user login. Let’s move on. On this screen, you have a choice. You can either build a search interface or if you just want to display the data without having the ability to search, then select a second radio button.
Record level security is very important. Make sure you check this box and make sure you select user ID from the second dropdown because what this is going to do is when a user logs in, it’s going to verify their ID from the authentication table and filter out the results that only pertain to that user’s ID. So you want the user ID from the related table and you want to user ID from the authentication table. On this screen, we want to include our status field because we’re trying to filter out all the articles that are currently in progress. So let’s hit next and for the value we want in progress, this means when an article is stored inside a table and has a status of in progress, this report is only going to show you articles that contain that value. So let’s move on. On the results page, it’s up to you what fields you want to include on the results page.
I’m just going to keep things simple and just include title, maybe department and date submitted. Let’s move on. I don’t want the end users to have the ability to edit information via the results page, so let’s continue. I’m not going to make any changes to my results page fields. Let’s move on. Let’s go ahead and display 25 records per page, hit next. I do want to enable a details page. So let’s hit next again, and in my details page I’m going to include a few more fields. So let’s go ahead and include department, title, article attachment, status, and also let’s include dates submitted and date updated and continue. And now let’s go ahead and configure the details page and you’re going to configure the details page exactly how you did the submission form to add new article. So my department, I’m going to turn this into a dropdown and very quickly I’m going to select my lookup table that contains all the department information.
For my title, that’s going to be a text field, make that field required. Article is going to be a text area. And the advanced tab, don’t forget to go ahead and enable this check box for rich text editing. Select standard for the attachment. I want this to be a file and display as a file. For my status, however, let’s go ahead and turn this into a dropdown once again and we’re going to have in progress because that’s the current status and I want to be able to say submit for review, but don’t forget to change the value to submitted for review. And my dates submitted and date updated, these two can actually be display only so that we can see when we’re submitting something and when we’re updating something at any time. On the following screen, I’m just going to go ahead and disable this checkbox, navigate the next previous record and hit finish to save my changes.
So let’s go ahead and preview this data page and you should be able to see one entry inside the results page because this is the one that we submitted a couple of minutes ago when we added new article to the table. And the reason why you’re seeing this entry display in the report, if you go back to your table of articles, you should be able to see that article has a status of in progress. If this had something else, then this report would not filter out that record. Let’s go back to data pages and inside the preview now you can also click on the details link and you can see the details of that article. There’s one thing that I actually forgot to do. I forgot to enable conditional logic, so we do have to repeat that step as well. So let me close the tab and let’s edit that report.
And let’s navigate very quickly to the details page, back one screen. And here under the rules tab is where you once again create that same conditional logic. When you select submitted for review, I want to require the department field and also the article field. So very quickly we’re going to insert our field status and I want that to equal to submitted for review. And my action is going to be required department and also require the article of field. And now when you’re done, click finish, you can preview once again just to make sure that it’s working correctly. So in details, if you select submitted for review, you will see how both of these fields are now required. Close your preview tab.
Now to create the other reports. It’s actually very easy. In Caspio you can make copies of data pages, you don’t have to build them from scratch. So what I’m going to do is very quickly click on more, click duplicate and then change this one to submitted for review. Hit save, but I do need to make a few modifications. So I’m going to click edit and just go through all the screens. For example, for status now I’m not interested in seeing in progress, I would like to be able to see submitted for review. So if in my table the status changes to submitted for review, this report is going to display those articles. And in the details page, you no longer want to be able to edit because once you submit an article for review, those are the final changes. Now it’s up to the admin to either approve that article or reject it. So instead of these being editable fields, we’re going to simply just change these to display only.
So let’s change this as well. Article. That’s fine. For the attachment, this can be a file for download, but you want to actually just display this only. Once again, you don’t want the user to be able to change the attachment once the article is submitted for review. Status, you actually don’t need in the details view because once it’s submitted for review, we already know that this article is submitted for review. So you can actually remove that from the details view. And when you’re done, click finish. So if you preview the data page, now you will see no records found because at the moment inside the table there are currently no articles that are submitted for review. However, just so I can show you the behavior, let’s go ahead and preview in progress, go to details and change the status to submit for review. Hit update. Now you will see no records found, but if we go back to the second data page that we just created and hit preview, you should be able to see it. And in the details view everything is display only. Let’s close the preview tab and let’s continue building the rest.
Next data page that I’m going to build is the one that’s asking the user to resubmit for review. So let’s make a copy and let’s call it resubmit for review. And let’s go ahead and edit. There are a couple of changes that I want to make to this report as well. So let’s hit next a few times. First and foremost is the status. So let’s change that to resubmit for review because this type of status is also going to be stored inside a table. And I want this report to only filter out the articles that the admin asked the user to resubmit for review. Not going to make any changes here. Let’s move on to the details page and inside the details page, the only change that I want to make is to my status and what I’d like to do here is change this from in progress to resubmit for review because this will be the default status when the admin changes the status to resubmit for the review in the details page, this will be the first thing that should show up. And then as the user, I can also submit that article for review once again for the admin to take a look at.
So when you’re done, click on finish to save your changes. And even right now, when I preview this data page, you will see that there are no records found because currently in the table there are no articles that are flagged as resubmit for review.
Let’s close the preview tab and the last data page that I’m going to build is the one that’s showing all the articles that are currently unpublished. So once again, I’ll make a copy of the same data page and let’s call it unpublished, hit save. And once again, let’s make a few modifications.
Let’s go to our status field and change the value to unpublished. Let’s go to the details view. And in the details view, I’m also going to include the internal notes. And the reason why is because if the admin decides to unpublish an article, we should be able to provide some internal notes to the end user as to why that article is unpublished. The internal notes field, let’s change that to a text area. So then maybe the user and the admin can communicate back and forth. And for my status, I’m going to change the in progress to unpublished because if the admin decides to change the status to unpublished, that should be as the default value. When the user looks at the details page, the first thing I should be able to see is unpublished. And then I can submit that article once again for review if I want to publish that article once again.
When you’re done, click on finish and just like that, within just a couple of minutes, we were able to build four different types of reports that filter out the data based on the status inside a table. And as we began to look at this functionality from the admin perspective, you’ll be able to see the entire workflow of the article as it goes through a life cycle from in progress to submitted for review, unpublished, et cetera. Let’s move on.
Next, let’s go ahead and create two additional data pages here. Maybe the first one we can build is for the user to update their profile. And the second data page is where a user can update their password. So let’s go back to Caspio and let’s build these two data pages very quickly.
To update the profile, we want single record update where a user can update their information. So let’s continue. And this time we want as our data source, we want to select the user’s table because that’s where all the user information is stored. Let’s call this update profile, something very simple, restrict access to our users. And let’s move on. Now once again, we need to take advantage of record level security. So from the authentication table, we want user ID and the matching field in the current data source. We also want user ID. Let’s move on.
Now it’s asking me what fields do you want to include on the web form. So what information do you want the end user to be able to update? So let’s have them be able to update first name, last name, and perhaps email. Let’s continue. First name is going to be text field. Make sure that’s a required field because you don’t want the user to delete their first name and then hit update. Then you’re going to have a blank entry inside your table. So something has to be filled in. Last name, same thing. And email. We have a special element called email, which is looking for the at symbol and the period afterwards to make sure that it’s in a correct format. Hit next. I don’t need to make any additional changes here. Let’s hit finish.
And now if you preview the form, you will see that I have the ability to update my name, my last name, and also my email. Don’t worry about the aesthetics, the template that I’m using when you embed this form, the style sheet that’s with the template is going to supersede the style that’s in Caspio. So all of the fields and all the labels will be aligned correctly. So let’s close the preview tab and let’s build one more data page where the user can update their password.
Once again, we’re going to use single record update, hit next user table and let’s call it update password, restrict access to our users. Hit next. Once again, we want user ID to user ID, hit next, and let’s include the password field. Hit next one last time. And now for the password, what we want to do here is actually enable this checkbox require current password validation, which means that the user has to enter their old password first before they can update it to something else. And that’s really the only change that we need to make here. You can also enable some password options if you want password strength and if you want minimum strength required to make the password more secure. Once you’re done, very simple, click on finish and you can preview this data page as well. And now if I want to update my password, I have to input my old password first and then input my new password and hit update.
Let’s close preview. Now I don’t know if you’re able to tell, but we already have eight data pages built in just a couple of minutes. This is the power of the Caspio platform. You can build this functionality once you get the hang of the data page wizard and how things work. You can build all these interfaces very, very quickly. And once you’re done building all the interfaces, you will be able to deploy this application to your website, which I’m going to show you in the last part of this video guide.
Now let’s go ahead and create a search interface where users can find all of the published articles. So this report is only going to show you articles that have been published by the admin. And in order to do that we need to create an additional view. So let’s go over here to this object and click on new view and let’s call this KB filter published articles.
Of course you can call it whatever you want. That’s easier for me. And we want to include the articles table to the right because again, all the articles are stored inside that table and I only want to filter published articles from this table. And here’s how we do that. Hit next, using the criteria tab, if you recall from an earlier video how we were able to filter out users from the table, we’re going to use the same method to filter out published articles. So let’s move over to field element. And the only thing that I’m interested in is that my status field equals to value published. So any article that has a status of published will be listed inside this view. So now when I open the view, you will see that there are no entries because currently inside a table there aren’t any articles that are published.
And you can verify that very simply by going back to your article’s table, opening it, and you’ll be able to see that there’s only one article with this status. However, if you do end up changing the status, let’s just do that on the fly right now, let’s change that to published and you go back to your view, you should be able to see that article now inside the view. Again, the view is just helping us filter information from the table.
Now let’s build that data page. So let’s go back to data pages. And inside the user folder we’re going to hit new data page, build a report, tabular format, and hit next. My data source now is not going to be the article’s table because this table contains articles that have every status. I want to use the view that’s just filtering published articles. So when you select your data source, you can either link your data page to a table or to a view.
In this case, I want to link it to my view. Let’s give it a name, we’ll call it search published articles, same style, same localization, and I’m going to enable my security and use the user login. Hit next. This time I want to build a search interface. So let’s go ahead and select that radio button. As for record level security, you no longer need to enable this because when you search articles, I should be able to see articles published by all the users, not just my own. So we don’t have a need for record level security in this report. Let’s move on. Now, what fields do you want to use in the search interface? Well, we can use the same ones as in my live example. We have department, title, article, author, date submitted. So let’s include department, title, article. Let me just see which ones I have, author and date submitted.
So we’ll include user ID, which is going to be the author and also date submitted. So these are my five fields. Let’s move on. As for my department, I just have to make a modification to my label. When you create the view, it carries over the table name as well. So let’s just modify the label and let’s have a dropdown. We’ll include both custom values and lookup table. The reason why I want both is for my display, I want to say search any as in search any department, make sure you delete the value because this doesn’t exist in your table. So when you leave this option and you click search, it’s going to find all the results across all the departments. For my title, once again, I need to modify my label and I’m going to use contains instead of equal. If you leave it as equal, then you have to know the exact title of the article that you’re looking for.
But if you select contains, it’s more like a partial keyword search. If you type in a letter and you click search, it’s going to return all the articles that contain that letter. For my article, let’s do the same thing, it will include contains. You can go ahead and bypass this warning message. It’s just letting you know that it might slow down your data page if you have a lot of text inside that field. I have actually never seen any performance issues with this, so just feel free to bypass that, user ID. It automatically created a dropdown for me because if you set up the relationship between your two tables like we did in video number two, Caspio automatically creates this dropdown for you inside the search interface. But I don’t want just the source to be look up table or view. I want both because under custom values, I want to be able to search any author, delete the value, and let me just modify my label to say author.
And finally, date submitted, let’s modify the label once again. I want to create a date range here. And in order to do that, we’re going to insert what we call new criteria, which you’ll see two additional options underneath your date field. Make sure you select the logical operator and set that to end. And your first criteria is going to be in the label. You can say from, and then your comparison type is going to be greater than or equal to. And your second criteria is going to be date submitted to. And this is going to be less than or equal two. So basically greater than or equal to this date, less than or equal to to this date. That’s essentially what you’re creating with these two criteria. Let’s move on. On the results page, I would like to have the title of the article. Well, you know what? Let’s take a look at our live examples.
So I see what type of results I’m showing. So we have the title, department and author. So title, department and author, hit next, no editing capability for the users. Only the admin will have the ability to edit. So here’s my title. Once again, I’m going to modify my label. Here’s my department and here’s my user ID, which I’m going to rename to author, hit next. I’m enabling link to search form. That’s fine. Let’s display 25 records per page. Let’s move on and enable a details page. And in the details view, let’s take a look at our live example to see what we have in the details. So everything is going to be display only and we have title, article, department, author, and date submitted and date updated. So let’s go back. We’ll include title, department, article, if there is an attachment, we want that as well. And we also want this field called count because when a user looks at the details of an article, we want to automatically add a plus one counter on that article.
So in our dashboard view, we can actually see how many times that article has been viewed. And this is a built in feature in Caspio. So just include this field. And we also said we want dates submitted and date updated, title. Once again, let’s modify our labels here. Department, article, attachment. As for your attachment, what you want to do here in the advanced tab is just say hide field if blank. So if that article does not have an attachment, I just want to hide that field because it might take up too much real estate on the details page if there’s a field and there’s nothing next to that field. For your count, what you want to do with this is click on the form element and just do a page view increment. And then we have date submitted and date updated. Let me see if there are any additional changes that I want to make.
Yes, I also want to now add this button called feedback. And to do that in Caspio, you’re going to insert what we call header and footer, okay? Or you can also insert an HTML block, it’s up to you. And inside that header section, you can either go to the source to add your own custom html or you can disable this toolbar by going to the advanced tab and disabling it this way. Here you have to add a hyperlink that’s going to redirect you to a different webpage. Notice in my live example, when I click on the feedback button at the moment I’m on this page called search articles dot html, but once I click on this button, it’s going to redirect me to a page called feedback dot html. So it’s a very basic link that’s taking me to a different page. But in the process I am also passing the ID of the article and I’m also passing the title of that article.
We’re going to be passing values in the url. This is a common industry standard. Almost any database application that you come across uses the ability to pass values in the url and they’re used to actually pass information from one table and filter information on a different webpage or pass that information and store that information in a different table. This is how you create relationships between your tables and I’m going to teach you how to do that right now.
So let’s go back to Caspio and let’s create a very basic HTML hyperlink. a href equals, and then let’s just call this feedback. This is the name of my link, okay? And in between the quotes is where you put destination url. In my example, I’m calling it feedback dot html. That’s the name of my webpage. So I’m just going to call this feedback dot html. And now to pass those values from the table, you’re going to add a question mark to initiate the string and then you can call it whatever you want.
The parameter name, you can call this whatever you want. So let’s call it aid, which stands for article id. You add an equal sign and then you’re going to use this button to insert that article ID. Click okay, I’m also passing the title. So let’s add an ampersand here, T for title. Once again, you add an equal sign and then you insert the title field. Basically what I’m doing with this is every time I click on this link called feedback, I want to go to this webpage. But in the process I want to pass the idea of the article. And I also want to pass the title and you will learn why we’re doing this in just a couple of minutes. But for now, let’s click finish and let’s preview the data page. And there’s my search interface. When I click search, I should be able to see one article that we published.
I can go back to the search interface by clicking on that button, I can go back to the results page. And when I go to the details page here, you will see that link called feedback. Now I’m not going to spend the time customizing the feedback button. If you look at the application that it’s available for download, you will be able to see how I configure that HTML link to include some inline CSS to create a button instead of this hyperlink. If I click on this, it’s not going to take me anywhere because I do need to deploy this to the website in order to see how we pass those values to a different webpage. But for now, if I hover over this link, and if you look down here in the bottom left hand corner of my screen, when I hover over, you will see that I’m passing aid equals, and then the article ID and I’m passing the title, which in this case is new pricing. Let’s close the preview tab. The next thing that I’m going to build is that feedback form.
So when you actually click on that feedback button, you need to build this form in order to submit that feedback and link that feedback to the article. So here’s how we do that. We’re going to build a new data page.
We need to build a submission form, click next. And if you guess the feedback data source here, you’re correct. We’re trying to submit information to the feedback table, let’s call it add new feedback, restrict access to our users, hit next. I need all the fields on the submission forms, let’s move them all to the right. And now for the article ID, we learn how to pass the information. That’s where we added that question mark, parameter name. And then we pass the value. Now we have to learn how to receive that value. And to receive it, all you need to do is go to the advanced tab, receive the value or parameter externally, and you want to call it the same exact way as you did when you passed it. And we call the parameter aid. Next thing that you may want to do is hide that field.
I recommend that you leave it as a text field for now so that you can see how that value is being received. So I’m going to leave it as a text field, I’ll leave it exposed for now, but once we figure out that it’s working properly, we’ll come back to this data page later and simply just hide that field. Okay, the user ID, we already learn how to do this. All you need to do is hide that field and use the authentication field to stamp the user’s ID, so we can track and see who was the user to have submitted that feedback. For my helpful field, we can turn this maybe into a dropdown and we can say, yes, helpful, no, not helpful and somewhat. And for my comments field, we can turn this into a text area so that we have more real estate where we can input our thoughts and our feedback. Once you’re done, you can at this point click on finish.
And I can’t really demonstrate this right now, how we pass those values and receive those values until I deploy this application to a website. But basically what we’re doing with this is from this data page where we search published articles and we go to the details to submit feedback, we’re going to be passing the article ID to the feedback form, and when we submit that feedback form, it’s going to stamp the article’s ID into the feedback table. And if you have the article ID in both tables, well now you have a common value between them and you have a relationship between those two tables. But it’s all going to make sense in part five of this video guide. Once we deploy this application to a website. The last thing left to do on the user side is to create this dashboard. Now, I’m not going to actually create this entire dashboard, I’m going to build one chart and I’m also going to show you how to populate these numbers inside the boxes.
When you download a template that you get via email, just import the application into your account and you will be able to see how all of these data pages were built. But for now, what we’re going to do is build one chart and also these boxes on the top to show you how we populate these zeros with the actual numbers from the database.
Go back to Caspio and create a new data page. And to create those boxes across the top, go to reports and select details data page, hit next. The data source inside this report is not really necessary, but go ahead and just select articles. Let’s call this data page user dashboard and make sure you restrict access to your users and hit next. No need for search forms. So let’s just have a predefined and hit next. We’re not filtering data based on any one of the fields. Let’s continue.
And in the details page, you are going to have to include at least one field. So pick any field that you want. I’m going to use count, hit next. And this screen is where you’re going to have to input some calculated values along with SQL in order to compute those values that are coming from the tables. Go ahead and insert an HTML block, go to the advanced tab and disable the HTML editor and go back to the standard tab. Now the next thing that you want to do is open this webpage in a notepad plus plus because then you’re going to be able to see the HTML and the structure of this webpage.
Here it is, and you have to locate those boxes that are written in html. So just go ahead and copy that code. So these div containers that you see, there’s a total of six of them. You’re going to want to copy that and then go back to Caspio again. And inside that HTML block, just go ahead and paste all that code. Eventually what you’re going to do is when you save your changes, you’re going to replace that code in the HTML file with the Caspio data page, and it’s going to render those boxes the same way as it did inside the HTML page. But the reason why we want to copy that code inside the Caspio data page is because now when we calculate these values, we can replace these zeros with the actual calculations. The next step is to insert a calculated field. And the first one that I’m going to compute is in progress.
I am trying to compute all of the articles that are flagged as in progress, and I already have my SQL statement written. Let me just copy and paste it in here, and then I will explain exactly what it’s doing. So it’s two lines of code. Basically what we’re doing here is counting the article ID from the article table where user ID is the authenticated user that’s logged into the application. And we’re looking at the status equals in progress. We also have this is null function. If there is a blank entry in the table, we want to replace that with a zero. Okay, so if there’s nothing inside a table, we want to show number zero. However, if an article does exist and it has a status of in progress for this user that’s logged into the application, we want to count the number of articles that that user currently has in progress. Now you can hit preview at this point to see what that looks like, and you can see how it has a zero here for in progress. We’re going to also replace all of the rest of them as well. And eventually we’re going to hide this field because we don’t want this to be displayed on our dashboard. So let’s continue inputting all of the other SQL statements by adding additional calculated fields. Let me just copy and paste them.
This one here is going to compute submitted for review, and you can see how we’re looking at the status that it’s submitted for review. Let’s insert another one. The third one is going to show all the ones that are published. So if you do have a published article inside a table, it’s going to show that published article. Let’s rename this to published and let’s click preview. And you can see how there is one published article inside a table. If you recall from a couple of minutes ago when we went to our table, we changed the status from submitted for review to published. This is why this SQL statement at the moment is computing this as one. Let’s close the preview and let’s continue. The fourth one is going to list all the ones that need to be resubmitted for review. So let’s give this a title resubmit for review. Let’s add two more. The fifth one, it’s going to show all the ones that are unpublished.
And last but not least, let’s add one more calculated value. And this one here is actually going to count the number of feedback that exists in the feedback table for that specific user. And we’ll just call this total feedback. Now remember, I do want to hide this count field because I don’t want this to be displayed on my dashboard. So I’m going to go back to this HTML block, and at the very end, I’m going to insert a very simple table tag to hide that field. And we’re going to say table style equals display none. I’m going to add one more HTML block and move it at the bottom underneath the count field. And I’m just going to simply close that table tag.
All I’m doing is wrapping this field in between two html blocks. In the first one down here, you can see table style display none. In other words, I don’t want to display anything in between that table. So we’re going to be hiding that count field. You can hit preview now again, and what you want to do now is basically take these calculated values and feed them inside that HTML that we grab from the HTML files. So here’s how you do that. These are my calculated fields. Here’s my HTML code right over here, and all you need to do is replace the zero. So for in progress, remove that, use the insert button to add a parameter, and we’re going to simply just add in progress as a number, and you can see how that zero is now replaced with that calculation of in progress. So just go down your code and replace all the zeros. So this is going to be pending here is submitted for review, resubmit for review. So let’s replace that one as well.
So very simple, just go down the code and replace all the zeros with the correct calculated fields. So these are published, I believe we have two more. Here’s unpublished, and we also have feedback. Let’s preview again. And now you can see how these calculated values are actually feeding inside this HTML code that we grab from the HTML file.
And the very last thing that’s left to do here is to hide this entire section of all of the calculated fields because we don’t actually need to display that. So let’s go back to Caspio. And you could include them actually with the count field. So if you click on the very first one and hold control on the keyboard and just keep clicking on the rest of them, you can move them down. And now all of these fields that you see here are going to be hidden. So if I hit preview now, the only thing that’s showing is the actual HTML code along with the total from the table.
Now, I want you to remember here, there’s one published article. Let me show you how this works. Okay, let’s just go ahead and go back to our table now just for a second. I’m going to save my changes, go back to tables of articles, and let’s just manipulate this value here from published to say in progress. Let’s save that. Go back to data pages in the user folder, preview the dashboard data page again, and now you will see how we have one in progress and now we have zero published. That’s what those SQL statements are doing. They’re basically calculating information from the table in order to display that data inside those boxes later on when we embed this into a webpage.
Let me show you actually very quickly, I know I said I’m going to save this for the last part of the video guide in part five, but I do want to show you now that if you were to deploy this data page user dashboard, and you were to grab the Caspio embed code and close this screen in your HTML file, if you now remove that entire code that we grabbed earlier, all of those blocks, and replace it with a Caspio data page, let’s go ahead and remove this branding.
If you now replace that entire code with the Caspio deploy code, and if you publish this page, let me show you what that looks like and look at it, you will see how those boxes are brought back into template. And we can also see those calculated values working correctly as we pull information from the tables. When you download the template, this is already going to be configured for you. All you’re going to need to do is edit the data page and see how it was created. But if you’re interested in learning, maybe you can follow the same process as I just did and try to replicate that inside your own account.
The last thing that we’re going to do on the user side is create one chart. Now, this dashboard has more charts, but I’m just going to go through the steps of creating one. Like I said, when you download this template, you’re going to be able to see all the charts in the user folder.
But let’s click on a new data page. And the chart that I’m going to build looking at our live example is this chart here that shows top five authors or top five contributors. In this case, it happens to be Sam in my live demo. Let’s go back to Caspio and let’s click on charts. And the type of chart that I want to build is a bar chart, but of course you have the flexibility of building any type of chart that Caspio gives you. You have a variety of options. I’m going to stick with this one, hit next. My data source needs to be the article table because we’re trying to count all the articles per user. And let’s give this a name. We’re going to call this total articles by user, restrict access to your user level role. Hit Next.
No need for a search interface, let’s just predefine the data, and I want everyone to be able to see those top five contributors. So no need for record level security. I am only interested in seeing published articles. I don’t want to see articles that are currently in progress and have those count towards the overall score. So I want to include my status field and I want the value to be published. Once again, I only want this chart to show me published articles of all the top contributors. And this is the screen that you’re going to use to configure the chart. So the title itself, my template already has the title, so I don’t actually have to include that here. For my data source, you can create the series from the data in a single field or a separate field for each series.
In this example, I’m going to use the separate field. My Y axis is going to plot my users, so authors. My x axis is going to say total because I want to be able to see the total of published articles. And for my value, what I’m trying to do is count. And for my value, I want to be able to count the number of users inside this table. Hit next. Here, I want to be able to sort based on count on user ID and show me highest to lowest. And I only want to see top five contributors. So just change this to five. And now this chart is going to display just the top five contributors. If we click on preview, it’s an empty chart because at the moment in our articles table, there are no published articles, so it shouldn’t really display anything. However, like before, let’s go ahead and finish and save our changes.
Go back to the table and let’s just quickly manipulate that one article that we have inside a table and change the status to published. So in this case, Ned, myself, I have one published article, which means that if I go back to my chart and we hit preview again, I should now be able to see one published article by Ned. When you’re done, just close the previous screen. And that’s it on all the user data pages.
For the interest of time, we are not going to build all the data pages on the admin side, but I am going to show you how to build two data pages for the admin so that you can see how we apply the admin authentication and some of the differences that you see in the report, what the admin can see versus what the user can see. But do remember when you download this application and you import the application into your account, you are going to be able to see all the data pages on the admin side and all the data pages on the user side. And I do recommend that you just go through each one of the data pages to see how they were created, but it follows the same process as building all the user level data pages.
Okay, let’s close the folder and let’s take a look at the admin side. I’m going to log out as the user and very quickly log in as the admin, in this example that happens to be John. And as soon as I log in, the data page that we’re going to build on the admin side is manage articles. So when you click on it, you have the ability to search. Once again, we can also search based on author, click search to see the results. And you can see that I’m looking at all the articles from the knowledge base. It doesn’t matter what the status is. I should be able to see all the articles.
I have the ability to delete, and I can go to details to modify this article, unpublish it, or change the status at any point. So back to Caspio, open the admin folder and build your new data page, reports, tabular format, hit next, data source needs to be the articles table. Let’s call this manage articles. And now we’re going to restrict access and use the admin level login. Before we were using the user level login, but now only the admin will have the ability to see this report and be able to delete articles. So let’s move on. Let’s have a search interface. No need for record level security because admin level user is the highest level, so they should be able to see everything from within the table.
For my search fields, I’ll include user ID, department, title, and let’s just have a couple of search fields. I’m not going to include the same ones as in my live example. You’re going to be able to see these fields and these reports when you download the app. For my user ID, it automatically creates a dropdown for me and lists the full name of the user, but I’d like to actually have both look up table and custom values and simply say, search any author, delete the value. For my department, this can also be a dropdown, both, search any, delete the value and under look up table, make sure you select your department’s table. And instead of ID, switch that for department’s field, the title can be contains and article itself can also be contains hit next. On the results page, you can include some fields. So perhaps you can have department, author, title and status, move on.
So here on this page, now you have the ability to enable some editing. And all I want to enable here is inline delete. Inline delete is going to create, let’s go back to results, is going to create this delete link. This is inline delete, so admin can delete any one of these articles at any point. Move on. My results page, I’m not going to make any changes here. Let’s show 25 articles per page. Let’s enable the details view, and in the details view, now you can include your user ID field and any other field that you wish to have admin be able to edit and status. And finally here, you can configure the details page fields. So for example, if I want to be able to reassign that article to a different user, I can change this from display only to a dropdown. And this is going to link back to my user table and display the name so that I can quickly reassign that to somebody else. Department, I can also turn this into a dropdown and very simply just do a lookup table on my department’s table.
Title can be a text field, article can be a text area. Attachment is going to be displayed as a file. And also file form elements so that we can replace the file with something else. Internal notes can be a text area. Date submitted is going to be display only. Date updated is also display only. And then finally we have status. Now as the admin, we should be able to choose any status that we want. So we have in progress, we have submitted for review, we have resubmit for review. Unpublished. Make sure in the value here you put unpublished because you want that to be stored inside a table as unpublished and finally published. But for display, we want to say published because if I want to publish an article, I’m going to say this, but as a value, it needs to be stored as published inside the table.
You can click on preview to see what you created. We have some search fields here that we can pull up based on the author. We can hit search to see the results. There should be only one article being pulled from the database. And now as the admin, I can delete that article or I can go to details to customize that article. To continue, just close the preview tab, hit finish to save your changes. And there’s your very first admin data page. If you don’t know how to create the rest of these data pages on your own, as I said a few times already, just download the app, go to tools, and import the application into your account. And then you’re going to be able to find all those data pages and be able to see how they were created. And eventually what you want to do is deploy that application to the web template that you can also download via email and to deploy the application to the web. Join me in the next video where I teach you how to deploy each one of these data pages into a website. Thanks for watching part four, and I hope to see you in the last part of this video guide.
Share this post: