Creating and Using User-Defined Functions

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 a programmer 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 returns a descriptive value for a Call Priority value:

function GetPriority(PriorityCode)
PriorityDesc = ''
do case
  case PriorityCode = 1
    PriorityDesc = 'Urgent'
  case PriorityCode = 2
    PriorityDesc = 'Important'
  case PriorityCode = 3
    PriorityDesc = 'Normal'
  otherwise
    PriorityDesc = 'Unimportant'
endcase
return PriorityDesc

To use this UDF in a report, create a new report and add the desired fields, including the Call Priority field. 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:

GetPriority(CALLLOG.PRIORITY)

(CALLLOG.PRIORITY is the real name of the Call Priority field.)

Save and run the report.