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.

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.