This is an advanced topic intended for programmers.

Stonefield Query can use "custom" functions in various places, such as report fields. A custom function is code that does something when it's called. Custom functions 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.

There are a couple of ways you can create custom functions:

  • 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 functions as you wish. Each function should start with "FUNCTION" followed by the function name and any parameters (in parentheses), and end with "RETURN" or "RETURN variable or expression."

  • Create a text file called FunctionName.PRG in the Functions subdirectory of the folder where Stonefield Query's data files are stored, where FunctionName is the name of the function. Edit this file using any text editor such as Notepad. Unlike REPPROCS.PRG, this file can only contain a single function and should not include a "FUNCTION" statement. It can, however, include an LPARAMETERS statement if it accepts any parameters and should 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(tcPriorityCode)
   local lcPriorityDesc

   lcPriorityDesc = ''
   tcPriorityCode = ALLTRIM(tcPriorityCode)

   do case
      case tcPriorityCode == '1'
         lcPriorityDesc = 'Urgent'
      case tcPriorityCode == '2'
         lcPriorityDesc = 'Important'
      case tcPriorityCode == '3'
         lcPriorityDesc = 'Normal'
      otherwise
         lcPriorityDesc = 'Unimportant'
    endcase
return lcPriorityDesc

To use this UDF in a Call report, create a new report and add the desired fields, including the "Priority Code" field. Click the Formula button in Step 2 to display the Formula Editor. Type a name for the formula, such as "Priority Description" and enter the following for the formula's expression:

GetPriority(CALLLOG.PRIORITY)

CALLLOG.PRIORITY is the name of the field in this example that contains the "Priority Code".

Click OK to close the Formula Editor. The formula appears in the Available Fields list. Add it to the Selected list. When you run the report, it shows the description for a Priority Code.


© Stonefield Software Inc., 2018 • Updated: 06/05/18
Comment or report problem with topic