Master/Detail

Labels
AJAX(112) App Studio(7) Apple(1) Application Builder(245) Application Factory(207) ASP.NET(95) ASP.NET 3.5(45) ASP.NET Code Generator(72) ASP.NET Membership(28) Azure(18) Barcode(2) Barcodes(3) BLOB(18) Business Rules(1) Business Rules/Logic(140) BYOD(13) Caching(2) Calendar(5) Charts(29) Cloud(14) Cloud On Time(2) Cloud On Time for Windows 7(2) Code Generator(54) Collaboration(11) command line(1) Conflict Detection(1) Content Management System(12) COT Tools for Excel(26) CRUD(1) Custom Actions(1) Data Aquarium Framework(122) Data Sheet(9) Data Sources(22) Database Lookups(50) Deployment(22) Designer(177) Device(1) DotNetNuke(12) EASE(20) Email(6) Features(101) Firebird(1) Form Builder(14) Globalization and Localization(6) How To(1) Hypermedia(2) Inline Editing(1) Installation(5) JavaScript(20) Kiosk(1) Low Code(3) Mac(1) Many-To-Many(4) Maps(6) Master/Detail(36) Microservices(4) Mobile(63) Mode Builder(3) Model Builder(3) MySQL(10) Native Apps(5) News(18) OAuth(9) OAuth Scopes(1) OAuth2(13) Offline(20) Offline Apps(4) Offline Sync(5) Oracle(11) PKCE(2) Postgre SQL(1) PostgreSQL(2) PWA(2) QR codes(2) Rapid Application Development(5) Reading Pane(2) Release Notes(183) Reports(48) REST(29) RESTful(29) RESTful Workshop(15) RFID tags(1) SaaS(7) Security(81) SharePoint(12) SPA(6) SQL Anywhere(3) SQL Server(26) SSO(1) Stored Procedure(4) Teamwork(15) Tips and Tricks(87) Tools for Excel(2) Touch UI(93) Transactions(5) Tutorials(183) Universal Windows Platform(3) User Interface(338) Video Tutorial(37) Web 2.0(100) Web App Generator(101) Web Application Generator(607) Web Form Builder(40) Web.Config(9) Workflow(28)
Archive
Blog
Master/Detail
Friday, December 21, 2012PrintSubscribe
Order Form Sample–Part 38

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. 

Simple report created from multiple orders.

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

SQL query to select order details from the specific order shows the inaccuracy of the report.

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.

Decreasing the size of the tablix.

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

Adding a new parent row group to the tablix.

In the Tablix group window, select the following options:

Property Value
Group by: [OrderID]
Add group header true
Add group footer true

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.

Cutting the column headers from the first row.

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

Pasting column headers into the second row inside the group.

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.

Deleting the first row.

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.

Inserting a row inside the group.

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

Merging the cells in the added row.

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

Minimizing the 'OrderID' column.

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

Adding a rectangle element to the row.

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

Cutting all elements from the header.

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

Pasting the header elements inside the first row of the group.

Inserting Order Footer

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

Merging the cells in the footer row of the group

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

Dropping a rectangle into the footer row.

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

Cutting the report footer elements.

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

Pasting footer elements into the group footer.

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.

Adding another parent row group.

Specify the following parameters:

Property Value
Group by: [OrderCustomerID]
Add group header true

Press OK to add the group.

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

Minimizing the Customer column.

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

Merging the cells in the first row.

In the row, type the following:

[OrderCustomerCompanyName]

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

Changing the text properties of the first row.

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.

Activating the Expression context menu option for 'Subtotal' field.

Replace the expression with the following:

=Sum(Fields!ExtendedPrice.Value)

Press OK to save.

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

Activating the Expression context menu option for 'Total' field.

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.

Report created using grouped template shows accurate data.

Saturday, September 1, 2012PrintSubscribe
Creating a Three-Level Master-Detail Page

Let’s create a page with a three-level master-detail relationship between Customers, Orders, and Order Details controllers.

Three-level master-detail relationship between Customers, Orders, and Order Details tables.

Creating the Page

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

New Page icon on the Project Explorer toolbar.

Enter “OrderManager” in the page Name and press OK to create a new page.

Property Value
Name OrderManager

The Order Manager page will be added to the bottom of the list of pages in the Project Explorer. Drag Order Manager node to the right side of Home node to place it after the page.

Dropping Order Manager page node on the right side of Home node.     Order Manager page is placed after Home page.

Switch to the Controllers tab in the Project Explorer. While holding Ctrl, select the controllers in the following order: Customers, Orders, OrderDetails. Right-click on OrderDetails, and press Copy.

Copy context menu option on OrderDetails controller node in the Project Explorer.

Switch back to the Pages tab. Right-click on Order Manager page node, and press Paste option. The controllers will be instantiated as data views in separate containers on the page.

Paste context menu option on the Order Manager page node in the Project Explorer.     Views for Customers, Orders, and Order Details controllers have been added to the Order Manager page.

Right-click on Order Manager page node, and press View in Browser.

'View in Browser' context menu option for a page node will generate the project and open the relevant page in the default browser.

Navigate to the Order Manager page. Three views will be available on the page, but they do not have any master-detail relationships established between them. The three data views are completely independent from each other.

Order Manager page with three unconnected data views. Filters have been applied to reduce the size of the image.

