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