How to create if-then-else formulas

If-Then-Else formulas are conditional formulas: if a condition is met, then a certain consequence, an action, takes place. If the condition is not met, a different action takes place. If-Then-Else formulas are created using the If-Then-Else operator. Search for If- Then-Else in Seagate Crystal Reports online Help.

Example	● If a sales representative has already earned the maximum
	allowable bonus, print the amount of the maximum bonus
	allowed; if he has not yet earned the maximum, calculate
	the bonus actually earned and print it.
	● If the value in the title field is “Mr.”, print “Dear Mr.” as the
	beginning of the salutation; if it is not “Mr.”, print “Dear
	Ms.” as the salutation.
	● If the quantity on hand of a part is less than or equal to the
	reorder amount, reorder according to the reorder
	instructions; if the quantity is greater than the reorder
	amount, do nothing.

These are just a few of the kinds of conditional formulas you can create using the If-Then-Else operator.

When using the If-Then-Else operator, remember that there must be three separate parts to any If-Then-Else formula:

1. If

This portion sets the condition.

2. Then

This portion sets the action that takes place when the If condition is met.

3. Else

This portion sets the action that takes place when the If condition is not met.

NOTE: The data types (text, number, currency, date, time, dateTime, or Boolean) for the Then and Else parts of your formula must be the same.

346	Seagate Crystal Reports User’s Guide

Thus, if the action that takes place if the condition is met (Then) is to print a text string, the action that takes place if the condition is not met (Else) must also be to print a text string, even if that text string is empty. For example:

If {file.FIELD} = 5 Then “Text String”

Else

“Another Text String”

OR

If {file.FIELD} = 5 Then 0

Else 1

NOTE: You can not create If-Then-Else formulas that use a Time or dateTime data type as a Then action and a null or empty Time or dateTime as an Else action because there are no null or empty values for those data types. You can print Time or dateTime values conditionally. See How to print Time or dateTime values

conditionally, Page 347.

How to print Time or dateTime values conditionally

If you want to print Time or dateTime values conditionally (print the field if the condition is met, print the field in another color if the condition is met, etc.), you can not do it using an If-Then-Else formula because there is no such thing as an empty or null value for the Time or dateTime data type. Instead, you insert the field itself in your report and set its Suppress property conditionally.

Place the dateTime field where you want it to print on your report.

Select the field and click the OBJECT PROPERTIES button on the supplementary toolbar. The Format Editor appears.

! Click the Common Tab to activate it.

" Click the Formula button to the right of the Suppress property.

Advanced Formulas

347

#Type the following formula in to the Formula Editor when it appears.

{employee.LAST NAME} <> “Fuller”

Now when you run the report, the program will print the dateTime field whenever the employee last name is Fuller and it will suppress the field when the last name is anything but Fuller.

How to create multi-condition if-then-else formulas

You can create powerful multi-condition formulas using the If-

Then-Else operator.

Multi-condition and nested If-Then-Else formulas can be set up in this general pattern:

●If the X (first) condition is met, Then, go to the Y (second) condition.

●If the Y condition is met, Then perform the Y action.

●If the Y condition is not met (Else), perform the Y alternative.

●If the X condition is not met (Else), perform the X alternative. Thus:

If {file.FIELD1} = “X” Then

If {file.FIELD2} = “Y” Then “Y Action”

Else

“Y Alternative”

Else

“X Alternative”

See How to create if-then-else formulas, Page 346.

This formula checks the field FIELD1 first.

●If the value of that field is “X,” the FIELD2 field is checked.

¾If the value of that field is “Y,” “Y Action” is printed.

¾If the value of FIELD2 is not “Y,” “Y Alternative” is printed.

348	Seagate Crystal Reports User’s Guide

● If the value of FIELD1 is not “X,” “X Alternative” is printed.

While multi-condition formulas look complex at first, after you have worked through one or two you will find that they are not as intimidating as they seem, especially given the work they perform.

How to format text with formulas

You can use formulas to format text. For instance, functions are included with the program for removing unnecessary leading or trailing spaces from text stings as well as converting text entirely to upper or lower case.

Example TrimLeft(“ Al/4520/B12”)

«Returns “A1/4520/B12”.»

TrimRight(“A1/4250/B12 “)

«Returns “A1/4250/B12”.»

LowerCase(“Ronald Black”)

«Returns “ronald black”.»

Related Topics

Formatting, Page 231

How to use variables in formulas

