How to Build Web Forms (Part 3): Relational Forms | Caspio
PLAY PREVIOUS PLAY NEXT

Summarized Video Transcript

Part 3 will teach you how to build a relational form, with a one-to-many relationship, to set up a full-on database application. Here’s a demo of the form.

Screenshot of a sample “New Service Request” submission form made on Caspio. It shows fields that users will fill out to submit their requests.

After inputting some sample data pertaining to a service request and hitting Submit, a Request ID will be passed to the next page.

Screenshot of a sample page made on Caspio. It shows details of the users requests under “Request Details” and a menu to assign the request under “Assign Tasks”.

That same Request ID will be used when assigning tasks. For example:

  • Assign this request to John Doe by selecting his name on the dropdown list.
  • Add “fix blinds” in the Comments field.
  • Input any future date in the Resolve by field.
  • Click the Add button to create the task.

It’s also possible to add multiple tasks and submit them under the same request. In this case: we can assign a new task to Raj Singh, input “order blinds” in Comments, input another date in Resolve by, then click Add. We can always go back to page one if we want to submit a new request.

This is a simple one-to-many relationship where we can track and see all the tasks pertaining to a single request. In this video, we’re going to show you how to create that relationship between the two tables.

Creating Your Database Tables

If you’ve seen the first two videos, you already understand the process of building an application in Caspio — first, log into your account and click the New App link on top.  You’ll then be given two ways on how to begin:

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

Click the second option to build your application from scratch.

You’ll then be prompted to name your app — input Relational Form and click Finish to see your app listed on your Home screen.

Next, click the Open link of your new app to access its App Overview. You’ll find all the necessary objects to create an app on the left-hand panel.

Screenshot of Caspio’s app builder. It shows the “App Overview” section.

The most important thing you should build first is your database table.

Tables are the foundation or backbone of any app you develop. For this use case, you’ll be creating four:

  1. A table that will store every request.
  2. A table that will store all the tasks.
  3. A lookup table for categories.
  4. A lookup table for locations.

Begin by clicking the Tables object on the left-hand panel.

TABLE 1: uvg_requests

Click the New Table link on top and list the following fields with their corresponding DataType:

  1. Request_ID – Select Random_ID as the DataType.
  2. Category – Select Text (255) as the DataType.
  3. Location – Select Text (255) as the DataType.
  4. Full_Desc – Select Text (64000) as the DataType.
  5. Access_Instructions – Select Text (255) as the DataType.
  6. Permission_to_Enter – Select Text (255) as the DataType.
  7. Attachment – Select File as the DataType.
  8. Request Status – Select Text (255) as the DataType.
  9. Date_Submitted – Select Timestamp as the DataType.
  10. Date_Updated – Select Timestamp as the DataType. Then under Options to the right, enable Stamp On Update and disable Stamp On Insert.

Take note of the following with regards to the data types used:

  • Use Random_ID, or other ID data types, to uniquely identify each record inside the table.
  • Use Text (255) when storing a maximum of 255 characters in a field.
  • Use Text (64000) when you need users to input up to 64,000 characters.
  • Use File when you need to attach an image, PDF, word document or any other file type.
  • Use Timestamp to automatically record the date and time of form submission.
Screenshot of Caspio’s app builder. It shows the “Tables” section and is opened at the “Tables Design” tab.

Click Save on top and name your table uvg_requests, then click Finish.

TABLE 2: uvg_tasks

Click the New Table link on top and list the following fields with their corresponding DataType:

  1. Task_ID – Select Random_ID as the DataType.
  2. Request_ID – Select Text (255) as the DataType.
  3. Assign_to – Select Text (255) as the DataType.
  4. Comments – Select Text (64000) as the DataType.
  5. Resolve_by – Select Date/Time as the DataType.

Note that the Request_ID field in this table functions as a foreign key. Associating it with the primary request ID field from TABLE 1 will establish a relationship.

Screenshot of Caspio’s app builder. It shows the “Tables” section and is opened at the “Tables Design” tab. The cursor hovers over the “Save” button.

Click Save on top and name your table uvg_tasks, then click Finish.

TABLE 3: uvg_categories_lookup

Your last two tables will function as data sources for dropdowns on the submission form. Follow the steps below to build your first lookup table:

  1. Click the New Table link on top.
  2. In the Name field, input Category.
  3. For its DataType, select Text (255).
  4. Click Save on top.
  5. Name your table uvg_categories_lookup.
  6. Click Open on the new table container.
  7. List the following values under Category to serve as dropdown menu options:
    • Blinds
    • Electrical
    • Plumbing
    • Parking
    • Pest Control
