Building Master Detail Web Applications with Grid Components
Description
Learn how to build a Master Detail web application using Grid components.
Important Notice for Community Edition Users
This tutorial was built using the full Alpha Anywhere Development Environment. Portions of this tutorial may not apply due to features that are not available in Community Edition. Community Edition users may encounter screens that don't match their environment due to differences between the two development environments.
We strongly recommend starting with the tutorials in the Alpha Software Learning Center to start learning Alpha Anywhere.
Overview
In this tutorial, you will build a Master Detail application that allows a user to view Customer records and inspect the Orders and Order Details for individual customers. Each customer in the Customers table can have one or more Orders, and each order can have one or more related Order Details.
To represent this relationship between the Customers, Orders, and Order Details tables, you will create 3 Grid Components corresponding to each table. Grids can be "linked" together using Alpha Anywhere's Linked Content Sections, creating parent-child relationships between the components. Linked Content can be added to a Grid component using two methods: using the Row Expander or by creating a Linked Content Section. This tutorial employs both features to create the interfaces for viewing the Customers and Orders.
The Grids will be built from the bottom-up, starting with creating the Grid component for the Order Details table. Approaching construction of the Grids for each table in this fashion allows you to link Grids together as they are created.
Alternatively, you could follow a top-down approach, creating the parent Grid for Customer records first. While you can arrive at the same result with a top-down approach, the top-down approach requires working with multiple Grid components simultaneously in order to link the Grids together.
By the end of this tutorial, you will have a Master Detail application for displaying records from the Customers, Orders, and Order Details tables of the Northwind database.
Before you Begin
This tutorial requires the following resources before you begin. :
A new or existing workspace
A Connection String for the Northwind Database
The Development Server
You can build these resources yourself or download a pre-configured workspace.
Download the Pre-Configured Workspace
If you're ready to start building, download the pre-configured workspace, which includes a predefined connection string and the Northwind database. This workspace also includes a popup help window that will be displayed when you open the workspace.
Download the workspace. Click here to download the pre-configured workspace
Unzip the workspace inside a folder in the Documents folder in your Users directory. Eg, C:/Users/your_windows_user_name/Documents/GettingStartedWorkspace. The workspace must be placed in a directory that you have read/write access.
Open the workspace. This can be done by double-clicking the GettingStartedWorkspace.adb file.
Creating the Workspace
Follow the tutorials below to create a new workspace and setup the Northwind database. Each tutorial includes detailed, step-by-step instructions that guide you through the process of creating a new workspace, building AlphaDAO connection strings, or setting up the Alpha Anywhere Development Server, which allows you to test web and mobile applications in your development environment.
Create a new Alpha Anywhere Workspace: Learn how to create a new workspace for a mobile or web application by following this step-by-step guide.
Creating AlphaDAO Connection Strings: Learn how to create an AlphaDAO connection string to an Access, MySQL, or SQL Server database using the Northwind database that ships with Alpha Anywhere.
Configuring the Alpha Anywhere Development Server: Learn how to configure the Alpha Anywhere Development Server for testing your web and mobile applications.
Lesson 1: Creating the Grid for the Order Details Table
In this Lesson, you will learn the following:
How to create a Grid component based on a SQL Select Statement.
How to use Display Formats to format numeric fields.
How to add and display a summary total value for a field in the Grid footer.
Create a new Grid Component. From the Web Projects Control Panel, click the New button in the toolbar.
When the New File dialog appears, select Web Component and click Next.
Creating a new Web Component using the New File dialog.Select Grid and click Next.
Creating a new Grid from the New File dialog.Select Start with a blank Grid Component and click OK.
Choose Tabular for the Grid Layout on the Component Type tab.
Click on Data Source. Make sure SQL Database accessed via AlphaDAO is selected.
Next, open the Query (AlphaDAO) pane. Click the Select... button to create a connection to a SQL Database.
Select the Northwind connection string and click OK.
If you don't have a named connection string for the Northwind database, follow the instructions in the Creating AlphaDAO Connection Strings tutorial. The Creating AlphaDAO Connection Strings tutorial will guide you step-by-step through the process of setting up the Northwind database and creating an AlphaDAO connection string.
Back on the Query (AlphaDAO) pane, click the Connect button to connect to the Northwind database and build a SQL Query for the Grid Component.
Click the SQL Select Statement radio option. The SQL Select Statement option lets you to define a SQL query used to populate the fields and data in the Grid Component. Click the Edit SQL in Query Builder button to open the SQL Genie.
In the SQL Genie dialog, click the Add Table button on the Tables tab to add the Order Details table.
Select Order Details in the Add Table dialog and click OK.
Let's add a column to the SQL Query that calculates the total for each record in Order Details. On the Columns tab, click the Insert Expression link below the list of Available Columns.
In the SQL Expression dialog, create an expression that calculates the total price for a record. The total price is the Price times the Quantity. The Price is calculated by subtracting the Discount from the UnitPrice.
Enter the following expression in the Enter expression: text area then click OK:
((UnitPrice - (Discount * UnitPrice)) * Quantity)
Change the Alias property for the SQL Expression to Total. Then, click OK to save the Query.
Next, open the Fields pane. Select all database fields in the Available Fields column. Do not select the <RowNumber> or <LogicalRecNo> fields. Click the Select Field(s) button to add the selected to the Grid Component. These are the fields that will be displayed when the Grid Component is rendered.
Let's add some formatting to the UnitPrice, Discount, and Total fields. Select the UniPrice field. Click on the
button for the Display format property to open the Display Format dialog.
In the Display Format dialog, click the Select Pre-Defined Format button to display a list of Pre-Defined Formats.
In the Pre-Defined Formats dialog, select the $123,456.78 format. Click OK.
The selected Display Format will format the UnitPrice field to display as a dollar amount with 2 decimal places. Click OK to save the Display Format for the UnitPrice field.
Select the Total field and click the
for the Display format property. Add the same format used in the UnitPrice field to the Total field:alltrim(str(convert_type(<value>,"N"),250,2,"$("))Next, select the Discount field. Click the
button for the Display format property. The Discount field is a percentage value. To display the Discount field with as a percentage, you need to multiply its value by 100 before formatting it. Enter the following into the Display Format text area int he Display Format dialog and click OK:alltrim(str(convert_type(<value>,"N")*100,250,0,"%"))
Next, let's add a "Total" to the grid footer that displays the sum of the Total field for all records in the Grid. Select the Total field. In the Summary values section for the Field Properties for the Total field, check the Total box.
Next, add a display format for the Summary value. Click the
button for the Total display format property. Add the same format defined for the UnitPrice field's Display format. alltrim(str(convert_type(<value>,"N"),250,2,"$("))Add the summary total for the Total field to the Grid footer. In the left hand column, click Properties to display the Grid's Properties pane.
By default, summary fields are only shown on the last page of the Grid component. This behavior is defined by the Show on last page only property. To show the summary total for the Total field on every page in the Grid, you must disable Show on last page only property. Find the Show on last page only property and uncheck it. This will display the summary total value for the Total field in the Grid footer on every page in the Grid component.
Preview the OrderDetailsGrid component. Click on the Live Preview tab. If prompted, start the Development Server.
You should see a Grid with 6 columns. The UnitPrice and Total column should display as dollar amounts. The Discount column should appear as a percentage. At the bottom of the Grid between the navigation footer and records, you should see the summary total for the Total field.
The summary field will be the total for all records in the Order Details table. When the Order Details grid is linked to the Orders Grid, a filter will be applied to the SQL query that fetches the records from the database. When filtered on a specific order, the summary total will be the total for the Order.
Save the Grid component as "OrderDetailsGrid". Click the
Save button in the UX Builder toolbar. Type OrderDetailsGrid in the File name text box and click Save.
The Grid component for the Order Details table is now complete. The next step is to create a Grid for the Orders table and add a Row Expander to display the Order Details for a record in the Orders table.
Lesson 2: Creating the Grid for the Orders Table
In this Lesson, you will learn the following:
How to create a Grid component based on a Table or View.
How to display related information in a Grid component using the Row Expander.
Now that you have a Grid component for the Order Details table, you can create a Grid for the Orders table to display a list of orders. The Order Details can be linked to their respective Orders using a Row Expander. The Row Expander allows you to define one or more linked content items - such as Grid components - displayed in a "row expander". The row expander can be shown for each record by clicking the "Expand Row" icon. Clicking the row expander icon expands the row, revealing the linked content within the Grid Part.
The Expand Row icon is displayed as a Plus or Minus icon at the beginning of each row in the Grid Component. The row expander icon indicates the state of the row expander for a row. A "Plus" icon is shown when the row is collapsed while a "Minus" icon is shown when the row is expanded.
Start by creating a new Grid component based on a SQL data source. On the Query (AlphaDAO) pane, connect to the Northwind database. Set the Connection Name property to the Northwind connection string and click the Connect button to expose the Query options for defining the query the Grid component will be based on.
The Orders grid will be based on the Orders table. Select the Table or View radio button. Then, click the Select... button for the Table/View name property.
In the Select Table dialog, select the Orders table. Click OK.
The SQL data source for the Grid is now configured. Click on Fields in the left-hand column to open the Grid Builder Fields pane.
Select the OrderID, CustomerID, OrderDate and ShippedDate fields and click the Select field(s) button to add the fields to the Grid component.
Next, you will add a Row Expander that shows information from the Order Details table for an order by linking the OrderDetailsGrid to Orders grid. Click on Properties in the left-hand column to open the Grid Properties pane.
Locate the Row Expander section in the Grid Properties and check the Has row expander property.
Next, click the
button for the Row expander linked Content property to open the Define Linked Content dialog.
In the Define Linked Content dialog, click the Add Object button to add a new linked component. Select Add linked Component from the popup menu.
In the Select Component dialog, select the OrderDetailsGrid Grid component from the list of options. Then, click OK.
In the Grid Properties, define the link fields for the linked content. The link definition defines a filter that should be applied to the linked Grid component. For the Orders Grid, we would like to display all of the Order Details for a record in the Orders table. This is done by filtering the Order Details on the OrderID.
Select OrderID (N) for the Parent Field.
For the Child Field, select OrderID (N). Click OK to create the new Linked Content Section.
Save the Orders grid component as "OrdersGrid". Click the
Save button in the UX Builder toolbar. Type OrdersGrid in the File name text box and click Save.
Preview your component in Live Preview.
Each order in the Orders Grid includes a small plus icon to at the beginning of each row. This is the Row Expander icon.
Click the icon for a row. This will open the Row Expander for the order record in that row. Inside the Row Expander, you will see the Order Details for the order.
The Orders Grid
Order Details are shown when the Row Expander is opened.
Lesson 3: Creating the Grid for the Customers Table
In this lesson, you will learn the following:
How to add a Linked Content section to a Grid component's Freeform Edit Region.
The final step in creating a Master-Detail interface for the Customers, Orders, and Order Details tables in the Northwind database is to create a Grid component for the Customers table and link the Orders Grid to the Customers Grid, allowing you to view the orders for a selected Customers record. Instead of using a Row Expander, in this lesson you will use a Linked Content Section placed in the Freeform edit region of the Grid component to display the Orders for a selected customer record.
Start by creating a new Grid component based on a SQL data source. On the Query (AlphaDAO) pane, connect to the Northwind database. Set the Connection Name property to the Northwind connection string, and click the Connect button to expose the Query options for defining the query the Grid component will be based on.
The Customers grid will be based on the Customers table. Select the Table or View radio button. Then, click the Select... button for the Table/View name property.
In the Select Table dialog, select the Customers table. Click OK.
The SQL data source for the Customers Grid is now configured. Click on Fields in the left-hand column to open the Grid Builder Fields pane.
Select the CustomerID, ContactName, Address, City, Region, and Country fields from the list of Available Fields. Click the Select field(s) button to add the selected fields to the Grid component.
Next, open the Properties pane for the Grid component. Locate the Linked Grid/Content section and check the box for the Has linked Grid or other content property.
Click the
button for the Linked content definition property to open the Linked Content Builder.
Add a new Linked Content Section. Click the Add Linked Content Section. Name it ORDERS and click the OK button.
Select ORDERS listed in the Linked Content Section and click the Define Linked Content button to open the Define Linked Content dialog.
Next, you will add the OrdersGrid to the Linked Content Section. In the Define Linked Content dialog, click the Add Object button and select Add linked component.
In the Select Component dialog, select OrdersGrid (Grid) from the list of components. Click OK.
Link the CustomerID field in the Customers Grid to the CustomerID in the OrdersGrid component. Select CustomerID (C) for the Parent Field.
In the Child Field, make sure CustomerID (C) is selected. Then click OK to save the Linked Content Section.
The ORDERS Linked Content Section needs to be placed in the Grid Component's layout so that it can be seen. Linked Content Sections can be placed in either the Freeform Edit Regions or Master Template for the Grid Component. Place the ORDERS Linked Content Section in the Freeform Edit Region for the Grid Part on the Right side of the grid.
Click the Edit 'Freeform Edit Region' button. Mouse over Grid Part and select Right from the popup menu.
In the Freeform Edit Region dialog, double-click on the placeholder for the ORDERS content section. It is listed in the Available Placeholders list on the left-hand side of the Freeform Edit Region dialog. Double-click {LinkedContentSection:ORDERS}. This will add the placeholder to the text area on the right, which is used to define HTML that will appear in the Freeform Edit Region. Click OK.
Click OK to close the Linked Grid or Content Builder dialog.
Save the grid as "CustomersGrid". Click the
Save button in the UX Builder toolbar. Type CustomersGrid in the File name text box and click Save.
Preview your component in Live Preview.
When the component loads in Live Preview, you will see two Grids. The Grid on the left is the list of Customers. The Grid on the right is the list of orders for the selected customer record. When the component is first loaded, the first record in the Customers Grid is select.
You can expand the Order Details for an Order by clicking the Row Expander icon in the Orders Grid. As you select different Customer records, the Orders Grid will update, loading the Orders for the currently selected customer.
The Customers Grid with the Orders shown in the Linked Content Section on the right.
You can explore the details for an order by clicking the Row Expander icon.
Multiple Grid Components can be linked to a Grid in a Linked Content Sections and Row Expander. Other components - UX Components, Calendar Components, Gallery Components, Custom Components, etc - can also be linked to a Grid Component using the Linked Content and Row Expander tools. In addition, using the Linked Content Section is not mutually exclusive from the Row Expander. You can add link components to the Grid component using both methods.
Using linked Grid components shown in the Freeform Edit Region or a Row Expander allows you to build robust Master-Detail interfaces, allowing you display detailed information for data in your database how and where you want. You can build interfaces for a wide variety of data relationships using Grid components.
See Also