Thursday, November 29, 2012
Multi-Field Copy with an SQL Business Rule

It may be necessary for similar values to be copied from a lookup to a record.

When a simple copy operation is required, use the Copy property on the lookup field. For more complex copying operations, a business rule can be implemented. Let’s implement an SQL business rule to copy shipping information from a customer to an order in a sample Northwind web application.

Start the Project Designer. In the Project Explorer, switch to the Controllers tab and double-click on Orders / Fields / ShipName node.

ShipName field in the Orders controller in the Project Explorer.

Change the following properties.

Property Value
The value of the field is calculated by a business rule expression true
Context Fields CustomerID

Press OK to save the field. Right-click on Orders / Business Rules node and press New Business Rule.

New Business Rule context menu option in the Orders controller.

Give this rule the following properties.

Property Value
Type SQL
Command Name New|Calculate
Phase Execute
Script
if @CustomerID is not null
begin
     select 
        @ShipName = ContactName,
        @ShipAddress = Address,
        @ShipCity = City,
        @ShipRegion = Region,
        @ShipPostalCode = PostalCode,
        @ShipCountry = Country
     from Customers 
     where 
         CustomerID = @CustomerID
end

Press OK to save the business rule. On the toolbar, press Browse to generate the web application.

Navigate to the Customers page. Select a customer from the list, and a list of orders will appear underneath. On the action bar, press New Orders. The shipping fields will be prepopulated with the customer shipping information.

New Orders form on the Customers page prepopulated with customer shipping information.

Navigate to the Orders page. On the action bar, press New Orders. The ship fields will be blank.

New Orders form on the Orders page. Customer is not selected, and the shipping fields are blank.

Use the Customer Company Name lookup to select a customer. The ship fields will automatically be populated with the values from the customer.

A customer has been selected. Shipping information has been populated.