Screenshot of Caspio’s app builder. It shows the “Tables” section and is opened at the “Datasheet” tab.

TABLE 4: uvg_locations_lookup

Go back out to the Tables menu and build your last lookup table. Follow the steps below:

  1. Click the New Table link on top.
  2. For the Name field, input Location.
  3. For the DataType, select Text (255).
  4. Click Save on top and name your table uvg_locations_lookup.
  5. Click Open on the new table container.
  6. List the following values under Category to serve as dropdown menu options:
    • Living Room
    • Master Bedroom
    • Bathroom
    • Laundry Room
    • Balcony
Screenshot of Caspio’s app builder. It shows the “Tables” section and is opened at the “Datasheet” tab. The sample item “Balcony” is selected in the list.

Go back out to the Tables menu to find all your tables listed.

Implementing Table Relationships

Caspio also allows access to your table relationships. Click the Relationship link on top to make changes.

Screenshot of Caspio’s app builder. It shows the “Tables” section and is opened at the “Relationships” tab.

Follow the steps below to implement a one-to-many relationship between TABLE 1 and TABLE 2:

  1. In the list of Tables on the left, check the boxes for uvg_requests and uvg_tasks.
  2. Select your primary key from the requests table (Request_ID field of uvg_requests), then drag the line to the field you are stamping the ID on (Request ID field of uvg_tasks).
  3. In the resulting pop-up screen, Caspio will identify the table relationship you’ve created as one-to-many. No additional changes are needed here so click Create.

A single request can now be linked to many tasks. This, in a database environment, is referred to as a one-to-many relationship, where one item from the parent table can be linked to many items in the child table.

Building Your Request Form

Go to DataPages on the left-hand panel and click the New DataPage link on top. This will open Caspio’s point-and-click DataPage wizard to help you build your app interfaces.

DATAPAGE 1: Patient Intake Form Part 1

  • Wizard Step 1 – DataPage Type

    Screenshot of the “DataPage Wizard – DataPage Type” menu. It shows the “Forms” tab.

    Make sure that Forms is selected, then choose Submission Form on the right-hand side.

    Click Next to continue.

  • Wizard Step 2 – DataPage Data Source

    Screenshot of the “Web Form Wizard – DataPage Data Source” menu.

    Under DataPage Properties, configure the following fields:

    1. Select data source – Make sure that uvg_requests is selected.
    2. Enter DataPage name – Input Request Form for this tutorial.
    3. Select Style – Use any of the pre-loaded styles that comes with your account.
    4. Select Localization – Use English (default) for now.

    Click Next to continue.

  • Wizard Step 3 – Select Fields

    Screenshot of the “Web Form Wizard – Select Fields” menu.

    Apply the following changes:

    1. Transfer all Available Fields to your Selected Fields by clicking the “>>” button.
    2. Enable On exit, pass ID and formula fields as parameters.
    Whenever you submit this form, you’ll be passing a request ID to the subsequent page. Additional interfaces will then receive that ID and stamp it on your tasks table.

    Click Next to continue.

  • Wizard Step 4 – Configure Fields

    Screenshot of the “Web Form Wizard – Configure Fields” menu. Under the “Elements” section, it is opened at the “Standard” tab.

    Make the following changes under DataPage Elements:

    1. Category – Go to the Standard tab and apply the following changes:
      • Under General Options, in the Form Element dropdown, select Dropdown.
      • Under Field Options, in the Source dropdown, select Both.
      • Under the Custom Values tab, input Select Category on the Display field.
      • In Value field, delete the mirrored input.
      • Under the Lookup Table tab, in the Table or view dropdown, select uvg_categories_lookup.
      • In the Field for display dropdown, make sure that Category is selected.
      • In the Field for value dropdown, make sure that Category is selected.

      Go to the Advanced tab:

      • Enable Continue next element on the same line.
      • In the Label Position dropdown, select Top.
    2. Location – Go to the Standard tab and apply the following changes:
      • Under General Options, in the Form Element dropdown, select Dropdown.
      • Under Field Options, in the Source dropdown, select Both.
      • Under the Custom Values tab, in the Display field, input Select Location.
      • In the Value field, delete the mirrored input.
      • Under the Lookup Table tab, in the dropdown for Table or view, select uvg_locations_lookup.
      • In the Field for display dropdown, make sure that Location is selected.
      • In the Field for value dropdown, make sure that Location is selected.

      Go to the Advanced tab:

      • In the Label Position dropdown, select Top.
    3. Full_Desc – Apply the following changes in the Standard tab:
      • Under General Options, in the Form Element dropdown, select Text Area.
      • Under the Advanced tab, in the Label Position dropdown, select Top.
    4. Access_Instructions – Follow the same changes above.
    5. Permission_to_Enter – Go to the Standard tab and apply the following changes:
      • Under General Options, in the Form Element dropdown, select Dropdown.
      • Make sure that the dropdown for Source has Custom Values selected.
      • Under the Custom Values tab, in the Display field, input Yes.
      • Click the New button to the right.
      • In the Display field, input No.

      Go to the Advanced tab:

      • Enable Continue next element on the same line.
      • In the Label Position dropdown, select Top.
    6. Attachment – You have the option to change the Maximum allowed size of attachments to 50mb, but the default 15mb will suffice. But go to the Advanced tab and apply the following change:
      • In the Label Position dropdown, select Top.
    7. Request_Status – Go to the Standard tab and apply the following changes:
      • Under General Options, in the Form Element dropdown, select Hidden.
      • Under Field Options, in the On load, receive dropdown, make sure Default Value is selected.
      • Input New in the text box next to it.
    Request_Status is automatically flagged as new each time the form is submitted. But as an admin user, you could create a dropdown that will change the status into “In progress” or “Completed”.

    Follow the steps below to add a simple heading:

    1. Click the insert button at the bottom of the DataPage Elements panel.
    2. Select Header & Footer.
    3. Click Header on the panel.
    4. Type in New Service Request on the window to the right.
    5. Highlight the text and select Heading 2 in the Format dropdown above.

    Click Next to continue.

  • Wizard Step 5 – Destination and Messaging

    Screenshot of the “Web Form Wizard – Destination and Messaging” menu. It shows the “Destination Options” tab.

    Make the following changes under Destination Options:

    1. In the destination after record submit dropdown, select Go to a new page.
    2. For the Page URL, input the name of the webpage you want to link to (form_3_request_details.html for this tutorial).
    3. Enable Relative to maintain the domain name.
    Instead of seeing a confirmation message in the next screen, you’ll now be redirected to a new page.

    Click Finish to save your work.

