In Action: One-To-Many Relational DataPages

This article describes how to make an app interface that incorporates a one-to-many relationship.  We show an example where  for each "customer" many "contact logs" are entered and displayed.

As shown below, the workflow for the app user is that they search the database, find the list of matching customers and drill down to a specific customer where they see the details of that customer plus a list of their past contacts and a form to enter a new log.

Begin with the Right Table Design

In any one-to-many relationship there are two tables involved; a Parent table and a Child table. The Parent table is where you have your primary or unique records. In our example Customer stored in a table called "Rental_Application" are the primary records. The parent table must have a field that uniquely identifies each field. It's called the ID field or the primary key. We have a filed called Application_ID that is an AutoNumber. AutoNumber fields are always unique. Your unique field doesn't have to be an AutoNumber but the Unique property of the field should be enabled in Table Design.

Each record in the child table is associated with a record in its parent table. This is done by a field in the child table that will contain the parent ID of the record to which it belongs. This field cannot be unique  because there may be multiple children for each parent record. If the field was set to Unique the database would not allow the id to be repeated. In our example we have Application_ID of type Number in Customer_Log table.

To enforce the relationship between the parent and child tables you could create a Relationship between them in table design.

Create the Customer Search DataPage

The first screen that end users see is a search form followed by the search results. Both screens will be configured in a Report DataPage.  In your Caspio Bridge Account go to DataPages tab and click New. Select the Reports DataPage type and choose either Tabular, Gallery or List.  Press Next.

Enable advanced options and parameters

Because this DataPage will be passing parameters we need to enable advanced options and parameters.  Click Next.  On the third screen, the default option "Allow users to select data using a search form" is good.  Click Next.

Choose and configure the search fields

Move the desired search fields from the left to the right-hand panel.  Click Next when finished.  This example doesn’t require much formatting for the search criteria.  We will set the comparison type for each field to Contains so that the end-user can enter any portion of a field value, or leave the field blank, and still see results.

Configure your results page fields

Select which fields will appear in the results page.  When finished, click Next again.  Now you can configure the results page fields.

Add an HTML block to replace the default link to Details

The typical Reports DataPages allows you to enable a Details page for data drill-down. However, the default Details page can only show the Parent record in Details view. We need something more complex because we need to display not only the parent record but also a child report and a child form. This is done through a customer details page and you need to know the URL where you will be deploying the customer details page. Ours is going to be at http://www.bayarealandmark.com/internal/customerdetails.htm. Additionally you need to configure your Results DataPage where this custom Details page is. Here is how:

On DataPage screen titled "Configure Results Page Fields" insert an HTML block by using the Insert button at the bottom right of the  Elements panel.  Inside the HTML block create an HTML link to the custom details  DataPages. At the end of the URL we insert the customer’s unique ID (Application_ID) .  This is how the parent ID is passed to all the child DataPages on the custom Details page. The HTML tag has the following template:

< a href="http://www.mydomain.com/mypage.html?field_name=field_value">Details</a>

 

Here is what you need to know about the tag above:

"Http" could be "https" if you need SSL connection and have an SSL certificate on your site. Caspio DataPages can be deployed in SSL but to avoid an alert on browsers your site also requires it own SSL certificate.

The URL must be customized to have your site and page of the custom Details page. The page name could end in anything not just ".html".

Question mark is necessary.

Field name is your choice but whatever you use must be used to receive paramter on all child DataPage. We suggest using the actual name of the primary key field to avoid confusion. Equal sign after the field name is necessary.

For Field Value you will use Caspio's special parameter feature to dynamically insert the field's value. Use the Insert button on the bottom right of the HTML block panel to insert your primary key. In our example it will be Application_ID  AutoNumber in string format.

Remember that no spaces are allowed anywhere in the URL or in the parameter name.

Select No Details Page

On the next screen you can choose the default sort order.  Press Next.  Because we’ve already created a details link there is no need to include the default details link.  Select No Details Page.  Click Finish to save the DataPage.

Create the Customer Details Report DataPage

The DataPages that will go on the details web page will all receive the Application_ID parameter from the query string and use that value to display or submit information.  First, we are going to create a Customer Details Report.  This DataPage will use the Application_ID as a pre-defined criteria to locate and display all of the details about the customer.

Enable advanced options and parameters

Select a Details Report.  Press next.  Since this DataPage will be receiving a parameter it is important to enable advanced options and parameters.  Click Next.  Select the radio button to Filter data based on your Pre-defined criteria.  Click the checkbox to allow parameters in search criteria.  Select the radio button to allow both Bridge and external parameters.  Click Next again.

Configure the AutoNumber field to receive a parameter

In the Advanced tab, choose Receive parameter.  The default parameter name in this case should be correct.  Make sure it matches the field name you used in the query string exactly.  Press Next.

Format the Details page

You can format the details page as normal.  Click Finish to save your DataPage.

Create the Log History Report Form

The Log History will be visible through another Report DataPage.  Select a Tabular Report and click Next.  Choose the Customer Log table as the base table.   This DataPage will receive the Application_ID parameter and return all of the previous log records associated with the customer’s unique ID.  We will enable advanced options and parameters in this DataPage as well.

Use pre-defined criteria and catch the parameter

Just as in the Customer Details Report DataPage we will use pre-defined criteria and enable both Bridge and external parameters.  The Application_ID field will receive the query string parameter and use it to filter the results.  Format the results and details pages as normal.

Create the New Log Submission Form

Now we will create a Submission Form DataPage so the end-user can add a new log to the Customer Log table.  This DataPage will receive the query string parameter and record it automatically.  This way all new logs will be associated with the correct customer.  Select the Submission Form option from the Forms DataPage types.  Press Next.  Because this DataPage also receives a parameter we are going to enable parameters.

Select the input fields including the foreign key

Include input fields by moving them to the panel on the right using the arrow button.  Be sure to include the Application_ID field.  Click Next.  Configure the fields as you would in any other Web Form.  The foreign key field (Application_ID) requires some special attention.  First select hidden from the Form element dropdown field.  This is done so your end-user cannot accidentally change the unique ID.  Next go to the Advanced tab and choose Receive parameter from the On Load section.  Now this field will receive the parameter from the query string and record it automatically.

Choose the same form as the destination page option

On the last screen of the DataPage wizard we will change the destination option to the Same form.  This way when the end-user submits a new log the DataPage will reload with a fresh, empty New Log Form available for another log entry.

Deploy the DataPages

Now that the DataPages have been created we can deploy them into two web pages.  The first Search and Report DataPage will go on its own web page.  The Customer Details Search and Report, The Customer Contact History Search and Report, and the New Log Web Form will all be on the second web page.

Testing and Formatting

Be sure to enter some data into your tables to make sure the parameters are being passed and received correctly.  If you are having any trouble, be sure to ask:

• Are parameters enabled in all of my DataPages?
• Is the name of my parameter identical on both the passing and receiving pages?
• Does the details page link URL include “http://” at the beginning?
• Does my query string include a question mark, a fieldname, an equal sign, and a correctly formatted parameter from the Insert button?

Once the link between the two DataPages functions correctly we can take some time to improve the look and feel of the DataPages to match the host website.  In this example we changed the style and added a header section to each DataPage to explain its functionality.