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.

Change the following properties.

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

Give this rule the following properties.

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.

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

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