Master/Detail

Let’s examine two tables in the Northwind database, Employees and Orders. Each order is linked to an employee via the EmployeeID foreign key relationship.

A Code On Time web application will perform automatic denormalization and use the LastName field as alias for EmployeeID. This field is shown below, labeled “Employee Last Name”.

The end user may want to see the full name of the employee instead.

Let’s create a calculated field that combines First Name and Last Name of the employee, and use this field as an alias for EmployeeID in both Employees and Orders controllers.

Creating the Calculated Field in Employees Controller

Activate the Project Designer. In the Explorer, switch to Controllers tab. Let’s take a look at the command of Employees controller. Double-click on Employees / Commands / command1 node.

Take a look at the text of the command.

select
    "Employees"."EmployeeID" "EmployeeID"
    ,"Employees"."LastName" "LastName"
    ,"Employees"."FirstName" "FirstName"
    ,"Employees"."Title" "Title"
    ,"Employees"."TitleOfCourtesy" "TitleOfCourtesy"
    ,"Employees"."BirthDate" "BirthDate"
    ,"Employees"."HireDate" "HireDate"
    ,"Employees"."Address" "Address"
    ,"Employees"."City" "City"
    ,"Employees"."Region" "Region"
    ,"Employees"."PostalCode" "PostalCode"
    ,"Employees"."Country" "Country"
    ,"Employees"."HomePhone" "HomePhone"
    ,"Employees"."Extension" "Extension"
    ,"Employees"."Photo" "Photo"
    ,"Employees"."Notes" "Notes"
    ,"Employees"."ReportsTo" "ReportsTo"
    ,"ReportsTo"."LastName" "ReportsToLastName"
    ,"Employees"."PhotoPath" "PhotoPath"
from "dbo"."Employees" "Employees"
    left join "dbo"."Employees" "ReportsTo" on "Employees"."ReportsTo" = "ReportsTo"."EmployeeID"

You can see the alias “Employees” in front of the LastName and FirstName columns. You will need to use this alias when referring to columns of the Employees table in the SQL Formula of the calculated field.

Make sure not to modify and/or save the command – the code generator
will stop automatically updating the command if you do so.

Let’s create the new field. Right-click on Employees / Fields node and select New Field option.

Give this field the following settings:

"Employees"."LastName" + ', ' + "Employees"."FirstName"

Press OK to save the new field.

Now let’s change the data fields in the grid view. In the Project Explorer, double-click on Employees / Views / grid1 / LastName data field node.

Make the following change:

Press OK to save the data field. In the Project Explorer, right-click on Employees / Views / grid1 / FirstName data field node, and choose Delete option. Select OK to confirm the delete operation.

Creating the Calculated Field in Orders Controller

Before creating the “EmployeeFullName” field in Orders controller, we’ll need to find the alias of the Employees tables in the command text.

Double-click on Orders / Commands / command1 node.

Take a look at the text of the command.

select
    "Orders"."OrderID" "OrderID"
    ,"Orders"."CustomerID" "CustomerID"
    ,"Customer"."CompanyName" "CustomerCompanyName"
    ,"Orders"."EmployeeID" "EmployeeID"
    ,"Employee"."LastName" "EmployeeLastName"
    ,"Orders"."OrderDate" "OrderDate"
    ,"Orders"."RequiredDate" "RequiredDate"
    ,"Orders"."ShippedDate" "ShippedDate"
    ,"Orders"."ShipVia" "ShipVia"
    ,"ShipVia"."CompanyName" "ShipViaCompanyName"
    ,"Orders"."Freight" "Freight"
    ,"Orders"."ShipName" "ShipName"
    ,"Orders"."ShipAddress" "ShipAddress"
    ,"Orders"."ShipCity" "ShipCity"
    ,"Orders"."ShipRegion" "ShipRegion"
    ,"Orders"."ShipPostalCode" "ShipPostalCode"
    ,"Orders"."ShipCountry" "ShipCountry"
from "dbo"."Orders" "Orders"
    left join "dbo"."Customers" "Customer" on "Orders"."CustomerID" = "Customer"."CustomerID"
    left join "dbo"."Employees" "Employee" on "Orders"."EmployeeID" = "Employee"."EmployeeID"
    left join "dbo"."Shippers" "ShipVia" on "Orders"."ShipVia" = "ShipVia"."ShipperID"

