Digital Success. One and Done.TM
Experience the best of everything Caspio has to offer.
Start Your Free Trial in 30 Seconds
Learn to Build Caspio Apps for FREE
Caspio Powers Over 1 Million Applications
We're ranked the strongest of all vendors based on product strengths.
SiteRocket Labs Transforms Healthcare Delivery
Builds HIPAA-compliant apps and slashes development time by over 60%.
Low-Code Fuels National Energy Company
J-W Power meets demand for custom apps despite limited IT resources.
Build Your Own CRM Without Coding or IT Skills
Learn how to create a custom CRM application in a matter of hours.
Low-code pioneer continues to democratize application development.
Build custom applications fast, without any coding or IT skills.
Part 4 will teach you how to build nine different applications made up of charts, forms and reports without writing a single line of code.
But first, you must be familiar with the Caspio DataPage.
DataPages are the application interfaces that you will, later on, be embedding into your website to access all the functionality.
To build your first DataPage, click on the DataPages object on the main left-hand panel. You’ll find in the resulting screen that no DataPages currently exist, so go ahead and click the New DataPage link on top to launch Caspio’s point-and-click DataPage wizard.
On the left-hand side of the wizard, you’ll see a navigation menu that features all the different apps you can build: Forms, Reports, Calendars, Charts and HTML DataPages if you’re technically adept.
DATAPAGE 1: My Sales
The first DataPage you’ll learn to build is a combination of a tabular report and a chart. It should serve as a valuable resource for sales reps to log in and view their own sales.
Wizard Step 1 – DataPage Type
Click on the Reports tab of the DataPage Wizard, then click on Combined Chart and Report. You’ll then see an illustrated preview of what you’re building at the bottom. It shows that you’ll have the ability to search data if you decide to build a search interface, view a chart along with your results on the Results Page, and drill into details if necessary.
Move on by clicking Next.
Wizard Step 2 – DataPage Data Source
Under DataPage Properties, you have four fields you can configure:
Select data source – Clicking this dropdown will show you a list of all five of your tables. Select demo_tbl_sales to pull all the sales information from your sales table.
Enter DataPage name – Since this DataPage will be built to show data limited to the rep who logged in, call this DataPage: My Sales.
Select Style – Apply different styles to change the aesthetics of your DataPage here. You can even create your own style when you go to the Styles object in Caspio’s main left-hand panel.
Select Localization – This dropdown will allow you to select different regional settings for your DataPage. You can change the language of your forms and reports, and apply different date formats as well. If you wish to configure your own localization, go to the Localizations object in Caspio’s main left-hand panel.
Under Access and Security, make the following modifications:
Wizard Step 3 – Search Type
Under Data Selection, you can build a search interface by clicking the first radio button to Allow users to select data using a search form. Since you don’t need your reps to have the ability to search, have them view data right away after logging in by selecting the second radio button to Filter data based on your pre-defined criteria.
Then under Record Level Security, make the following modifications:
Matching your Rep_ID fields correctly between your authentication data source (demo_tbl_reps) and DataPage data source (demo_tbl_sales) will enable you to implement record level security, meaning your sales reps can only view their own data upon logging in.
Wizard Step 4 – Select Filtering Fields
This wizard will ask you to filter data based on your Available Fields. Since we won’t be applying other filters on the results beyond record level security, bypass this screen by clicking Next.
Wizard Step 5 – Select Results Page Fields
Select the fields you want to use in the results page here. Transfer the following from your Available Fields to your Selected Fields by clicking the “>” button:
Wizard Step 6 – Results Page Editing Options
If you want to give your sales reps the ability to edit data on the results page, you can allow them to edit here. But to keep things simple, leave Add Records, Edit Records and Delete Records unchecked for now. Move on by clicking Next.
Wizard Step 7 – Configure Results Page Fields
Two modifications must be made under DataPage Elements:
Wizard Step 8 – Results Page Options
This screen allows you to configure Default sort order, Records per page and more. Skip this portion for now and click Next.
Wizard Step 9 – Select Chart Type
This wizard will ask you to choose the kind of chart that could best display your data. For this use case, select Stacked Column under Column Charts, then click Next to continue.
Wizard Step 10 – Configure Chart Options
Make the following modifications under Chart Elements:
Wizard Step 11 – Chart Page Options
There’s no need to make any changes in your chart options so go ahead and click Next.
Wizard Step 12 – Details Page
Since all the data you need is already in the results page, select the second radio button to have No Details Page.
You can also click the Preview button here to check the DataPage you’ve built. Upon doing so, you’ll be prompted to log in as a sales rep. Go ahead and use your sample account for Max (Email: firstname.lastname@example.org, Password: maxw) to view his sales information.
You should be able to observe the following for DATAPAGE 1:
After making sure that everything’s in order, close your preview page and click Finish. You’ve now created your very first DataPage. Note that you can edit it anytime if you need to make additional modifications.
Now that your reps can log in and view their own sales, the next DataPage you need to build should allow them to input their own sales information. Click on New DataPage to get started.
DATAPAGE 2: Add New Sale
The goal of this DataPage is to have an online web form for your reps to submit sales information to your sales table.
Click on Forms, select Submission Form, then click Next to continue.
Configure the following fields under DataPage Properties:
Select data source – Select demo_tbl_sales to input new sales data to your sales table.
Enter DataPage name – Call this DataPage: Add new Sale.
Wizard Step 3 – Select Fields
On this screen, instead of including one field at a time from your Available Fields to your Selected Fields, simply click the “>>” button to execute bulk transfer. You’ll be using all of them on your submission form.
Wizard Step 4 – Configure Fields
Before you continue, click Preview to see what your form looks like. Then apply these modifications on the following fields under DataPage Elements:
Product_Name – To turn this from a text field into a dropdown menu that displays all available products from your products table, follow the steps below:
Rep_ID – Since you wouldn’t want your reps to manually input their ID every time they submit the form, you’ll need to automatically stamp the ID from when they log in to your sales table. Follow the steps below to apply this function:
Sale_Location_State – Manually typing the state each time isn’t ideal as well, especially if you already have a lookup table. Simply follow the same procedure as Product_Name with the changes below:
Click Finish to save your changes. The resulting screen should show both DataPages you’ve created, which can be edited and previewed anytime.
Upon clicking the Preview link of Add New Sale, you should be able to test DATAPAGE 2 by submitting any sample data and checking if they are seen in DATAPAGE 1. Now, whenever a rep adds their sale, the data will also be reflected on your My Sales DataPage.
Duplicating an existing DataPage is simple. For this next one, find the My Sales DataPage and click More to open a dropdown menu, then click Duplicate. A new DataPage should appear on your list that is prompting you to give it a name. Type in Company Sales and click Save.
To begin with the modifications, click Edit.
DATAPAGE 3: Company Sales
The third DataPage you’ll build will be the last one for the sales reps. It should allow them to log in and view a report of all the sales brought in by the other reps. One way to build it is through the same steps you’ve followed in creating My Sales, but it would be quicker if you just duplicate and modify the DataPage.
Here are the only pages you’ll be configuring in the Combined Chart and Report wizard to complete your new DataPage:
Under Record Level Security, uncheck Restrict record access based on user identity to display all the sales data.
Include Rep_ID from your Available Fields to your Selected Fields. This will serve to display your sales reps on both the table and the chart.
Under DataPage Elements, select your Rep_ID field and bring it to the top by clicking the up-arrow symbol at the bottom of the panel. Then make the following modifications:
Under Display Options, configure the Records per page by changing it from 25 to 500.
Under Bar Charts, select Bar.
Wizard Step 10 – Configure Chart Options
Make the following modifications under Chart Elements:
Configure your Chart sort order to display total sale amount from highest to lowest. Change the selected field on the first dropdown into SUM(Sale_Amount) and click the box beside it to change the setting to Z→A.
Click Finish to save your work.
When you click Preview back in the main DataPages screen, you’ll find that your chart list is from highest to lowest, and that the sales in the tabular report are grouped by Rep_ID. But the IDs aren’t identified by name.
One thing you need to address in your last DataPage is determining the reps behind the Rep_IDs. This can easily be solved within Caspio by applying table relationships.
Simply follow the steps below:
Right-click Tables from the main left-hand panel, then open the object in a new tab.
Click Relationships at the top of the resulting screen.
In the list of Tables on the left, check the boxes for demo_tbl_reps and demo_tbl_sales.
Select your primary key (Rep_ID) from demo_tbl_reps, then drag the line to the field you are stamping the ID on (Rep_ID field of demo_tbl_sales).
In the resulting pop-up window, in the dropdown menu for Display Value, select Name.
Check the box underneath to Use display value on DataPages.
Caspio will identify the table relationship you’ve created as one-to-many. It means that a unique Rep_ID from your reps table can be linked to multiple sales on your sales table.
You can now click Save Layout on top and go back to your DataPages. Click Preview on your Company Sales DataPage and log in as Max (Email: email@example.com, Password: maxw). You’ll then notice that all the Rep_IDs are now replaced with their respective names.
One neat trick you can do when building multiple DataPages in Caspio is to organize them in folders. This section will quickly teach you how to sort your list of DataPages by user groups.
Within the DataPages object, click the New Folder link on top and name it Admin.
Next, click the New Folder link again to create your second folder and name it Reps.
Since all three DataPages currently on your list are all under the user group Reps, check the boxes of all three to their left.
Click the Bulk Actions link on top.
Expand Sales Management and select Reps folder.
Now you can simply expand the Reps folder to find all three related DataPages. You don’t have to deal with a long, unsorted list that will prove troublesome when editing functionalities.
There are six more DataPages you’ll need to build, this time for the Admin portal. Begin by clicking the Admin folder you’ve recently created, then click the New DataPage link on top.
DATAPAGE 4: Add New Rep
This DataPage will allow your admins to add a sales rep to your reps table. What you’ll need is another submission form.
Click the Forms tab and select Submission Form.
Transfer all Available Fields to your Selected Fields by clicking the “>>” symbol.
Apply these modifications:
Note that you can also enable some options here to have password strength, and minimum strength required. When you’re done, click Finish to save your DataPage. You should find it within your Admin folder on your main DataPages screen.
Now, try checking your new submission form by clicking Preview, then log in using your sample admin account (Email: firstname.lastname@example.org, Password: test). You should be able to see four required text fields (Name, Email, Password, Confirm Password) and a Submit button. As an admin, you can also create a password on behalf of the employee.
DATAPAGE 5: View/Manage Reps
The next DataPage you’ll build for your admins will allow them to manage every sales representative from your reps table. What you’ll need here is a tabular report. Make sure that your Admin folder is opened before clicking the New DataPage link on top.
Click the Reports tab and select Tabular.
Follow the same configuration in Wizard Step 2 of DATAPAGE 4, except type in View/Manage Reps when you enter the DataPage name.
Under Data Selection, build a search form by selecting the first radio button to Allow users to select data using a search form. You can then choose whether to Display search form and results on separate screens (default) or Display search form above the results.
Wizard Step 4 – Select Search Fields
Transfer Name and Email from your list of Available Fields to your Selected Fields. This way, you can search for reps inside your database based on those fields.
Wizard Step 5 – Configure Search Fields
Under General Options of both the Name and Email fields, set the Comparison Type to Contains to have a broader results range.
Wizard Step 6 – Select Results Page Fields
Again, include Name and Email from your list of Available Fields to your Selected Fields.
Wizard Step 7 – Results Page Editing Options
Under Edit Records, check the first box to implement Inline edit (one record at a time). This will allow your admins to edit a row directly from the results page.
Wizard Step 8 – Configure Results Page Fields
No changes required here so skip to the next page.
Wizard Step 9 – Results Page Options
Again, no changes required here.
Wizard Step 10 – Details Page
Select the first radio button that says Yes, enable Details Page.
Wizard Step 11 – Select Details Page Fields
Display all the information in the Available Fields by clicking the “>>” button. The Rep_ID, Name, Email and Password fields should be included in your list of Selected Fields.
Wizard Step 12 – Configure Details Page Fields
The goal here is to be able to update the Name, Email and Password of your sales reps:
When you’re done, click Finish.
You should be able to see the following when you Preview your DATAPAGE 5:
DATAPAGE 6: View/Manage Reps
The next DataPage should allow admins to view all the sales for each of the reps, and reassign any given sale to somebody else. Again, make sure your Admin folder is opened before clicking the New DataPage link.
Click the Reports tab and select Combined Chart and Report.
Under Access and Security, follow the same admin configuration with DATAPAGE 4.
Under Data Selection, build a search form by selecting the first radio button to Allow users to select data using a search form.
Include Product_Name, Rep_ID and Date_of_Sale from your Available Fields to your Selected Fields.
Make the following modifications on these fields under DataPage Elements:
Product_Name – Apply the modifications below under the Standard tab:
Under General Options, change the Form element into a Listbox.
Under Field Options, in the Source dropdown menu, select Both.
Under the Custom Values tab below, on the Display text field, type in Search all.
In the Value text field beside it, delete the mirrored input.
And under the Lookup Table tab, in the Table or view dropdown, select demo_tbl_products.
Lastly, click on the Advanced tab on top and apply the modifications below:
Rep_ID – This field should automatically be configured due to the table relationship you previously applied. But also implement these modifications in the Standard tab:
Under the Custom Values tab, on the Display text field, type in Search Any.
Date_of_Sale – To be able to search by date range, make the following modifications:
At the bottom of the DataPage Elements panel, click the insert symbol button while the Date_of_Sale field is selected.
Select New Criteria. This will add Criteria 1 and Criteria 2 underneath
Click the Elements tab of Date_of_Sale on top.
From the Logical operator between criteria radio buttons, select AND
Under DataPage Elements, click Criteria 1.
Under DataPage Elements, click Criteria 2.
Transfer the following from your Available Fields to your Selected Fields:
Check the following boxes under Edit Records and Delete Records so your admin can edit by row, edit in bulk and delete by row whenever necessary:
Inline edit (one record at a time)
Bulk edit (applies same updates across multiple records)
Inline delete (one record at a time)
Make the following modifications under DataPage Elements:
Rep_IDs – Apply the modifications below under the Standard tab:
Under General Options, check the box to Enable data grouping by lookup values.
For the Grouping display dropdown, select Collapsible group.
From the radio buttons underneath, select Collapsed by default.
Aggregate 1 – Make the following modifications similar to Wizard Step 7 in DATAPAGE 1:
Click the insert symbol at the bottom of the DataPage Elements panel.
Select Totals & Aggregation.
In the Standard tab, under Aggregate Options, select Sale_Amount from your list of Available Fields.
Click the ‘>‘ button to include it in your Selected Fields.
Make sure that Function is selected in the radio button underneath.
Make sure that Sum is selected in the dropdown beside it.
Wizard Step 9 – Configure Fields for Bulk Edit
For this use case, you only need the ability to assign a sale to a different rep. Apply this by keeping the Rep_ID field and removing the rest: Click ‘X‘ at the bottom of the panel, then Yes in the confirmation box for each highlighted field you’ll be removing.
Wizard Step 10 – Results Page Options
Under Display Options, increase the Records per page to 500.
Wizard Step 11 – Select Chart Type
Under Bar Charts, select Stacked bar.
Wizard Step 12 – Configure Chart Options
Chart Settings – Under General Options, type in Total Sales as the Title and By Rep as the Subtitle.
Data Source Settings – Select the first radio button to Create series from data in a single field.
Category (Y Axis) – Make the following modifications:
Values (X Axis) – Make the following modifications:
Value – In the dropdown for Value field, select Sale_Amount.
Wizard Step 13 – Chart Page Options
Wizard Step 14 – Details Page
Select the second radio button to have No Details Page. Then click Finish.
You should be able to see the following when you Preview your DATAPAGE 6:
A list box where more than one product can be selected
Hold Ctrl to click on multiple items.
A dropdown that pulls all your sales reps
The ability to conduct a date range search
The ability to show and hide elements from your chart
Click the product items on the right-hand side.
A collapsible tabular report that is grouped by sales reps
The ability to reassign sales to a different rep, and have it reflected on the chart
The last three DataPages you’ll learn to build is comprised of two charts and a pivot table. All three will be deployed on one web page to form an administrator dashboard. The goal here is to quickly show aggregate data across all your sales in one place.
DATAPAGE 7: Total Sales by Month
The goal of this DataPage is to show the admin their aggregate sales trend per month through a line chart. Make sure your Admin folder is opened before clicking the New DataPage link on top.
Under the Charts tab, in Lines and Step Charts, select Line.
Follow the same configuration of Wizard Step 2 in DATAPAGE 6, except type in Total Sales by Month when you enter the DataPage name.
Under Data Selection, select the second radio button to Filter data based on your pre-defined criteria.
Wizard Step 4 – Search Filtering Fields
You don’t need to implement any filtering so skip this page by clicking Next.
Wizard Step 5 – Configure Chart Options
Chart Settings – Under General Options, type in Total Sales as the Title and by Month as the Subtitle.
Data Source Settings – Select the second radio button to Create series from data in a single field.
Category (X Axis) – Make the following modifications:
Value (Y Axis) – Make the following modifications.
Value – Make the following modifications:
When you’re done, click Finish to save your DataPage chart.
You should be able to view a simple trend of all your sales when you Preview your DATAPAGE 7.
DATAPAGE 8: Top Performers
The goal of this DataPage is to show the admin a bar chart of his reps by order of performance. Make sure your Admin folder is opened before clicking the New DataPage link on top.
Under the Charts tab, in Bar Charts, select Bar.
Follow the same configuration of Wizard Step 2 in DATAPAGE 6, except type in Top Performers when you enter the DataPage name.
Chart Settings – Under General Options, type in Top Performers as the Title.
Under General Options, type in Name as the Axis title.
In the dropdown for Category field, select Rep_ID.
Value (X Axis) – Make the following modifications:
Under General Options, type in Total Sales as the Axis title.
Under Display Options, in Formatting, click Edit.
In the resulting popup window, select the radio button for Custom.
In the dropdown for Format type, select Currency.
Wizard Step 6 – Chart Page Options
In the Chart sort order dropdown, select SUM(Sale_Amount) and click the box beside it to sort by Z→A.
You should be able to view a simple bar chart of your top performers from highest to lowest when you Preview your DATAPAGE 8.
DATAPAGE 9: Sales Pivot
Pivot tables will allow you to see fields at the top and on the left-hand side, with aggregates of your data in the middle. This functionality can easily be built with Caspio as well. Make sure your Admin folder is opened before clicking the New DataPage link on top.
Click the Reports tab and select Pivot Table.
Follow the same configuration of Wizard Step 2 in DATAPAGE 6, except type in Sales Pivot when you enter the DataPage name.
Wizard Step 5 – Configure Pivot Table Fields
Make the following modifications under DataPage Elements:
Columns – Make the following modifications:
Rows – Click the “+” symbol beside this field and select Rep_ID.
Values – Make the following modifications:
When you’re done, click Finish to save your DataPage.
When you Preview your DATAPAGE 9, you should be able to see a pivot table that shows how much revenue is being brought in per month, and who among the reps are contributing the most.
What’s left now is deployment.
In the final video of the series, you’ll learn how to deploy all your DataPages into a website where your users will have access to the entire functionality. Both the reps and the admins should be able to log in, view and edit information, and be able to submit new sales into the app.
Share this post:
Caspio is the world’s leading cloud platform for building online database applications without coding. We are ranked a Leader in Low-Code Platforms by Forrester Research.
© 2021 Caspio, Inc. Sunnyvale, California. All rights reserved.