Welcome to our ultimate video guide on turning spreadsheets into apps. This video series will cover everything you need to learn to create web applications using your Excel files, from importing the file online to deploying dynamic forms and reports to your website.
Expect to have an Excel-based application you can showcase to your co-workers and friends at the end of the entire series. But for now, let’s learn how to import an Excel file into Caspio to begin developing the application.
Before you start uploading Excel files to the Caspio Platform, try to review the sample spreadsheet you’ll be using.
The Sales.xlsx file consists of 5 tables:
demo_tbl_sales – This table contains Product Name, Representative ID, City, State, Sale Amount and Sale Date. Each sale entry contains its own unique identifier, the primary key: Sale_ID.
demo_tbl_products – This table is used as a lookup table and contains a list of products and states. Imagine a drop-down field in a web submission form where you can select the product — that’s exactly what this lookup table is going to serve.
demo_tbl_states – Same as above.
demo_tbl_reps – This table consists of the Name, Email and Password fields, with Rep_ID as the primary key. Notice that Rep_ID is also used in the demo_tbl_sales table. A primary key that uniquely identifies a row of another table is called a foreign key, and is used to connect data between tables.
In other words, Rep_ID is used to create a relationship between reps and sales tables.
demo_tbl_admin – And finally, we have a table that contains the list of all the administrators. There is only one admin listed there, but you can have multiple administrators. In the app we are building, admin has the highest level permissions, which allow them to edit products and sales information, reassign a sale to a different rep, etc.
Now, log in to your Caspio trial account and learn how to import data from an Excel file.
There are two ways to create an app with Caspio:
In this exercise, you’ll create an app from an existing Excel file. To do so, log in to your account, click New App, then click Start by importing data. Next, you’ll be asked to select an app name (input Sales Management) and upload a file (other supported files extensions are Access and ZIP).
Once you’ve uploaded the sample Excel workbook called Sales.xlsx, click Next.
On the next screen, you’re going to see a list of all the tables that you’re importing from Excel to web. You can also pick and choose which table you want to bring in through checkboxes in the column Source Object. In this case, bring in all five tables.
The second column, Action, allows you to specify how you want to bring this data into Caspio. Since new database tables will be created from the worksheets, Create New should be selected. Other options include Replace, Append, Update and Update Design.
You can also change the table names to something else in the third column, Destination Object. Use the default names in this tutorial for now. After making sure that everything is in order, click Next.
This screen will give you the ability to toggle back and forth between your in-transition worksheets under the column Tables.
And under Configuration Options, you can:
In the next section, you’ll learn how to associate the correct data type with the fields you’ll be importing.
The modifications you’ll be making next are essential to keep the integrity of the app. Note that these changes can also be done later after you import your data.
Table 1: demo_tbl_admin (New)
Change the User_ID field from Number to Autonumber. This will automatically assign a new ID that is numerically incrementing for each admin user. Then change the Password data type from Text (255) to Password. Now if anyone opens your table, they will find that the password column is encrypted.
Table 2: demo_tbl_products (New)
Change the Product_ID data type from Number to Autonumber. This, in turn, will assign a new ID with every new product.
Table 3: demo_tbl_reps (New)
Change the Rep_ID data type from Number to Autonumber; and change the Password data type from Text (255) to Password.
Table 4: demo_tbl_sales (New)
For this table, which serves as the main table responsible for keeping track of all the sales, change the Sale_ID data type from Text (255) to Autonumber as well.
But with the Rep_ID field, change the data type from Number to Integer. That’s because you’ve previously configured each new rep to have Autonumber values in its parent table, demo_tbl_reps. And to correctly stamp its primary key (which are whole numbers) on the Rep_ID field here, the data type must be Integer. The Number data type, on the other hand, should only be used when dealing with decimal numbers.
For the last two columns — change the Sale_Amount data type from Number to Currency, and the Date_of_Sale data type from Text (255) to Date/Time.
Table 5: demo_tbl_states (New)
Change the State_ID data type from Number to Autonumber.
You may have noticed a similar pattern when you look at all your tables — each of them has an Autonumber. That’s because each table in a database environment should always have a unique, automatically generated number called a primary key. If you have a table and you’re not automatically generating a number, then you’re not in accordance with best practices.
However, you don’t have to use Autonumber every single time. You can also implement the following:
Once you’re done, go ahead and click Import.
As soon as you hit the Close button, Caspio immediately takes you inside the framework of your Excel to web application.
Before going over your application framework, click the Home link on Caspio’s top menu bar. You should see a list of all your apps.
Caspio enables you to build multiple applications fast, and these apps can be used in different departments. You can even become a reseller if you want. In short, there are a lot of ways to leverage the Caspio platform.
Now, hover your cursor on the app container labeled Sales Management, and click Open to go back inside its application framework. The objects on the left-hand side, namely Overview, DataPages, Tables, Views, Authentications, Connections, Styles, Localizations and Tasks, include some of the tabs you will use to build your Excel web app.
You’ll notice that the first object highlighted by default is the Overview tab. This screen is mostly informational to help you keep track of your progress.
Here are the seven metric boxes you should take note of:
Aside from that, you also have the option to add Notes on the lower right side as you develop your application.
The most important place where you need to begin is always the tables object. Go ahead and click the Tables tab on the left.
Tables are the foundation of any app you develop inside Caspio. This is where all your data is going to reside.
After making sure that your tables from Excel are already imported, hover your cursor on demo_tbl_sales and click Open. You’ll see that all the sales data were also transferred. They are now sitting inside an online database.
Once you have the table opened, you can click on each cell to make modifications. But to edit your fields, you need to click on Table Design.
If you’ll recall back in the import screen, it was mentioned that changes can still be made even after importing your data. That’s exactly what you can do under table design. Here, you can change data types, rename fields or introduce new ones to your tables.
There are two more changes you need to make that the import screen doesn’t allow you to do:
Revision 1: demo_tbl_admin
Go back out to the tables menu by clicking the Tables tab, then hover your cursor on demo_tbl_admin and click on Design, then find the Email field and tick the checkbox under the column Unique. When you’re done, Save the admin table to keep your changes.
Given that no two users will have the same email address, the Email field should be unique to function as your login username (more on that in Part 3 of the video series).
Revision 2: demo_tbl_reps
You’ll also need to do the same to your rep table. Click the Tables tab to go back to the table menu, then hover your cursor over demo_tbl_reps and click Design, then find the Email field and tick the checkbox under the column Unique. When you’re done, Save the table.
This concludes Part 2 of our ultimate video series that teaches you to make a web app from excel data.
The tutorial covered how you can make modifications to your data types, while going through the process of importing an Excel file into Caspio.
Be sure to watch the next video on how to build a login screen for both the admins and the reps.