Variables can be used to solve many formula problems, but they have two primary uses:

1.streamlining formulas, and

2.expanding formula capabilities.

Unlike a constant value which is fixed and unchanging, a variable can be repeatedly assigned different values. You assign a value to a variable and the variable maintains the value until you later assign a new value. Then the variable maintains the new value until you later assign a newer value, etc.

Advanced Formulas

349

Using variables

to streamline formulas

Using variables, you can write formulas much more efficiently than you can without them. For example, to evaluate the {customer.FAX} field to determine if the area code is for Washington state (206, 360, 509) or British Columbia, Canada (604, 250), without the benefit of variables, you must write a formula similar to the following:

If {customer.FAX}[1 to 3] = “604” or {customer.FAX}[1 to 3] = “250”

Then “BC”

Else

If {customer.FAX}[1 to 3] = “206” or {customer.FAX}[1 to 3] = “509” or {customer.FAX}[1 to 3] = “360” Then

“WA”

Else

“”

See How to create if-then-else formulas, Page 346, and search for

Subscript in Seagate Crystal Reports online Help.

You have to write out the instructions for extracting the area code from the telephone number field ({customer.FAX} [1 to 3]) every time you want the formula to use the area code from the current record.

By using a variable (for example, AreaCode), you write those instructions one time. Using those instructions, the program automatically extracts the area code from the {customer.FAX} field, and stores it in the variable each time it reads a new record. You simply reference the variable AreaCode whenever you want to use the area code from the current record in your formula. Here's an example of the formula using a variable:

StringVar AreaCode:={customer.FAX}[1 to 3]; If AreaCode = “604” or AreaCode = “250” Then

“BC”

Else If AreaCode = “206” or AreaCode = “509” Then

“WA” Else

“”

350	Seagate Crystal Reports User’s Guide

Using variables

to expand formula capabilities

Not only does the streamlined version take less time to write, but it takes less time to process as well, so your report prints more quickly.

Besides their impact on streamlining formulas, variables allow you to expand your formula writing capabilities. Before discussing the specifics of using variables in formulas, it is important to understand some things about the way the Formula Editor reads formulas.

SPECIAL REQUIREMENTS FOR USING VARIABLES IN FORMULAS

Through the discussion thus far, formula elements that have been narrowly defined:

●a given operator only works in certain situations and with certain kinds of text and/or data,

●a function only works with a specific number of arguments, and each argument must be a specific data type, and

●If-Then-Else formulas work only if the data type of the Else part of the formula matches the data type of the Then part.

Such narrow definition allows you to create formulas, in many cases, simply by filling in the blanks, with the formula checker pointing out any mistakes you make.

Variables, however, are not narrowly defined. They are extremely flexible; you make them what you want them to be. You create them at will and include them in formulas as needed.

Because of this flexibility, it is necessary for you to define the variables before using them so that the program:

●is aware of them,

●understands how you intend to use them, and

●can set aside and set up the memory space they require.

You also need to assign values to the variables so the program knows what values they are to return.

Seagate Crystal Reports knows only what you tell it about the variables. The fail-safe formula-checker routines that work automatically with the other formula elements work with variables only after you define them.

Advanced Formulas

351

Declaring the variable

Naming the variable

Variable data types

To use a variable in a formula, you must do three things:

1.declare the variable,

2.set the value of the variable, and

3.enter the variable in the formula.

Seagate Crystal Reports requires you to declare all variables prior to using them. When you declare a variable, you tell the program:

●the name you intend to use for the variable, and

●the type of data you want the variable to hold.

The program uses this information to set aside a piece of memory for receiving and storing the values that are assigned to the variable.

NOTE: If you declare a variable with the same name and data type in two or more formulas, the formulas share the same variable. Thus, if one formula sets the value of the variable, the variable in the second (and additional formulas) reflects the change.

You can name the variable anything you wish with the following qualifications:

●the variable name must not exceed 254 characters, and

●it can not have the same name as an operator or built-in function. Search for Functions and Operators and Variables in Seagate Crystal Reports online Help.

NOTE: As a general rule, you are probably better off if you keep the variable name short, easy to remember, and unique (not so close to the name of another variable as to cause confusion).

The data type of a variable determines the type of data that can be stored as a value in that variable. You can create a variable with one of seven data types:

1.number (100000)

2.currency ($30,000.00)

3.Boolean (TRUE)

4.date (January 1, 1996)

352	Seagate Crystal Reports User’s Guide