One-To-Many Relationships Using the Database Results Wizard

Sample

OrderNumber	OrderCustomer	OrderItem
13298276	Customer 1
		Item 1
		Item 2
28346563	Customer 2
		Item 2
		Item 3
		Item 4
		Item 5

Implementation

Normally, the DRW would display the customer order information along with each item. The master/detail display is accomplished by using a custom DRW query with a join on your master/detail tables and then adding an ASP script that displays the order row only when it changes. 

The first step is to create the DRW using a custom query that joins the two tables. For example;

SELECT Orders.OrderNumber, Orders.OrderCustomer, Items.OrderItem
FROM Orders LEFT JOIN Items ON Orders.OrderNumber = Items.OrderNumber;

The DRW results will initially look like this;

This is the start of the Database Results region.
OrderNumber	OrderCustomer	OrderItem
<<OrderNumber>>	<<OrderCustomer>>	<<OrderItem>>
This is the end of the Database Results region.

The next step is to modify the table that the DRW creates so that it has two rows, one containing results from the master table and a second containing results from the detail table. The DRW results will then look like this;

This is the start of the Database Results region.
OrderNumber	OrderCustomer	OrderItem
<<OrderNumber>>	<<OrderCustomer>>
		<<OrderItem>>
This is the end of the Database Results region.

Finally, in HTML view, locate the table row tags surrounding the master table results (illustrated by the Before column in the table below) and replace them with the script (illustrated by the After column in the table below);

Before	After
<tr> <td> ... </td> </tr>	<% If fp_rs("OrderNumber") <> savedID Then savedID = fp_rs("OrderNumber") %> <tr> <td> ... </td> </tr> <% End If %>

"OrderNumber" in the script above is the field used to identify when the order row changes. It can be a non-displayed field from the DRW.