Business Rules / SQL

  Calculating Lookup Values

Business Rules / SQLPrint||
Calculating Lookup Values

The “Static” text mode property example copies the Unit Price from the selected product into the order detail, such as in the form below.

New Order Details screen with Unit Price copied from the Product.

Use SQL Business Rules if you need to perform calculations when copying the data.

Let’s apply a 15% premium on the Unit Price for every order detail.

First, we need to clear the Copy instructions. Start the Project Designer. In the Project Explorer, switch to the Controllers tab. Double-click on OrderDetails / Fields / ProductID field node.

ProductID field of OrderDetails controller in the Project Explorer.

Clear the following property:

Property New Value
Copy N/A

Press OK to save the field. In the Project Explorer, double-click on OrderDetails / Fields / UnitPrice field node.

UnitPrice field of OrderDetails controller in the Project Explorer.

Change the following properties:

Property New Value
The value of this field is calculated by a business rule expression. True
Context Fields ProductID

Press OK to save the field.

Next, let’s create the business rule to calculate the Unit Price. Right-click on OrderDetails / Business Rules node, and click on New Business Rule.

New Business Rule for OrderDetails controller in Project Explorer.

Use the following properties:

Property Value
Command Name Calculate
Type SQL
Phase Execute
Script
select @UnitPrice = UnitPrice * 1.15
from Products
where ProductID = @ProductID

Press OK to save the business rule. On the tool bar, press Browse to generate the application.

Navigate to the Order Details page, and create a new record. Select a product, and you will notice that the Unit Price has been marked up by 15%.

New Order Details screen with Unit Price calculated as 115% of Product UnitPrice.

Table of Contents