Monster Media 1993 #2

home *** CD-ROM | disk | FTP | other *** search

/ Monster Media 1993 #2 / Image.iso / database / p4w_all.zip / TI1437.ASC < prev next >

Wrap

Text File | 1993-04-14 | 12KB | 529 lines

PRODUCT : Paradox for Windows NUMBER : 1437 VERSION : 1.0 OS : WIN DATE : April 14, 1993 PAGE : 1/8 TITLE : How to Use IIF (Immediate If) in Calculated Fields Intended Audience All Paradox for Windows users Prerequisites Familiarity with how to create calculated fields Chapter 12, User's Guide, "Using Calculated Fields" Chapter 3, ObjectPAL Reference Guide, "iif" Purpose of TI This Technical Information sheet provides examples of using the ObjectPAL Immediate If keyword, iif(), in calculated fields of forms or reports. Although the examples do not cover all possible uses, they will provide you with techniques which you could apply to your own applications. When designing forms or reports, it is sometimes desirable to place calculated fields containing conditional statements. For example, if the state is California multiply the Amount field by 10, otherwise multiply the Amount field by 5. One of the possible applications of the iif() keyword is to use it to provide conditional functionality in calculated fields. This document provides detailed examples of how to use the iif() keyword in calculated fields of forms and reports. Several of the examples, reference other ObjectPAL methods and procedures in addition to the iif() keyword. For a complete reference of ObjectPAL methods, procedures, and keywords, refer to the ObjectPAL Reference manual. The syntax for the iif() keyword is: iif(Condition, ValueIfTrue, ValueIfFalse) where Condition is any expression that evaluates to a logical value of True or False; ValueIfTrue is the value returned if Condition evaluates to True; and ValueIfFalse is the value returned if Condition evaluates to False. PRODUCT : Paradox for Windows NUMBER : 1437 VERSION : 1.0 OS : WIN DATE : April 14, 1993 PAGE : 2/8 TITLE : How to Use IIF (Immediate If) in Calculated Fields The first eight examples take you through a credit card payment scenario using iif() keyword. The sample Credit database has the following structure and records: Credit │ Name │ Balance │ Limit │ Due Date │ Gender ───────┼──────┼──────────┼──────────┼──────────┼─────── │ Bob │ 1,100.00 │ 1,100.00 │ 12/2/91 │ M │ Judy │ 250.00 │ 3,000.00 │ 10/20/91 │ F │ Mark │ 12.00 │ 2,000.00 │ 6/12/92 │ m │ Pam │ 0.00 │ 2,500.00 │ │ f NOTE: The Balance and Limit fields have a field type of $. Example 1 If a person has not yet reached their credit limit, indicate that they still have credit available, otherwise indicate that no credit remains: IIF([Credit.Balance]<[Credit.Limit], "Credit Available", "No Credit Available") NOTE: The above expression must be entered on one line. Explanation of Example 1 If the Balance is less than Limit, return "Credit Available", otherwise return "No Credit Available". Result of Example 1 Name IIF() Result ─────── ─────────────────── Bob No Credit Available Judy Credit Available Mark Credit Available Pam Credit Available Notice that Bob is the only person who has used up his full credit limit (Balance = 1100.00, Limit = 1100.00). PRODUCT : Paradox for Windows NUMBER : 1437 VERSION : 1.0 OS : WIN DATE : April 14, 1993 PAGE : 3/8 TITLE : How to Use IIF (Immediate If) in Calculated Fields Example 2 If there is a credit card balance, show a minimum payment due (2% of the balance). Otherwise, show zero since there is no balance due: iif([Credit.Balance]>0, [Credit.Balance]*.02, 0) Explanation of Example 2 If the Balance is greater than zero, return 2% of the Balance value, otherwise return zero. Result of Example 2 Name IIF() Result ────── ──────────── Bob 22.00 Judy 5.00 Mark .24 Pam 0 Example 3 Paying 2% on any balance means that Mark would pay $0.24 and Judy would pay $5.00. In order to assure that a minimum payment of $20 is received, the previous iif() expression could be modified like this: iif([Credit.Balance]*.02<20, 20, [Credit.Balance]*.02) Explanation of Example 3 If 2% of the balance is less than $20, require a payment of $20. Otherwise, since the balance is high enough such that a 2% payment is $20 or greater, require a 2% payment. PRODUCT : Paradox for Windows NUMBER : 1437 VERSION : 1.0 OS : WIN DATE : April 14, 1993 PAGE : 4/8 TITLE : How to Use IIF (Immediate If) in Calculated Fields Result of Example 3 Name IIF() Result ─────── ──────────── Bob 22.00 Judy 20 Mark 20 Pam 20 Judy and Mark now pay a minimum of $20. NOTE: To format the output so that it looks like currency values, refer to Example 5 in this Technical Information sheet. Example 4 But what about Mark? He has a balance of $12.00. The $20 minimum payment means he is expected to pay more than he owes. And Pam has a zero balance--she does not owe anything! The previous iif() expression could be further modified so that anyone with a balance less than $20 pays it in full: iif([Credit.Balance]<20,[Credit.Balance], iif([Credit.Balance]*.02<20, 20, [Credit.Balance]*.02)) NOTE: The above expression must be entered on one line. Notice how you can nest iif() keywords. Explanation of Example 4 This nested iif() expression states that if the balance is less than $20, the full balance is due. Otherwise, the balance is $20 or greater. So, if the balance is $20 or greater and 2% of this balance is less than $20, $20 is due. Otherwise, require a 2% payment of the balance (since this 2% payment will be at least $20). PRODUCT : Paradox for Windows NUMBER : 1437 VERSION : 1.0 OS : WIN DATE : April 14, 1993 PAGE : 5/8 TITLE : How to Use IIF (Immediate If) in Calculated Fields Result of Example 4 Name IIF() Result ─────── ──────────── Bob 22.00 Judy 20 Mark 12.00 Pam 0.00 Now, Mark and Pam only pay what they owe. Example 5: Finally, to format the output so that it looks like currency, embed the iif() keyword within a format() procedure. format("W10.2, E$", iif([Credit.Balance]<20, [Credit.Balance], iif([Credit.Balance]*.02<20, 20, [Credit.Balance]*.02))) NOTE: The above expression must be entered on one line. The format() procedure allows you to format a string. Its syntax is format(FormatSpec, String). For additional information on the format() procedure, refer to Chapter 4 of the ObjectPAL Reference manual. Explanation of Example 5 Take the resulting value of the iif() keyword and format it as 10 digits with 2 decimal places, and use a floating dollar sign. Result of Example 5 Name IIF() Result ─────── ──────────── Bob $22.00 Judy $20.00 Mark $12.00 Pam $0.00 PRODUCT : Paradox for Windows NUMBER : 1437 VERSION : 1.0 OS : WIN DATE : April 14, 1993 PAGE : 6/8 TITLE : How to Use IIF (Immediate If) in Calculated Fields Example 6 Suppose you wanted to display the full gender of a cardholder based on the Gender field (which contains "M" for male and "F" for female). In order to display "Male" or "Female" in a report or form, you could use the following: iif([Credit.Gender]="M", "Male", "Female") Explanation of Example 6 If Gender is "M", return "Male", otherwise return "Female." Result of Example 6 Name Gender IIF() Result ────── ─────── ──────────── Bob M Male Judy F Female Mark m Female <----- Pam f Female Example 7 Notice that Mark's gender displayed as Female even though an "m" was entered. Since string comparisons are case-sensitive, "m" is not equivalent to "M" and, therefore, returns "Female". To assure a proper comparison when Gender values are lower case, use: iif(upper([Credit.Gender])="M", "Male", "Female") The upper() method allows you to change a string to upper-case. Its syntax is upper(Expression). Explanation of Example 7 Convert the Gender value to upper-case. Then, if it equals "M", return "Male", otherwise return "Female". PRODUCT : Paradox for Windows NUMBER : 1437 VERSION : 1.0 OS : WIN DATE : April 14, 1993 PAGE : 7/8 TITLE : How to Use IIF (Immediate If) in Calculated Fields Result of Example 7 Gender IIF() Result ─────── ──────────── M Male F Female m Male f Female Since the upper() method converts "m" to "M", "Male" is returned for both "M" and "m." ┌──────────────────────────────────────────────────────────────┐ │ The next example no longer deals with the Credit table │ └──────────────────────────────────────────────────────────────┘ Example 8 Suppose you had an employee table which had a DOB field for Date Of Birth. You could use the following expression to see if today was their birthday: iif(month([Employee.DOB])=month(today()) AND day([Employee.DOB])=day(today()), "Happy Birthday!", "") NOTE: The above expression must be entered on one line. The month() method returns the numeric month value of a date. Its syntax is month(Date). The day() method returns the numeric day value of a date. Its syntax is day(Date). The today() procedure returns the current date. Explanation of Example 8 If the month value of the employee's date of birth is the same as the current month and the day value of the employee's date of birth is the same as the current day, then it is the employee's birthday -- display message. Otherwise, it is not the employee's birthday -- do not display anything. PRODUCT : Paradox for Windows NUMBER : 1437 VERSION : 1.0 OS : WIN DATE : April 14, 1993 PAGE : 8/8 TITLE : How to Use IIF (Immediate If) in Calculated Fields NOTE: Notice how you can use the "AND" operator to make a compound condition. Result of Example 8 Assume today is 7/4/90. Employee ID DOB IIF() Result ─────────── ──────── ─────────────── 1 7/4/62 Happy Birthday! 2 12/25/43 3 7/4/57 Happy Birthday! 4 1/1/70 To order other Technical Information sheets by fax, phone our TechFax number at (800)822-4269. A menu system will guide you through ordering Technical Information sheets which will be faxed directly to you. You can even order a catalog which lists articles you can request when calling the TechFax Number. DISCLAIMER: You have the right to use this technical information subject to the terms of the No-Nonsense License Statement that you received with the Borland product to which this information pertains.