Formula 14

Operators/Functions Used

If-Then-Else, Length, TrimLeft, Less than or equal (<+), Parentheses ().

Formula Purpose

Five and a half years earlier, a company had changed from a lifetime warranty for its products to a five year warranty. The first products that had been manufactured with the shorter warranty were now out of warranty, but the repair department was slow to adapt. It continued to repair all products for free, as if they were all covered by the original lifetime warranty.

All products manufactured under the new, shorter warranty had issued eight character serial numbers instead of the five, six, or seven character serial numbers that had been issued to lifetime-warranted products.

To begin controlling the situation, management has called for a column on its repair report that identifies products serviced as either lifetime warranty or five year warranty products.

Formula

If Length(TrimLeft({file.SERIAL#}))<= 7 Then 

    "Lifetime Warranty" 

Else 

    "5 Year Warranty"

Result

Serial Number # Characters Flag

BP10001

7

"Lifetime Warranty"

BP1000

6

"Lifetime Warranty"

BP999

5

"Lifetime Warranty"

BP100001

8

"5 Year Warranty"

Explanation

The formula uses the If-Then-Else operator to say, "If the serial number is 7 characters long or less (Then), print "Lifetime Warranty", otherwise (Else) print "5 Year Warranty".
TrimLeft({file.SERIAL#}) removes all of the blank spaces stored to the left of the actual serial number in the right-justified {file.SERIAL#} field. The formula uses TrimLeft to eliminate spaces because Length counts spaces as characters if they are present.
Length counts the characters in the actual serial number.
The formula uses the Less than or equal to operator (<=) to make certain that the serial number is 7 characters long (or less).
If the serial number is less than or equal to 7 characters, the If part of the expression is satisfied, thus triggering the Then consequence.
If the If part of the expression is satisfied, the formula prints the text string "Lifetime Warranty".
If the serial number is greater than 7 characters, the If part of the expression is not satisfied, thus triggering the Else consequence.
If the If part of the expression is not satisfied, the formula prints the text string "5 Year Warranty".