Formula 5

Operators/Functions Used

Nested formulas, If-Then-Else, Subtract (-), Not equal to (<>), Less than (<), Concatenate (+), ToText, To dollar ($), Negate (-()), Parentheses ()

Formula Purpose

A company wants to stimulate new credit sales for those customers with open credit limits and to make customers who are over their limits aware of their over-limit balances. To do this the company wants to include a brief "PS" to its already customized (company name, contact name, etc.) sales letter to each customer.

Formula

If {file.CREDIT LIMIT} - {file.BALANCE} <> 0 Then

    If {file.CREDIT LIMIT} - {file.BALANCE} <0 Then

        "Your account is currently $" + 

        ToText($ (-{file.CREDIT LIMIT} 

        - {file.BALANCE})) 

        + " over limit. Please contact us if you would 

        like to discuss an increase in your credit limit." 

    Else 

        "Your account has $" + ToText($({file.CREDIT 
LIMIT}

        -{file.BALANCE})) + " available credit. 

        You can order today with no additional paperwork!" 

Else 

    ""

Result

CreditLimit	Balance	Result
$5000	$2250	"Your account has $2750.00 available credit. You can order today with no additional paperwork!"
$3000	$3457	"Your account is currently $457.00 over limit. Please contact us if you would like to discuss an increase in your credit line."
$7500	$7500	«no message»

Explanation

• The formula uses two If-Then-Else operators, one nested inside the other.
• The first begins with the first word If and does not end until the Else at the very end of the expression. This expression says, essentially, "If the credit limit less the balance is less than zero, then print the message based on the If-Then-Else expression that follows (the expression inside the parentheses). If the credit limit less the balance equals zero, print nothing. Think of this first If-Then-Else expression in this way: If {file.CREDIT LIMIT} - {file.BALANCE} <> 0 then (do what is in the parentheses ) Else "".
• The second If-Then-Else expression begins "If {file.CREDIT LIMIT}" and ends after the word "paperwork!". This expression says: "If the credit limit less the balance is less than zero, then print the over limit message, otherwise (in those cases where the result is greater than zero) print the available credit message.
• The Then expression ToText($(- {file.CREDIT LIMIT} - {file.BALANCE})) means:
  • _        first use the Subtract operator (-) to subtract {file.BALANCE} from {file.CREDIT LIMIT}.
• Since this part of the expression will only be activated if the result is a negative number (<0), the formula uses the Negate operator (-()) to multiply the result by -1 to return a positive number.
• The To Dollar operator ($) assures that the result will be printed in a dollar and cents format with two decimal places.
• The ToText function takes the resulting number and converts it to text characters so it can be used in the over limit message.
• The Else expression ToText($({file.CREDIT LIMIT}-{file.BALANCE})) differs only slightly from the Then expression above. This expression does not use the Negate operator (-()) because this expression {file.CREDIT LIMIT}-{file.BALANCE} will be used only in those cases where the result is a positive number (>0).
• The Concatenate operator (+) joins the text strings (enclosed in quotation marks) with the number (converted to text using the ToText function) to produce the appropriate message (the Then message [over limit] or the Else message [available credit]).
• Many sets of parentheses are used to control the order of calculation of this formula.
• The characters "" at the very end of the formula are the representation of an empty string. This means print nothing.

Comments

Note the leading space in the string " over limit." Note too, the leading space in the string
" available credit." These spaces are purposely entered into the formula so a space occurs between the dollar figure and the words in each message.

Related topics

Formulas In Action