$Paste the formula into the Formula text box using the Paste command (Ctrl-V).

Add additional formula elements as needed.

How to fine tune record selection formulas

Uppercase/

lowercase inconsistencies

You may have run into a situation in which you create a record selection formula (using the RECORD command on the Report|Edit Selection Formula menu), and, while header and footer information prints on your report, no detail information appears. The problem is a selection formula that is rejecting all records, and this usually occurs because of some inadvertent error in the creation of the selection formula.

There are several things that you can look for as the cause of your problem in the selection formula:

●Uppercase/lowercase inconsistencies, Page 366

●Number in text object not in quotes, Page 367

●Unwanted spaces appear in selection formula, Page 368

Record selection formulas are case sensitive. That is, “Bob” matches only with “Bob”. It does not match with “bob”, “BOB”, “BoB”, “bOB”, “boB”, or “BOb”. Thus, if your selection formula is set to include only those records with “BOB” in the {customer.CONTACT FIRST NAME} field, but all the entries in the {customer.CONTACT FIRST NAME} field are mixed case (“Bob”, for example), the selection formula will find no matches and thus not print any details for the report.

You can solve this problem by using the UpperCase (str) or LowerCase (str) functions in your selection formula to convert field data to a consistent case before the program begins its selection. For example, if you were using this formula:

{customer.CONTACT FIRST NAME} = “BOB”

you can change the formula to this:

UpperCase({customer.CONTACT FIRST NAME}) = “BOB”

366	Seagate Crystal Reports User’s Guide

Number in text

object not in quotes

This last formula first converts the value of the {customer.CONTACT FIRST NAME} field to upper case characters and then checks to see if the value in that field is equal to “BOB”. Using this formula, any instance of the three letters “b” “o” “b” will be a match, regardless of case, because the case will be converted first to uppercase for consistency.

You could use the LowerCase function in a similar manner to match with “bob”.

Check your selection formula closely and make sure you have the case correct on any text you are trying to match. If in doubt, use the UpperCase (or LowerCase) function to assure consistency and proper matches.

Another formula that does much the same as that above is:

“BOB” in UpperCase({customer.CONTACT FIRST

NAME})

When a number is stored in a text object, it is text even though it looks like a number. Whenever a value from a text object is used in a record selection formula, you must surround the value with single or double quotation marks. If your selection formula is set to look for a number in a text object and you fail to surround the number with quotes in the selection formula, the selection formula will find no matches and thus not print any details for the report.

For example, this selection formula:

{customer.CUSTOMER ID} = 12345

will not find any matches, even though the value 12345 appears in the {customer.CUSTOMER ID} field of many records.

To select records with the characters 12345 in a string field, you must put quotes around the characters you are attempting to match, like this:

{customer.CUSTOMER ID} = “12345”

Check your selection formula closely, and make sure that any numbers you are attempting to match in a text object are surrounded by single or double quotation marks.

Advanced Formulas

367

Unwanted

spaces appear in selection formula

Troubleshooting

record selection formulas

Spaces are characters, and when you include spaces in the search key of a record selection formula, the formula looks for records with the exact match in the selected field, spaces and all. For example, the following formula:

“Mr . “ in {customer.TITLE}

will not find any matches with the form of address “Mr.” because there is an extra space in the search key between the letter “r” and the period. Likewise, “Ph. D” will not match “Ph.D”.

Check your selection formula closely, and make sure that the spaces in the selection formula match the spaces in the fields you are trying to match.

To troubleshoot your selection formula, you will first begin making sure that all of the fields referenced in the selection formula are placed on your report, then delete the selection formula, and test it as you rebuild it, step by step.

Write down the record selection formula on paper. You will use the written copy of the selection formula to help you reconstruct the selection formula a step at a time.

Remove the record selection formula from your report by deleting the formula from the Formula text box in the Formula Editor.

! Click Accept when finished.

"Make certain that all fields referenced in the record selection formula (the selectors) are on the report physically and not hidden.

For example, if one of the selectors is:

{customer.POSTAL CODE} > “80000”

but the {customer.POSTAL CODE} field is not used on your report (as in the case of a sales report that uses the zip code to define territories but does not include the postal code in the report data), then insert the {customer.POSTAL CODE} field in an obvious place on the report.

Or, if one of the fields referenced in the selection formula is on the report but hidden, unhide it by toggling the Hide when Printing option off in the Format Editor for that field.

368	Seagate Crystal Reports User’s Guide

#Print the report and make certain that the data in those fields referenced by the selection formula prints satisfactorily. Make certain that all the data prints. For example, if there are x total records in the database you should have x records printing for each of the referenced fields. This establishes a baseline against which you can compare the results of printing with the selection formula.

$When you are sure you are getting satisfactory results without the selection formula, enter the selection formula using only one of the selectors.

For example, if you want to use this as your final selection formula:

{customer.POSTAL CODE} > “80000” and {customer.CONTACT LAST NAME}[1] = “C” and {customer.LAST YEAR'S SALES} >= 5000

this formula will select all of those records that show a Postal code greater than 80000, a value in the {customer.CONTACT LAST NAME} field beginning with “C”, and a value in the {customer.LAST YEAR'S SALES} field greater than or equal to 5000.

You might start with this as your first test selection formula:

{customer.LAST YEAR'S SALES} > “80000”

Print the report and evaluate the data that prints with only one selector activated. In your example, evaluate the data in the {customer.POSTAL CODE} field. Does the field show only ZIP codes greater than 80000?

●If it does, then you know that this part of the selection formula is working.

●If it does not, then troubleshoot this part of the selection formula.

%Once the selection formula with one selector activated is working properly, add a second selector. In your example, the new selection formula might look like this:

{customer.POSTAL CODE} > “80000” and {customer.CONTACT LAST NAME}[1] = “C”

Advanced Formulas

369