Blog: Posts from April, 2012

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.

Application business requirements may specify actions demanding user-defined values. Field values of a selected data record are not sufficient to complete such actions.

Consider the example of implementing a custom business logic with “SQL” action. The example explains how to create a Standard Discount action that can be executed on any order in the Northwind sample application. The action will increment the discount of line items of a selected order by 1%.

The screen shot shows the application in action.

The properties of the action are defined as follows.

update "Order Details"
set Discount = 0
where 
    OrderID = @OrderID and Discount = 0.1
    
update "Order Details"
set Discount = cast(Discount as decimal(3,2)) + 0.01
where 
    OrderID = @OrderID and Discount < 0.1

If the action is selected then a simple confirmation is displayed.

A more flexible implementation of this action would allow a user to enter the exact discount that will apply to order line items. Let’s implement a confirmation that will allow capturing the discount and have it passed to the action implementation.

Start the application generator and click on the project name, click Design to activate the Project Designer. Select the Controllers tab in Designer and choose New | New  Controller option on the action bar.

Enter the following property values and click OK button.

Right-click the DiscountDialog / Fields node on the Controllers tab in Project Explorer and select the New Field option.

Enter the following property values and click OK button to save the new field.

Our “confirmation” data controller is ready.

Select Orders / Actions / ag1 (Grid) / a100 – SQL | Standard Discount action node on the Controllers tab in Project Explorer.

Change the action properties and click OK button.

update "Order Details"
set Discount = @Parameters_Discount
where 
    OrderID = @OrderID

Click Browse on the Project Designer toolbar to have the application generated. The home page of the app will open in the default web browser. Navigate to the Orders page and select Apply Discount option in the context menu of an order.

The modal form view will display. Enter a discount and click OK button to execute the action.

Any field values entered in the modal action parameter form are accessible in the “SQL” action script if your prefix the field name with “Parameters_”. Our script is referencing the entered Discount as @Parameters_Discout parameter.

If you are writing a custom business rules class or implementing shared business rules then you can reference the action parameter field by name “Parameters_Discount” as you would use any other field of a data controller.

The application client library displays a modal view if you define the confirmation property of the action as a collection of name/value arguments.

The library supports the following arguments. Only the “_controller” attribute is mandatory.

The DiscountDialog controller defines a single field. There are no commands, views, or actions.

The application framework treats data controllers that do not have a command as virtual controllers. Virtual Data Controllers are not based on a database table or view. The assumption is made that the controller has one data row only.

If the controller does not define its own views or actions then the application framework will complete the controller definition at runtime. This configuration is equivalent to the custom-defined controller shown in the picture.

The virtual data controller used for action confirmation is not different from any other data controller based on a real database table or view. You can implement business rules and custom actions that may in turn require other virtual controllers for confirmation.

Developers can apply data controller virtualization techniques to customize the data controller actions at runtime according to the custom workflow requirements. For example, the action properties Data and Confirmation may be stored in a dedicated database table and applied to data controllers based on user identity.