Deploying Your Request Form

Screenshot of Caspio’s app builder. It shows the “DataPages” section. There is one entry titled “Request Form”.

DEPLOYMENT 1: form_3.html

Follow these steps to deploy using the Embed method:

  1. Click the Deploy link on Request Form’s DataPage container.
  2. On the resulting pop-up window, click Enabled to change Deployment Status.
  3. Make sure that the deployment method selected is Embed.
  4. Copy the Caspio generated deploy code.
    Screenshot of the “Deploying 1 DataPage” menu.
  5. Paste the code on your designated HTML file (form_3.html).
  6. Save your HTML file.
  7. Refresh the page on your website. Screenshot of a sample completed “New Service Request” form made on Caspio.

The expected behavior here is upon submission, you’ll be redirected to a different URL in your directory.

Building and Deploying a Details Report

Go back to the Caspio platform and click the New DataPage link on top.

DATAPAGE 2: Request Details

This DataPage will allow you to see the details of your submitted request from the previous screen.

  • Wizard Step 1 – DataPage Type

    Select Reports then choose Details on the right-hand side.

    Click Next to continue.

  • Wizard Step 2 – DataPage Data Source

    When you Enter DataPage name, input Request Details. Keep everything else the same as before.

    Click Next to continue.

  • Wizard Step 3 – Search Type

    Screenshot of the “Search and Report Wizard – Search Type” menu.

    Make the following changes:

    1. Under Data Selection, select Filter data based on your pre-defined criteria.
    2. Enable Allow parameters in search criteria.
    3. Select Bridge and external parameters.

    Click Next to continue.

  • Wizard Step 4 – Select Filtering Fields

    Screenshot of the “Search and Report Wizard – Select Filtering Fields” menu.

    Select Request_ID from your list of Available Fields, then click the “>” button to include it to your Selected Fields.

    Click Next to continue.

  • Wizard Step 5 – Configure Filtering Fields

    Screenshot of the “Search and Report Wizard – Configure Filtering Fields” menu.

    Go to the Advanced tab and make the following changes in Request_ID:

    1. Enable Receive value or parameter on page load.
    2. In the dropdown menu underneath, select External Parameters.
    3. In the text box next to it, input [@InsertRecordRequest_ID].
    4. Select the first radio underneath that says Value required.

    Click Next to continue.

  • Wizard Step 6 – Select Details Page Fields

    Include all Available Fields to your Selected Fields by clicking the “>>” button.

    Click Next to continue.

  • Wizard Step 7 – Configure Details Page

    No need to modify your details page and make all the fields editable.

    Click Finish to save your work and proceed to deployment.

Screenshot of Caspio’s app builder. It shows the “DataPages” section. There are two entries titled “Request Details” and “Request Form”.

