Summarized Video Transcript

Part 2 will teach you how to develop database tables and create a relationship between them.

Once you set up a 14-day free trial and log inside your Caspio account, it’s really easy to begin building an application.

We encourage you to follow along with the video and see if you can recreate this app. You’re going to learn how to use the platform with its point-and-click interface to build out different functionalities and workflows.

Starting from a Blank Template

To get started, all you have to do is click the New App link on top.

You’ll then have two ways on how to begin:

  1. By importing data from an external source
  2. With a blank template

Click the second option to Start with a blank app, then input your App name by typing in Simple CRM Application Demo before clicking Finish. A new container for this app will now be listed on your Home page. Note that you can’t have the same application listed twice with the same name.

Next step is to Open the application.

Notice the different objects on the left-hand side of the resulting screen, you’re going to use some of them to create this custom CRM app. Now, the very first object that’s highlighted by default is the Overview tab.

This screen is mostly informational for you to add notes and keep track of progress. Once you’re done exploring this screen, click the Tables object on the left-hand panel.

Creating Your Database Tables

The Tables tab is the most important place you need to begin when starting from a blank app. Aside from containing data, these tables are the foundation of any application you build inside your account.

This simple CRM app is only going to have three tables:

TABLE 1: SCRM_tbl_staff

Click the New Table link on top to start building your first table, which will hold staff information. Next, add the following fields under Name with their corresponding DataType:

  1. Staff_ID – Select Random_ID as the DataType. Doing so will flag the field as Unique so no two staff members will have the same identification. This is what’s called a primary key.
  2. Date_Added – Select Timestamp as the DataType. This will allow you to know the date and time an employee was added to the staff table.
  3. Name – Select Text (255) as the DataType.
  4. Email – Select Text (255) as the DataType and flag the field as Unique. That’s because you can’t have two employees with the same email.
  5. Password – Select Password as the DataType. This will encrypt the information saved on this field.
  6. Role – Select Text (255) as the DataType. This field will play a critical role in identifying admins from your managers, employees, supervisors and more depending on how many roles you’d like to have on your app.
  7. Status – Select Yes/No as the DataType. This field will be used by the manager to flag the active and inactive employees using a simple checkbox.

Once you’re done inputting all your fields, go ahead and hit Save. Then name this table SCRM_tbl_staff — SCRM being an abbreviation for a Simple Customer Relationship Management. Note that it’s best to include an abbreviation of your app name when naming your tables. It will be much easier to find a table that way when it (or the entire app) is exported.

Click Finish once you’re done, and you’ll see your very first table inside your account.

TABLE 2: SCRM_stage_lookup

Click the New Table link on top to build your second table. This is going to be a simple lookup table with only two fields:

  1. Stage_ID – Select Random_ID as the DataType. This will also serve as your primary key.
  2. Stage – Select Text (255) as the DataType.

When you’re done; click Save, name this table SCRM_stage_lookup, and click Finish.

But before you continue with the third table, Open this table’s container and quickly add some sample data:

  • Input Prospect (10%) for your first stage
  • Input Forecast (50%) for your second stage
  • Input Forecast (80%) for your third stage
  • Input Win/Closed (100%) for your last stage

While businesses might have different stages depending on their sales funnel and workflow, these are the ones that you’ll use for this application.

TABLE 3: SCRM_tbl_contact_info

The last table you’ll be building is going to have 20 fields in total. The information you’ll be saving here will be from your leads or contacts. Click the New Table link on top to start filling in the following fields:

  1. Contact_ID – Select Random_ID as the DataType to have it become the primary key.
  2. First_Name – Select Text (255) as the DataType.
  3. Last_Name – Select Text (255) as the DataType.
  4. Title – Select Text (255) as the DataType.
  5. Phone – Select Text (255) as the DataType.
  6. Email – Select Text (255) as the DataType.
  7. Contact_Method – Select Text (255) as the DataType.
  8. Company – Select Text (255) as the DataType.
  9. Address – Select Text (255) as the DataType.
  10. Address2 – Select Text (255) as the DataType.
  11. City – Select Text (255) as the DataType.
  12. State – Select Text (255) as the DataType.
  13. ZIP_code – Select Text (255) as the DataType.
  14. Website_URL – Select Text (255) as the DataType.
  15. Deal_Size – Select Currency as the DataType.
  16. Stage – Select Text (255) as the DataType.
  17. Follow_up_date – Select Date/Time as the DataType.
  18. Notes – Select Text (64000) as the DataType.
  19. Date_Added – Select Timestamp as the DataType.
  20. Assigned_to_by_Staff_ID – Select Text (255) as the DataType.

What you’re trying to accomplish with the last field, which is Assigned_to_by_Staff_ID, is to link each contact to the respective staff member. The next section will go over how to create table relationships to achieve that outcome.

When you’re done; click Save, name this table SCRM_tbl_contact_info, and click Finish.

Implementing Table Relationships

Now that you’ve built the primary foundation for all the forms and reports you’re going to create, the next thing you should do is to quickly establish a relationship between the staff table and the contact table.

In the screen showing you your list of Tables, click the link on top that says Relationships. You’ll find all three tables under the In App section of the Tables panel.

Check the boxes for SCRM_tbl_contact_info and SCRM_tbl_staff to include them in the layout display.

Next, click on the Staff_ID field inside SCRM_tbl_staff, and drag the pointer over to the Assigned_to_by_Staff_ID field inside SCRM_tbl_contact_info. A new window will then pop up showing you different configuration options.

Notice that the Relationship Type is recognized by the platform as One-to-Many. In the context of your table design, it means that a staff member can have more than one lead or contact.

Make the following changes on the same screen:

  • Display Value – Select Name in the dropdown menu. This will allow you to display the actual name of a staff member instead of their ID when you build reports.
  • Use display value on DataPages – Go ahead and check this box. It will enable the Display Value you’ve selected on your app interface.

Click Create once you’re done.

Now your staff and contact tables are linked in such a way that one staff member can have many contacts. You’re essentially stamping the Staff_ID into your contacts table.

See You in the Next Video!

Join us in the next video where we teach you how to build login screens and how to assign different privileges.

Next Steps

COMPARE LOW-CODE VENDORS
NEW FORRESTER WAVE REPORT
See why Forrester ranks Caspio a 'Leader' in low-code platforms in this 2019 report.
GET HELP FROM A CONSULTANT
TALK TO AN EXPERT
Have a vision for an application? Talk to a Caspio product expert and we’ll help you achieve it.
SEE CASPIO IN ACTION
SEE CASPIO IN ACTION
Want to see if Caspio is a good fit for your needs? Choose a date and time for a personalized demo.