Establishing Master-Detail Relationships

Switch back to the Project Designer. In the Project Explorer, drag the data field node Order Manager / c102 / view2 / grid1 / CustomerID onto the view node Order Manager / c101 / view1. This will configure a master-detail relationship between view2 and view1, with CustomerID as the filter field.

Dragging CustomerID data field node onto 'view1' data view node.     'View2' has been configured as a detail of the master view 'view1'.

Next, drag the data field node Order Manager / c103 / view3 / grid1 / OrderID and drop it onto view node Order Manager / c102 / view2.

Dragging OrderID data field node onto 'view2' data view node.     'View3' has been configured as a detail of the master view 'view2'.

On the toolbar, press Browse.

Only the Customers data view is now visible on the Order Manager page.

Order Manager page with only the list of customers displayed.

Select a record from the list of customers, and orders related to the selection will appear in a data view underneath.

When a customer has been selected, a list of order will appear.

Select an order, and order details for that order will be displayed.

When an order is also selected, a list of relevant order details will be displayed.

Select a child order detail. The record will be displayed in a modal form.

Opening an Order Detail record will display fields in a modal form.

If a master data view enters “New” mode, the child data views will automatically become hidden.

For example, if a new customer is created, the data views Orders and Order Details will be invisible. If a new order is created, only Order Details data view will disappear.

When a new customer is created, Orders and Order Details views are hidden.

Note that the field being used to filter must have Allow Query-by-Example enabled.

Monday, August 6, 2012PrintSubscribe
Using Drag and Drop to Configure a Master-Detail Relationship

Master-detail pages can be quickly created in Code On Time web applications using drag and drop operations in the Project Designer.

Creating a Page

Start the Project Designer. In the Project Explorer toolbar, press New Page.

New Page toolbar option in the Project Explorer of web application designer.

Assign the following properties, and press OK to save the page.

Property Value
Name Master Detail Page

The page will appear at the bottom of the page hierarchy in the Project Explorer. Drop it on the right side of Home node to place it in the second position in the navigation menu.

Dropping Master Detail Page on the right side of Home page.     Master Detail Page placed after Home page in the Project Explorer.

Adding Data Views

Switch to the Controllers tab. While holding down Ctrl key, click on the controllers in this order: Customers, Orders, OrderDetails. Right-click on OrderDetails node and select Copy.

Copy context menu option for three selected controllers in the Project Explorer.

Switch back to the Pages tab. Right-click on Master Detail Page and press Paste. The three copied controllers will be pasted as data views each in their own container.

Paste context menu option for a page node in the Project Explorer.     Master Detail Page with three new data views in individual containers after pasting.

Configuring Master-Detail Relationships

The data views have been added to the page, but there are no relationships between them yet.

Expand to Master Detail Page / c102 / view2 / grid1 / CustomerID data field node. Drag CustomerID and drop on view1 node. The data view view2 will be configured to filter orders by the customer selected in view1 data view.

Dropping CustomerID data field from view2 to view1.     Data view 'view2' is now configured with a master-detail relationship with 'view1'. Records will be filtered according to the selected record in 'view1'.

Expand to Master Detail Page / c103 / view3 / grid1 / OrderID data field node. Drag OrderID and drop on view2 node. Data view view3 will be configured to filter order details by the order selected in view2 data view.

Dropping OrderID data field node on 'view2'.     Data view 'view3' is now configured with a master-detail relationship with 'view2'. Records will be filtered according to the selected record in 'view2'.

Testing the Three-level Master-Detail Page

In the Project Explorer, right-click on Master Detail Page node and press View in Browser.

View in Browser context menu option for a page node in the Project Explorer.

The web application will be generated and the page will open in your default browser. Select a customer, and a list of related orders will appear underneath. Select an order, and related order details will be displayed.

Master Detail Page configured with a three-level master-detail relationship.

Further Customization

The standard form views have their fields rendered top down. The screenshot shows form views of related customer and order.

The standard form views have their fields rendered top down.

Let’s customize the presentation of both form views.

Switch back to the Project Designer. In the Project Explorer, double-click on Master Detail Page / c101 / editForm1 / c1 category node.

Category c1 in editForm1 of Customers controller.

Change the Floating property to “Yes”, and press OK to save the category.

Property New Value
Floating Yes

In the Project Explorer, right-click on Master Detail Page / c102 / view2 / editForm1 / c1 node and press New Category.

New Category context menu option for the view 'editForm1'.

Assign the following properties.

Property New Value
Header Text Shipping Info
New Column Yes

Drag all the Ship- data fields from c1 – Orders category and drop them on c2 – Shipping Info category.

Dropping all shipping data fields on 'c2' category.     All shipping data fields have been moved into 'c2' category.

Right-click on Master Detail Page node, and press View in Browser.

View in Browser context menu option for Master Detail Page in the Project Explorer.

When the page loads, select a customer from the list. The form view will be more compact as the fields are floating. Click on an order – the data fields will be split in two columns.

Master Detail Page with customized categories for Customers and Orders. Master Detail Page with customized categories for Customers and Orders.

If you select an order detail record, it will show up in a modal window. This behavior has been configured automatically when the master-detail relationship was established between view3 (Order Details) and view2 (Orders) data views.

If you select an order detail record, it will show up in a modal window.