DEPLOYMENT 2: form_3_request_details.html

Follow the embed process outlined in DEPLOYMENT 1 but paste your deploy code in its designated section on the second web page (form_3_request_details.html).

Screenshot of a sample completed form made on Caspio titled “Form 3: One to Many Relation”.

Building and Deploying a Tabular Report

Go back to the Caspio platform and click the New DataPage link on top.

DATAPAGE 3: Request Details

The last DataPage you’ll build is a report to assign tasks pertaining to a specific request.

  • Wizard Step 1 – DataPage Type

    Select Reports then choose Tabular on the right-hand side.

    Click Next to continue.

  • Wizard Step 2 – DataPage Data Source

    Make the following changes:

    1. In the Select data source dropdown, choose uvg_tasks.
    2. In the text box to Enter DataPage name, input Assign Tasks.

    Click Next to continue.

  • Wizard Step 3 – Search Type

    Follow the same changes in DATAPAGE 2, Wizard Step 3.

  • Wizard Step 4 – Select Filtering Fields

    Follow the same changes in DATAPAGE 2, Wizard Step 4.

  • Wizard Step 5 – Configure Filtering Fields

    Follow the same changes in DATAPAGE 2, Wizard Step 5.

  • Wizard Step 6 – Select Results Page Fields

    Include the following from your list of Available Fields to your Selected Fields:

    1. Request_ID
    2. Assign_to
    3. Commute
    4. Resolve_by

    Click Next to continue.

  • Wizard Step 7 – Results Page Editing Options

    Make the following changes:

    1. Under Add Records, enable Inline insert.
    2. In the dropdown beside it, make sure that Top is selected.

    Click Next to continue.

  • Wizard Step 8 – Configure Results Page Fields

    Screenshot of the “Search and Report Wizard – Configure Results Page Field” menu.

    Make the following changes under DataPage Elements:

    1. Request_ID – Go to the Editing tab and apply the following changes in order to stamp request IDs in your task table:
      • Under Grid and Inline Editing Options, in the Form element dropdown, select Hidden.
      • Under Field Options, in the On load, receive dropdown, select External Parameters.
      • In the text box next to it, input [@InsertRecordRequest_ID].
    2. Assign_to – Go to the Editing tab and apply the following changes:
      • Under Grid and Inline Editing Options, in the Form element dropdown, select Dropdown.
      • Under Field Options, in the Source dropdown, select Custom values.
      • Under the Custom Values tab, in the Display field, input the following:
        • John Doe
        • Mary Smith
        • Carlos Jones

        Note that you can also link this field to a user table.

    3. Comments – Go to the Editing tab and apply the following changes:
      • Under Grid and Inline Editing Options, in the Form element dropdown, make sure that Text Area is selected.
    4. Resolve_by – Go to the Editing tab and apply the following changes:
      • Under Field Options, make sure that the checkbox for Calendar popup is enabled,

    Click Next to continue.

  • Wizard Sep 9 – Results Page Options

    Use the default settings and click Next to continue.

  • Wizard Step 10 – Details page

    Select the second radio button to have No Details Page.

    Click Finish to save your work and proceed to deployment.

Screenshot of Caspio’s app builder. It shows the “DataPages” section. There are three entries titled “Assign Tasks”, “Request Details” and “Request Form”.

DEPLOYMENT 3: form_3_request_details.html

Follow the embed process outlined in DEPLOYMENT 1 but paste your deploy code in its designated section on the second web page (form_3_request_details.html).

Screenshot of a sample page made on Caspio. It shows details of the user requests under “Request Details” and a menu to assign the request under “Assign Tasks”.

Upon submitting your request form, you’re passing the request ID to this webpage. That is why you’re able to filter details and assign tasks pertaining to a specific request.

To verify if the assigned tasks belong to the right request, view your request table (uvg_requests) and compare the request ID to the one stamped in your task table (uvg_tasks). You’ll find that both IDs are the same. You now understand why this is a one-to-many relationship — one request has multiple tasks.

See You in the Next Video!

In the next video, we’ll teach you how to build a payment form.

Next Steps

NEW IDC WHITE PAPER
See why low-code developers are growing 3x faster than traditional developers.
DOWNLOAD FREE REPORT
Talk to an expert
TALK TO AN EXPERT
Have a vision for an application? Talk to a Caspio product expert and we’ll help you achieve it.
REQUEST FREE CONSULTATION
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.
SCHEDULE A DEMO
Caspio Logo

Caspio is the world’s leading cloud platform for building online database applications without coding. Start a free trial today and experience the power of no-code.

  • AICPA SOC Logo

© 2023 Caspio, Inc. Sunnyvale, California. All rights reserved.