TIP 3001

TOPIC: Performing Calculations and Functions in LeadCommander

LeadCommander can be used to perform a wide variety of calculations and functions, from calculating monthly payments to parsing data . These formulas are accessible via LeadCommander's Replace/Replace with calculated result feature. As is explained in the LeadCommander User's Guide, Replace itself is used to update a particular field (receptor) with a specified value for a found set of records in the database. By selecting the Replace with calculated result option, you can instruct LeadCommander to update the field with results of calculations and/or functions.

Calculations/functions can contain user-specified data, or be based upon data in fields. While this topic does not detail any specific examples, its focus is on helping you understand, and ultimately make efficient use of, this truly awesome feature.

Place your cursor in a field where you want the result of a calculation/function inserted. Choose Replace from the top button bar, then select the Replace with calculated result option. A calculation box presents itself.

The items you use in calculations/functions, from left to right, top to bottom, are :

1) The field list, in which you can select to use within the calculations and functions

2) Numbers and characters, and value and boolean operators

3) Calculations and functions

4) The calculation workspace

Using calculations and functions is really quite simple.

Calculations are basically simple math. For example, if you were to insert '12 * 12' in the workspace, then choose OK, and Replace, the result of '144' will be inserted in the receptor for all records in the current found set. Similarly you can base calculations upon field data. You insert a field into a calculation by locating it in the field list using the scroll bars, then double-clicking on it to select and insert it into the workspace.

Functions can be considered a combination between advanced math procedures and logic/theory, all of which are translated into English. Simply choose the function you want by locating it using the scroll bars, then double-clicking on it to select and insert it into the workspace.

Once selected, working with a calculation/function is simply a matter of providing the information to be used in the formula (parameters) enclosed in parentheses and separated by commas. You do this in the workspace by replacing each parameter indicator with the true parameter. Double-click on a parameter in a formula in the workspace to highlight it, then simply replace it with the true parameter.

For example, the formula:

Substitute ( text , search string , replace string )

means substitue data within a text string by searching for something and replacing it with whatever you want. Let's say the salutation field, Sal., contains Miss and you rather have it say Ms. You would adjust the formula above to read as follows:

Substitute (Sal., "Miss", "Ms.")

which in turn means, look in the Sal. field for the word Miss. If it's there, substitute the word Ms. instead. And, this will be done for all records in the current found set. (Note that free-form text in a calculation or function must be enclosed in quotes.)

Each calculation/function is presented in a consistent syntax:

Function (parameter 1, parameter 2,...)

Just remember to replace the parameters with the proper value, whether it's a field, free-from text, or even another calculation or function.

Useful examples are contained in the LeadCommander User's Guide, and in some of the on-line Hints &Tips.