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 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 Company from the Main table and User, Sale Date, and Amount from Sales History. Group on User (double-click on that field and turn on the "Group on this field" and "Show count in group footer" options). Then, turn on the "Advanced layout" option and click on Edit to bring up the Advanced Report Designer. Create a new field and enter the following for the field's expression:

CalculateCommission(val(SALESHIST.DURATION))

(SALESHIST.DURATION is the name of the field that contains the sale amount and since GoldMine stores it as a character value, we need to use the built-in VAL() function to convert it to a number).

Enter 999,999.99 for the Format and then click on OK. To create a subtotal of commissions by user, copy the field and paste it, then move the copy to the group footer. Double-click on the copy, click on the Calculations button, and choose Sum from the list of calculations. Save and run the report.


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