Formula 15

Operators/Functions Used

Nested If-Then-Else operators, Not equal (<>), Boolean operator (And), Equal (=), Concatenate (+), Parentheses ().

Formula Purpose

The director of a non-profit health care facility wants to automate the salutation in a letter he is sending to his staff. The staff consists of men and women, and professional and non-professional employees. He wants to be certain that all doctors on staff (M.D., Ph.D., and D.D.S.) are given the form of address "Dr." and all non-doctors are given the form of address appropriate to their gender.

Formula

If {file.DEGREE} <> "Ph.D." And

{file.DEGREE} <>"M.D." And

{file.DEGREE} <>"D.D.S" Then

    If {file.SEX} = "M" Then

        "Dear Mr. " + {file.LNAME}

    Else

        "Dear Ms. " + {file.LNAME}

Else

    "Dear Dr. " + {file.LNAME}

Result

Degree	Gender	Last Name	Salutation
B.A.	F	Jones	"Dear Ms. Jones"
Ph.D.	F	Smith	"Dear Dr. Smith"
M.D.	M	Jackson	"Dear Dr. Jackson"
M.S.	M	Miller	"Dear Mr. Miller"
D.D.S.	F	Johnson	"Dear Dr. Johnson"

Explanation

• This formula uses nested If-Then-Else operators.
• The first If-Then-Else expression begins with the first If and continues to Else "Dear Dr. " + {file.LNAME} at the end of the formula. It says, "If the degree listed is not a doctoral degree (Then), follow the Then consequences, which contains another If-Then-Else expression. If the degree listed is a doctoral degree (Else), print a doctoral salutation.
• The If part of the expression tests for three different conditions.
• Condition A uses the Not equal operator <> to make certain that the employee's degree is not Ph.D.
• Condition B uses the Not equal operator <> to make certain that the employee's degree is not M.D.
• Condition C uses the Not equal operator <> to make certain that the employee's degree is not D.D.S.
• The two And operators indicate that all three conditions must be met to satisfy the If part of the expression.
• If all three conditions are met, the If part of the expression is satisfied, thus triggering the Then consequences.
• If any one of the three conditions is not met (or two or all three), the If part of the expression is not satisfied, thus triggering the Else consequence.
• The second If-Then-Else statement begins with if {file.GENDER} = and ends with Else "Dear Ms. " + {file.LNAME}. It says, "If the employee is male (then), print a male salutation. If the employee is anything but male (Else), print a female salutation. This If-Then-Else expression determines what actually happens if the If part of the first expression is satisfied.
• If the gender is male (determined by using the Equal operator =), the If part of the second If-Then-Else expression is satisfied, thus triggering the Then consequence (printing the salutation "Dear Mr. " + {file.LNAME}.
• If the gender is not male, the If part of the second If-Then-Else expression is not satisfied, thus triggering the Else consequence (printing the salutation "Dear Ms. " + {file.LNAME}.
• · The Concatenate operator (+) connects the "Dear x" part of the salutation with the last name (as stored in the {file.LNAME} field).

Related topics

Formulas In Action