You can see that this data controller refers to Employees using “Employee”, which is different than the Employees data controller reference of “Employees”. The SQL Formula of the calculated field will need to use the alias of the command text in Orders data controller.

Make sure not to save any changes to the command. Right-click on Orders / Fields node, and choose New Field.

Use the following settings for the new field:

"Employee"."LastName" + ', ' + "Employee"."FirstName"

Press OK to save the field.

Let’s change the alias of the EmployeeID data fields in each view so that Employee Full Name is displayed. In the Explorer, double-click on Orders / Fields / EmployeeID field node.

At the top, switch to Data Fields tab. For all three data fields, make the following change:

On the tool bar, press Browse to generate the web application. When it opens in your default web browser, navigate to Orders page. The EmployeeID field now shows the full name of each employee, instead of just the last name.

If you edit a record and activate the lookup for Employee Full Name, you will see that the grid view will show the Employee Full Name in the first column.

Selecting a record from the grid will insert the full name into the lookup field.

Navigate to the Employees page of the web application. If you activate inline edit mode on the grid view, you will not be able to edit the Employee Full Name field.

However, if you edit a record using form view, the original First Name and Last Name fields are still there.

Code On Time web applications automatically hide the foreign key field of a detail data view on master/detail page if the field is used for relationship filtering.

Here is the screen shot that shows a master/detail relationship between Suppliers and Products in the Northwind sample application. Data rows visible in the list of products are linked to the supplier New Orleans Cajun Delights. The application client library hides the column Products.SupplierID to avoid repeating the same value in each child data row.

Automatic hiding of the foreign key field involved in master/detail filter will maximize the real estate of the page. The side effect of this convenient feature is that it is not possible for a user to change the product supplier by editing a product record in the detail data view. 

A product supplier can be changed if there is a dedicated page of products. Users will have to navigate away to that page. It will also take some time to locate the record in a dedicated view.

Fortunately,  a simple SQL action can be implemented to allow changing the parent of a child record even if the foreign key field is hidden by the master/detail relationship filter.

First, we need to create an action confirmation data controller.

Start the Project Designer and activate the Controllers tab at the top of the designer home page. Select New | New Controller option on the action bar, enter the following properties, and click OK button to save the controller.

Activate the Controllers tab in Project Explorer and right-click the SupplierSelector / Fields node.

Enter the following properties and save the field by clicking OK button.

SelectFieldValue("Context_ProductName")

Create two more fields – SupplierCompanyName and SupplierID.

Properties of the field SupplierCompanyName.

SelectFieldValue("Context_SupplierCompanyName")

Properties of the field SupplierID.

The hierarchy of the SupplierSelector node shall look as follows.

The SupplierSelector data controller allows collecting a new Supplier ID.

Now we need to define a new SQL action in the Products data controller and use SupplierSelector as a modal confirmation form.

Right-click Products / Actions / ag1 (Grid) action group node on the Controllers tab in the Project Explorer and select the New Action option in the context menu.

Enter the following values in the action properties and click OK button to save the changes.

update Products
set SupplierID = @Parameters_SupplierID
where ProductID = @ProductID

Click Browse on the Project Designer toolbar, navigate to the Suppliers page when the application opens in your default web browser. Open the context menu of any product and select Change Supplier option in the context menu of the product data row.

The modal action confirmation will show up. Select a new supplier and click OK button to execute the action.

The product will disappear from the detail view. You will find it liked to the supplier record selected in the action confirmation form.

It is easy to access the values of fields entered in the action confirmation.

Action Change Supplier defines the following SQL script.

update Products
set SupplierID = @Parameters_SupplierID
where ProductID = @ProductID

Simply prefix the field name from the action confirmation data controller with “Parameters_”. The action implementation updates the SupplierID of selected product with the value selected in the modal confirmation.

If you are implementing custom business rules or shared business rules then you can access the values of action parameters as fields with the same “Parameters_” prefix.

Modal confirmation shows the name and current supplier of the selected product. Both fields are configured as read-only and use a similar Code Default expression. Here the expression from the SupplierSelector.SupplierCompanyName field.

SelectFieldValue("Context_SupplierCompanyName")

This code works with both C# and Visual Basic. The prefix “Context_” allows the action confirmation data controller business rules to access the field values of the controller that will be effected by the action. In our example, the name “Context_SupplierCompanyName” is referring to the SupplierCompanyName field of  Products data controller.