Code On Time: Master/Detail

Blog

Master/Detail

Saturday, January 19, 2013Print Subscribe

The Filter Field property specifies the foreign key field on the child data view that will be filtered in a master-detail relationship.

Drag & drop techniques can be used in order to automatically configure the relationship. For example, the picture below shows how to configure CustomerID as the filter field between Customers and Orders data views.

However, when the master data view has a compound primary key, the relationship must be configured manually.

Let’s add a Notes table to the database that will have a compound primary key that refers to OrderID and ProductID.

The table will then be added to the project. A Notes data view will be added to the default Orders page in order to compose a three-level master-detail relationship.

Creating the Notes Table

Start SQL Server Management Studio and connect to your database. In the Object Explorer, right-click on Databases / Northwind node, and press New Query.

Paste in the following script:

create table dbo.Notes(
    IDofOrder int not null,
    IDofProduct int not null,
    Notes ntext not null,
    Created datetime not null,
     constraint PK_Notes primary key clustered
    (
        IDofOrder asc,
        IDofProduct asc
    )
)

alter table dbo.Notes add constraint FK_Notes_OrderDetails_OrderID foreign key (IDofOrder)
references dbo.Orders (OrderID)

alter table dbo.Notes add constraint FK_Notes_OrderDetails_ProductID foreign key (IDofProduct)
references dbo.Products (ProductID)

Click on Execute on the toolbar to run the query.

Adding the Table to the Project

Start the web application generator. Click on the project name, and press Refresh. Check the box next to dbo.Notes table to add it to the project, and press Refresh.

Adding Notes Data View

Right-click on Notes controller node, and press Copy.

Switch back to the Pages tab. Right-click on Customers / Orders page node, and press Paste. The data controller will be instantiated as a data view in a new container.

Configuring Master-Detail Relationship

Double-click on Customers /Orders / c101/ view4(Notes) node.

Make the following changes:

Property	New Value
View	grid1
Text	Notes
Filter Source	view3
Filter Field #1	IDofOrder
Filter Field #2	IDofProduct
Auto Hide	Container

Press OK to save.

Viewing the Results

On the Project Designer toolbar, press Browse. Navigate to the Orders page. Select an order from the list, and then select an order detail. The Notes data view will appear underneath, filtered by OrderID and ProductID.

Labels: Application Builder, Master/Detail, Web Application Generator

Monday, January 14, 2013Print Subscribe

“Filter Source” Property for Data Views

The Filter Source property specifies the master data view that will pass down a filtering parameter to the child data view. The child data field that will be filtered is declared in the Filter Field property, and must be specified in addition to Filter Source in order for master-detail relationships to work. When the master data view has a compound primary key, up to 5 Filter Fields must be specified in order.

Let’s configure a master-detail relationship between Customers and Orders controllers in the Northwind database.

Adding the Page

Start the Project Designer. On the Project Explorer toolbar, click on the New Page icon.

Assign a name:

Property	Value
Name	Customers and Orders

Press OK to save. Switch to the Controllers tab. Select Customers controller node. While holding Ctrl key, select Orders node. Right-click on Orders, and press Copy.

Switch back to the Pages tab. Right-click on Customers and Orders node, and press Paste. The two controllers will be instantiated as data views in separate containers.

Configuring Master-Detail Relationship

However, these data views are currently independent from each other – a master-detail relationship must be configured.

Expand Customers and Orders / c102 / view2 (Orders) / grid1 node. Drop CustomerID –> CustomerCompanyName data field node onto Customers and Orders / c101 / view1 (Customers) node.

Because CustomerID field is matched to a similar field in Customers controller, a master-detail relationship is automatically configured. Filter Source is changed to “view1”, and Filter Field is changed to “CustomerID”. In addition, the Page Size has been changed to “5”, Auto Hide has been changed to “Container”, Show Modal Forms has been enabled, and Text is changed to the name of the controller, “Orders”.

On the Project Designer toolbar, press Browse. When generation is complete, navigate to the Customers and Orders page.

The page will initially only show a list of customers.

When a row is selected, a list of orders will appear underneath. These orders will be filtered by the value of “CustomerID” field in the selected row of “view1” data view (Customers).

Note that the filtered “CustomerID” data field in Orders view is hidden in order to prevent display of duplicate data. An SQL action may be implemented if the hidden foreign key field needs to be changed.

Labels: Application Builder, Master/Detail, Web Application Generator

Wednesday, January 2, 2013Print Subscribe

Duplicating a Record with SQL Business Rule

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:

Property	Value
Command Name	Custom
Command Argument	DuplicateOrder
Header Text	Duplicate Order
Confirmation	Are you sure you want to duplicate this order?

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

Use the following:

Property	Value
Type	SQL
Command Name	Custom
Command Argument	DuplicateOrder
Phase	Execute

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.

Labels: Business Rules/Logic, Master/Detail, User Interface, Web Application Generator