Convert Excel to Web Apps

Let’s quickly review the sample Excel file you’ll be importing inside Caspio.

The Sales.xlsx file consists of 5 different spreadsheets:

1. 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 called the primary key (Sale_ID).

   

   SALES DATA

2. demo_tbl_products – This table is used as a lookup table and contains a list of products. 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.

3. demo_tbl_states – Same as above, only this table contains your list of states.

4. 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.

   

   REPS DATA

Now, let’s go inside Caspio and learn how to import this Excel file.

Once you’re logged inside Caspio, click the New App link on top. You’ll then be given two ways to begin:

1. By importing your existing data
2. With a blank app

Since you’ll be creating an app from an existing Excel file, click Start by importing data. Next, you’ll be asked to name your app. Input Sales Management for this exercise, although you’re free to use your own naming convention.

Next, upload the sample Excel workbook called Sales.xlsx and click Next.

On following 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 spreadsheets, 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. Click Next to proceed.

This following screen will give you the ability to toggle back and forth between your Tables.

And under Configuration Options, you can:

• View the original field names within your selected table: Field Name (Source)
• Edit the names into something else in: Field name (Destination)
• Include or exclude fields to be imported into Caspio: Include

In the next section, you’ll learn how to associate the correct data type with each of your fields.

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 data type from Number to Autonumber. This will automatically assign a new ID that is numerically incrementing for each admin user.

  • Change the Password data type from Text (255) to Password. This will encrypt the password column for anyone who opens your table.

• 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.

  • Change the Password data type from Text (255) to Password.

• Table 4: demo_tbl_sales (New)

  • Change the Sale_ID data type from Text (255) to Autonumber. This will serve as the main table keeping track of all your sales.

  • Change the Rep_ID data type from Number to Integer. That’s because you’ve previously configured each new rep to have Autonumber values. And to correctly stamp the unique IDs from demo_tbl_reps (which are whole numbers), the data type must be Integer. This is how you’ll recognize the sales reps on this table.

  • Change the Sale_Amount data type from Number to Currency,

  • Change 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, you are not in accordance with best practices.

However, you don’t need to use Autonumber every time. You can also implement the following ID types:

• GUID – A long, secure and randomly generated ID.
• Prefixed ID – Where the first characters are custom set to go with incrementing numbers.
• Composite Key – An ID that concatenates multiple fields into one using a formula field (remember to flag it as unique).

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 to be used in different departments. 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. This will take you back inside its app framework. 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. You can even add notes as you begin to develop your application.

But 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, we mentioned that changes can still be made 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.

  • Hover your cursor on demo_tbl_admin and click on Design.

  • Find the Email field and check the box under the column Unique. We do this because no two users should have the same email address. The email field will also be used to log into the application later on.

  • When you’re done, Save the admin table to keep your changes.

• Revision 2: demo_tbl_reps

• • Follow the same procedure as Revision 1.