Data Controllers / Actions / SQL

  Changing a Hidden Foreign Key Field

Table of Contents
Data Controllers / Actions / SQLPrint||
Changing a Hidden Foreign Key Field

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.

Master-detail relationship in the Northwind sample created with Code On Time web application generator

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.

Foreign key fields are not hidden in data views if they are not involved in master/detail filtering

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.

Property Value
Name SupplierSelector

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

Adding a new field to a data controller using Project Explorer

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

Property Value
Name ProductName
Type String
Code Default
Label Product
Values of this field cannot be edited Yes

Create two more fields – SupplierCompanyName and SupplierID.

Properties of the field SupplierCompanyName.

Property Value
Name SupplierCompanyName
Type String
Code Default
Label Supplier
Values of this field cannot be edited Yes

Properties of the field SupplierID.

Property Value
Name SupplierID
Type String
Label New Supplier
Items Style Lookup
Items Data Controller Suppliers
Data Value Field SupplierID
Data Text Field CompanyName

The hierarchy of the SupplierSelector node shall look as follows.

The hierarchy of the 'SupplierSelector' data controller node in Project Explorer

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.

Adding a new action to a group with 'Grid' scope in a web app created with Code On Time web application generator

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

Property Value
Command Name SQL
Header Text Change Supplier
update Products
set SupplierID = @Parameters_SupplierID
where ProductID = @ProductID

_title=Select a New Product Supplier

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.

Activating a custom 'SQL' action in the context menu of a data row in Code On Time web application

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

Modal action confirmation forms allow easy collection of parameters from the application end users

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

The product data row is now linked to another supplier selected in the modal 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.


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.