In Action: One-To-Many Relational DataPages

This article explains how to make an interface to display one-to-many relationships.  In this example we are creating a tool to keep track of customer contact history.  The end-user searches for a customer using a Report DataPage.  When the end-user locates the customer, they click on the details link and are then automatically directed to another webpage with the customer’s details, contact history, as well as an additional Submission Form to allow the end-user to submit a new log.

Envision – Table Design

In this example we are using a table called Rental_Application to hold all of our customer information and a second table called Customer_Log to hold the all of the logs entered for all of the customers.  The Rental Application table uses an AutoNumber field called Application_ID.  In database terms, this field is called a key because it is used to relate data between two or more tables.  The Customer Log table has a Number field which is also called Application_ID.  This is called a foreign key because it references the key value of the other table.

Create the Customer Search DataPage

The first DataPage that we will create will be the Customer Report.  The end-user will use this DataPage to locate an individual customer.  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, "Allow users to select data using a search form" is chosen by default, which is what we want.  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 details link

Before we click Next past the Configure Results Page Fields screen we need to insert an extra DataPage element.  In order for our end-users to jump directly to the details web page, we are going to replace the default details link with an HTML block containing an hyperlink.  Use the Insert button on the bottom right of the DataPage Elements panel to insert an HTML block after all of your other fields.  Inside the HTML block create an HTML link to the web page where the other three DataPages will be deployed.  In this example the URL is “http://www.bayarealandmark.com/internal/customerdetails.htm”.

Add the AutoNumber as a query string

At the end of the URL we are going to pass the customer’s unique ID (Application_ID) as a query string.  When configured properly all of the DataPages embedded in the details web page will receive this data.  Add a question mark at the end of the URL.  Type the fieldname of the parameter followed by an equal sign.  Finally, use the Insert button on the bottom right of the HTML block panel to insert the AutoNumber in string format.

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.