Master/Detail

The default Duplicate action available in Code On Time web apps performs a client-side duplication. When the action is activated, the create form will be opened and the fields will be populated using values from the duplicated record. The new record will only be saved to the database when the user presses OK.

Sometimes it is necessary for more complex operations to be performed when a record is duplicated. For example, when a master record is duplicated, the child records should be duplicated as well. This will require a server-side duplication. A custom action will be implemented – when the user activates this action, the master and detail records will be copied and saved to the server. The order will then be displayed in the edit form.

Let’s implement a master-detail duplication with Orders and Order Details controllers in the Northwind database.

Start the Project Designer. In the Project Explorer, switch to the Controllers tab. Right-click on Orders / Actions / ag1 (Grid) node, and press New Action.

Assign the following values:

Press OK to save the action. Right-click on Orders / Business Rules node, and press New Business Rule.

Use the following:

In the Script property, enter the following:

-- duplicate the order
insert into Orders (CustomerID, EmployeeID, OrderDate, RequiredDate, 
                    ShippedDate, ShipVia, Freight, ShipName, ShipAddress, 
                    ShipCity, ShipRegion, ShipPostalCode, ShipCountry)
select CustomerID, EmployeeID, getdate(), RequiredDate, null, ShipVia, 
        Freight, ShipName, ShipAddress, ShipCity, ShipRegion, 
        ShipPostalCode, ShipCountry
from Orders
where OrderID = @OrderID

-- find ID of duplicated order
declare @NewOrderID int;
select @NewOrderID = @@IDENTITY;

-- duplicate order details
insert into "Order Details" (OrderID, ProductID, UnitPrice, 
                            Quantity, Discount)
select @NewOrderID, ProductID, UnitPrice, Quantity, Discount
from "Order Details"
where OrderID = @OrderID

-- open edit form for duplicate order
set @Result_NavigateUrl = 'Orders.aspx?OrderID=' + cast(@NewOrderID as nvarchar)
                          + '&_controller=Orders&_commandName=Edit'
                          + '&_commandArgument=editForm1'

The business rule will first duplicate the order. The OrderDate will be assigned the current date, and RequiredDate will be set to null. Then, the ID of the new order will be acquired using the @@IDENTITY function. The order details will be duplicated using the new ID. Finally, the client library will be prompted to navigate to open the new order using URL parameters. Press OK to save the business rule. On the toolbar, press Browse.

Navigate to the Orders page. Using the context menu on a row, activate the Duplicate Order option.

The order and associated order details will be duplicated, and it will be displayed in the edit form.

If multiple orders are included in the “order details” data set, then a simple filtered report will produce an incorrect output. This will happen if the Report action has a missing or incorrect filter in its Data property.

For example, the header of the report presented below displays the order information from the first “order details” data row. Order details from multiple orders are listed without any separation. The subtotal and total are calculated from the sum of Extended Price of all order details included in the data set. 

In fact, order #10248 has only two “order detail” rows in the Northwind database.

Let’s change the custom report template to group items by OrderID and CustomerID to ensure valid output even in situations when a report data set contains details of multiple orders.

Adding Grouping by Order

First, decrease the horizontal size of the  tablix by clicking and dragging on the dividers in the grey area above the column headers.

Right-click on the grey area to the left of the [ProductProductName] data cell. Select Add Group | Parent Group… option.

In the Tablix group window, select the following options:

Press OK to save. A row group has been added to the tablix. Select the original header label cells by clicking on Product Name, then holding Shift key and clicking on Extended Price. Right-click, and press Cut.

Right-click on the cell below Product Name, and press Paste.

The column headers will be inserted inside the group.

Right-click on the grey area to the left of the first row in the tablix. Press Delete Rows.

Inserting Order Header

Let’s add another row above the headers to provide a placeholder for the order information. Right-click in the grey area to the left of the column headers, and press Insert Row | Inside Group – Above.

Select the entire row that was added. Right-click, and select Merge Cells option.

Delete the text in OrderID column. Use the divider in the grey area to “minimize” the OrderID column.

Drag a Rectangle element from the Toolbox, and drop it onto the header row of the group.

Select all elements in the header area of the page, right-click, and press Cut.

Right-click on the rectangle, and press Paste. The header elements for the order will be moved from the page header to the group.

Inserting Order Footer

Select the entire row below the data cells in the tablix. Right-click, and press Merge Cells.

From the Toolbox window, drop a Rectangle element onto the row.

Select all elements in the report footer. Right-click and press Cut.

Right-click on the row underneath the data cells, and press Paste.

The order footer elements will be pasted into the tablix row.

Adding Grouping By Customer

Right-click on the grey area to the left of the first row, and press Add Group | Parent Group… option.

Specify the following parameters:

Press OK to add the group.

Delete the text in the first column, and “minimize” the column.

Select all the cells in the first row. Right-click and press Merge Cells.

In the row, type the following:

On the toolbar, set the font size to “24”, and set the text as bold, underlined, and center-aligned.

Expand the first row using the handle of the grey area to the left of the row to make sure the text fits.

Fixing Subtotal and Total Formulas

The Subtotal and Total fields are currently displaying a sum for all order details in the returned dataset. Let’s fix the formulas to ensure the correct data is being displayed for each order.

Right-click on the Subtotal field, and press Expression.

Replace the expression with the following:

=Sum(Fields!ExtendedPrice.Value)

Press OK to save.

Right-click on the Total field, and press Expression.

Use this expression:

=Sum(Fields!ExtendedPrice.Value) + First(Fields!OrderFreight.Value)

Press OK to save.

If a data set with “order details” from multiple orders is used with the new report template with “grouping”, then each order and its details will be rendered on a separate page.