Creating and Using User-Defined Functions

Note: this is an advanced topic designed for programmers.

Stonefield Query can use "user-defined" functions in various places, such as report fields. A user-defined function (UDF) is code that does something when it's called. You have to be familiar with dBase or FoxPro to write such a function. However, UDFs allow you to do just about anything you wish, including:

Create complex formulas and display the results in a report.
Ask the user for a value and use that value in the report.

To create a UDF, create a text file called REPPROCS.PRG in the directory where Stonefield Query's data files are stored. Edit this file using any text editor (not a word processor, such as Microsoft Word, which stores binary files, but an editor such as Notepad that stores text files). This file can contain as many UDFs as you wish. Each UDF should start with "FUNCTION" followed by the UDF name and any parameters (in parentheses), and end with "RETURN" or "RETURN <variable or expression>."

Here's an example of a UDF. This function determines the commission for a sale based on a complex formula: if the sale amount is over $1,000, the commission is 12%; between $500 and $1,000, it's 10%; between $100 and $500, it's 8%; and there's no commission for sales less than $100.

function CalculateCommission(SaleAmount)
do case
  case SaleAmount >= 1000
    Commission = SaleAmount * 0.12
  case SaleAmount >= 500 and SaleAmount < 1000
    Commission = SaleAmount * 0.10
  case SaleAmount >= 100 and SaleAmount < 500
    Commission = SaleAmount * 0.08
  otherwise
    Commission = 0
endcase
return Commission

Let's use this UDF in a commission report. Create a new report and add any fields you wish. Then, turn on the "Advanced layout" option and click Edit to bring up the Advanced Report Designer. Create a new field and enter the following for the field's expression:

CalculateCommission(SALES_ORDER_DETAIL.BVORDQTY * SALES_ORDER_DETAIL.BVUNITPRICE)

(SALES_ORDER_DETAIL.BVORDQTY is the name of the field that contains the order quantity and SALES_ORDER_DETAIL.BVUNITPRICE contains the unit price).

Enter 999,999.99 for the Format and then click OK. To create a subtotal of commissions by salesperson (assuming you've grouped on salesperson), copy the field and paste it, then move the copy to the group footer. Double-click the copy, choose the Calculate page, and choose Sum for the calculation type. Save and run the report.