True/False Formulas

In Panorama as in most programming languages, control flow decisions are made on the basis of formulas that are either true or false. The most basic true/false formula compares two values to see if they are equal.

    PaymentMethod="C.O.D."

This formula will compare the value in the field PaymentMethod with C.O.D. The result will be true if PaymentMethod is C.O.D., and false if it contains anything else (for example Check, Cash, Visa, etc.).

#### Universal Comparison Operators

Panorama has about a dozen different operators that can compare two values and produce a true/false result. These comparison operators will work with any type of data: text, numeric, or date.

A=B Compares A with B, returns true if they are exactly equal.

A<>B Compares A with B, returns true if they are not exactly equal. You can also use A≠B.

A>B Compares A with B, returns true if A is larger.

A>=B Compares A with B, returns true if A is larger or equal. You can also use A≥B.

A<B Compares A with B, returns true if A is smaller.

A<=B Compares A with B, returns true if A is smaller or equal. You can also use A≤B.

All of the above operators require that A and B be the same data type. In other words, you cannot directly compare numbers to text, or text to dates. If A and B are different types you must convert them to the same type before comparing them, using the str(, val(, pattern(, date( or datepattern( functions.

#### Text Comparison Operators

Panorama also has a number of specialized comparison operators that work only with the text data type. When using these operators, both operands must be text.

A beginswith B This operator checks to see if the text in A begins with the characters in B. For example, the formula below will determine if the Name begins with the letters `Dr.`

    Name beginswith "Dr."

This formula will be true if the name is Dr. Robert Johnson, and false if the name is Mark Reynolds.

Note: The beginswith operator does not worry about upper or lower case, so DR. ROBERT JOHNSON or dr. robert johnson will also produce true results. If upper and lower case are important to you use the matchexact operator.

A endswith B This operator checks to see if the text in A ends with the characters in B. For example, the formula below will determine if the Name ends with the letters `D.D.S.`

    Name endswith "D.D.S."

This formula will be true if the name is Ronald Nelson, D.D.S., and false if the name is Mark Reynolds.

Note: The endswith operator does not worry about upper or lower case, so ronald nelson, d.d.s. would also produce a true result. If upper and lower case are important to you use the matchexact operator.

A contains B This operator checks to see if the text in A contains the characters in B. For example, the formula below will determine if the Address contains the letters box.

    Address contains "box"

This formula will be true if the address is P.O. Box 5328, and false if the address is 6389 E. Wilson Blvd.

Note: The contains operator does not worry about upper or lower case, so P.O. BOX 5328 and p.o. box 5328 would also produce true results. If upper and lower case are important to you use the matchexact operator.

A notcontains B

This operator checks to see if the text in A does not contain the characters in B. This is the exact opposite of the contains operator. For example, the formula below will determine if the Address contains the letters `box`.

    Address notcontains "box"

This formula will be true if the address is 6389 E. Wilson Blvd, and false if the address is P.O. Box 5328.

Note: This same function could also be performed by combining the not operator with the contains operator in the formula:

    not (Address contains "box")

A soundslike B This operator checks to see if the text in A “sounds like” the text in B. For example, the formula below will determine if the LastName sounds like the name Smith.

    LastName soundslike "Smith"

This formula will be true if the name is Smith, Smyth or Smythe, and false if the name is Jones or Williams.

The method Panorama uses to determine whether two values sound alike is called “soundex.” This technique is not very exact, and often will produce extra matches that you might not think really sound similar. However, it almost never fails to match on names that do sound similar, so it is a good starting point when you are not sure of an exact spelling.

The soundex technique does require that the first letter of the two values match. For example even though we think they sound alike, Christy and Kristy will not match because the first letter is different.

A match B

This operator checks to see if the text in A matches a pattern you specify in B. The pattern allows you to set up very flexible “wildcard” matches where some characters must match and some don’t have to.

The pattern should combine normal characters, which must match the text in A, and wildcard characters: ? and *. The ? wildcard character will match any character. The * wildcard character (asterisk) will match a variable number of characters. The best way to understand wildcard matches is probably to look at a few examples.

Our first example uses the pattern `j*johnson`. With this pattern the name must begin with `j` (or `J`) and end with `johnson` (or `Johnson`, etc.) The characters in between don’t matter.

    Name match "j*johnson"

This formula will produce a true result for names like Jim Johnson, Jack Johnson, Joe Johnson, etc. The formula will also be true for names like J346 Ujohnson or J@#opcjohnson.

The second example uses the pattern `926??`. With this pattern the zip code must begin with `926` and must be 5 digits long. (Our example assumes that ZipCode is a text field, not a numeric field.)

    ZipCode match "926??"

This formula will produce a true result for zip codes like 92631 or 92685 but a false result for zip codes like 89324 or 92685–0301. Here’s a variation that will work with 5 or 9 digit zip codes. The `??` characters mean that there must be at least five digits, while the `*` means that any extra characters are ok.

    ZipCode match "926??*"

This formula will produce a true result for zip codes like 92631, 92685 or 92685–0301, but a false result for 926 or 9262.

Don’t forget that a space is a normal character. The example below checks for people with a middle initial. The pattern looks for any number of characters followed by a space, followed by a single character, followed by a period, followed by another space, followed by any number of characters.

    Name match "* ?. *"

This formula will produce a true result for Robert E. Lee or Winston O. Link, but a false result for Frank Tesh, Billy Martin, or Sara Jessica Parkman.

The match operator can be used to simulate the beginswith, endswith and contains operators.

- `A match B+"*"` is the same as `A beginswith B`
- `A match "*"+B` is the same as `A endswith B`
- `A match "*"+B+"*"` is the same as `A contains B`

Note: The match operator does not worry about upper or lower case. If upper and lower case are important to you, use the matchexact operator.

A matchexact B This operator checks to see if the text in A matches a pattern you specify in B. This operator works exactly the same as the match operator, except that the normal characters must match exactly, including upper and lower case. For example, the formula below

    Name matchexact "J*Johnson"

will produce a true result for Jeff Johnson, but a false result for JEFF JOHNSON. (However, JEFF Johnson would produce a true result.)

You can use the matchexact operator instead of beginswith, endswith, or contains if you need an exact upper and lower case match.

A notmatch B This operator is the exact opposite of the match operator.

A notmatchexact B This operator is the exact opposite of the matchexact operator.

A like B This operator checks to see if the text in A matches a pattern you specify in B. This operator is similar to the matchexact operator, but it uses different wildcard characters: `%` and `_` instead of `*` and `?` Here are some examples showing both formats:

    Name matchexact "J*Johnson"
    Name like "J%Johnson"
    Zip matchexact "926??*"
    Zip like "926__%"

A regexmatch B This operator checks to see if the text ont the left matches the regular expression on the right (see Regular Expressions). The regexmatch operator is case insensitive (ignores the difference between upper and lower case) – if you want a case sensitive match use the regexmatchexact operator.

Entire books have been written about regular expressions (see Regular Expressions for recommendations), and you’ll probably want to read one or more of these books to get the maximum benefit from this powerful tool. Here are some very simple examples.

Does text contain a number?

    "Deadline is in 34 days" regexmatch "[0-9]+" ☞ TRUE
    "Deadline is Friday" regexmatch "[0-9]+" ☞ FALSE

Does text contain letters (and only letters and spaces) inside parentheses?

    "Bob Smith" regexmatch "\([A-z ]+\)" ☞ FALSE
    "Mary Wilson (Vice President)" regexmatch "\([A-z ]+\)" ☞ TRUE

Is the text a valid e-mail address (valid format, at least)?

    "joe@bob.com" regexmatch "^[\w!#$%&'*+/=?`{|}~^.-]+@[A-Z0-9.-]+$" ☞ TRUE
    "j oe@bob.com" regexmatch "^[\w!#$%&'*+/=?`{|}~^.-]+@[A-Z0-9.-]+$" ☞ FALSE

The final example gives you a taste of what Regular Expressions are capable of.

A regexmatchexact B This operator checks to see if the text on the left matches the regular expression on the right (see Regular Expressions). This operator is identical to the regexmatch operator described above, except that this version is case sensitive.

#### Combining Comparisons

The basic comparisons described in the previous section can be combined together for more complicated decisions. There are four basic operators that can combine or modify decisions: and, or, xor, and not.

A and B The and operator combines two true/false formulas together so that the result is only true if both formulas are true. The example procedure below determines if a person is a teenager.

    if Age>=13 and Age<20
        Status="Teenager"
    endif

The result of the formula is only true if the person is 13 or older and less than 20.

A or B The or operator combines two true/false formulas together so that the result is true if either one of the two formulas are true. The example below determines if a transaction is being paid with a credit card.

    if PaymentMethod="Visa" or PaymentMethod="MasterCard"
        Terms="Credit Card"
    endif

The result of the formula is only true if the payment method is Visa or MasterCard.

Notice that each side of the or operator must contain a complete formula. The formula below looks right in English, but will not work in Panorama. The example below is WRONG:

    if PaymentMethod="Visa" or "MasterCard"      /* WILL NOT WORK !! */

There must be a comparison on both sides of the `or`, as shown in the first example.

A xor B The xor (short for exclusive-or) operator is a bit tricky. Xor combines two true/false formulas together so that the result is true if one of the two formulas is true, but false if both are true or both are false. Another way to put it is that the result will be true if A and B are different, but false if they are the same. The example below determines if two shoes are a pair.

    if Shoe1="Left" xor Shoe2="Left"
        message "These shoes are a pair"
    endif

The result of the formula is only true if one shoe is Left and the other shoe is Right (or to be more precise, not Left).

not A The not operator reverses a true-false formula. If the result was true, now it will be false. If it was false, now it will be true.

    if not (Shoe1="Left" xor Shoe2="Left")
        message "These shoes are not a pair!"
    endif

#### Equals Comparison vs. Assignment

If you have skipped ahead to read about procedures you know that the equals sign is used to assign a value to a field or variable. The example formula we used earlier to compare two values:

    PaymentMethod="C.O.D."

would also be the same formula used to assign the value C.O.D. to the field or variable PaymentMethod. At first glance this may appear ambiguous…the same formula is used to compare two values and to assign a value. How do we know when we are assigning and when we are comparing? The answer lies in the context in which the formula is found.

In a procedure, an assignment is always by itself, not part of a larger statement. A true-false formula is always part of another statement, for example if, case, until, while, stoploopif, repeatloopif, find, select. Here’s an example that shows two formulas that look almost the same, but one is a true-false formula and one is an assignment.

    if PaymentMethod="C.O.D."
        ShippingMethod="UPS"
    endif

The first formula, `PaymentMethod="C.O.D."`, is part of the if statement. This formula means: Is the field (or variable) PaymentMethod equal to C.O.D. (true/false)?

The second formula, `ShippingMethod="UPS"`, is not part of any statement, but stands alone, so this is an assignment. The statement means: Take the value UPS and copy it into the field or variable named ShippingMethod.

If an assignment has more than one equals sign, the first equals sign is for the assignment and the rest are for comparisons. The example assignment below compares B and C. If they are equal (true) the value -1 will be copied into A. If they are not equal (false) the value 0 will be copied into A.

    A=B=C

In other words, A becomes the result of the comparison between `B=C`. Another, perhaps clearer, way to write this would be:

    A = (B=C).

#### True/False Values

For purposes of calculation, Panorama treats true and false as numbers: true is -1 and false is zero. Panorama also has two functions that directly generate these values: true() and false().

Like any other number, you can store a true/false value in a field or variable and then use it later. The example below calculates whether a person is a teenager, then uses that information later.

    local Teenager
    Teenager=Age>=13 and Age<20 ...
    if Teenager
       Price=4.50
    else
        Price=6.00
    endif

Notice that the if statement doesn’t need to compare, it simply uses the result of the comparison that was calculated earlier. In fact, the if statement (and all other statements that use true/false logic) can use any formula that produces a numeric integer result. The value 0 will be regarded as false, and any non-zero value will be regarded as true. The example below will be true if the length of the name is non-zero.

    if length(Name)
       yesno "Is this a home address?"
       ...
    endif

The first line of this example could also have been written

    if length(Name)<>0

The result is the same either way.

#### The ? Function

The ?( function allows a formula to make a decision. Will it be door number 1 or door number 2? The function uses a true-false value to pick from one of two values. The basic syntax for this function is like this.

    ?(decision-value,true-value,false-value)

The first parameter, decision-value, is used to pick which of the two choices will be returned as the final value, the true-value or the false-value.

For example, the formula below can be used to calculate a 10% discount if the quantity is 100 or more—

    ?( Qty<100 , Price , Price*0.9 )

You can add additional pairs of decision-value,true-value parameters to the function.

    ?(decision-value1,true-value1,decision-value2,true-value2, … false-value)

In that case, it will evaluate each pair from left to right. If none of the decision-value parameters are true then the final falsevalue parameter will be selected.

    ?(5=5,"same",5>5,"bigger","smaller") ☞ same
    ?(5=4,"same",5>4,"bigger","smaller") ☞ bigger
    ?(5=6,"same",5>6,"bigger","smaller") ☞ smaller

No matter how many truevalue and falsevalue parameters are included in the function, ultimately only one is actually used. The rest are effectively discarded. Since these extra parameters are not actually used, Panorama doesn’t require that they have valid values. In other words, if an unused parameter contains a runtime error, Panorama doesn’t care. Since the value isn’t used, the formula continues normally without stopping because of the error.

For example, suppose that you have a division formula like this:

    numerator/denominator

This is fine for most values, but what if the denominator is zero? Division by zero is invalid. Using the ?( function we can ensure that a valid result is calculated even if the denominator is zero (assuming you think zero is an acceptable result in this situation).

    ?(denominator<>0,numerator/denominator,0)

If the denominator is zero the division is ignored, and zero is returned.

#### The Switch( Function

The switch( function makes it easy to choose from a list of values. The first parameter is the key. The function attempts to match the key value with each of the case values (from left to right). If it finds a match, the function returns the corresponding value. If there are no matches at all, the default value is returned. (Each case/value pair is shown on a separate line in the examples below, but this is not necessary.)

    switch(1,
        1,"Gold",
        2,"Silver",
        3,"Bronze",
        "-") ☞ Gold
    switch(2,
        1,"Gold",
        2,"Silver",
        3,"Bronze",
        "-") ☞ Silver
    switch(7,
        1,"Gold",
        2,"Silver",
        3,"Bronze",
        "-") ☞ -
    switch("Gold",
        "Gold",79,
        "Silver",47,
        "Copper",29,
        "Tin",50,
         0) ☞ 79
    switch("Copper",
        "Gold",79,
        "Silver",47,
        "Copper",29,
        "Tin",50,
         0) ☞ 29
    switch("Cu",
        "Au","Gold",
        "Ag","Silver",
        "Cu","Copper",
        "Sn","Tin",
        "Unknownium") ☞ Copper
    switch("Nx",
        "Au","Gold",
        "Ag","Silver",
        "Cu","Copper",
        "Sn","Tin",
        "Unknownium") ☞ Unknownium

As shown in the examples above, the key and case values can be either text or numbers (you can even mix both text and numbers, though this rarely makes sense). The returned value parameters can also be either text or numbers (again, it usually makes sense for all of them to be the same type).

Panorama has two variations on the switch( function: switchmatch( and switchmatchexact(. These are similar to the switch( function but match based on a wildcard match instead of exact equality.

#### Converting a Boolean Value to Text

The boolstr( function converts a Boolean value to text, either true or false. For example

    message boolstr(Qty<100)

will display true if the Qty is less than 100, or false if it is greater or equal to 100.

See Also

- Arithmetic Formulas -- mathematical operators and functions.
- Characters and Unicode Values -- working with individual characters of text.
- Constants -- values embedded into a formula.
- convertvariablestoconstants -- converts all of the variables in a formula into constant values.
- Date Arithmetic Formulas -- performing calculations on dates, and converting between dates and text.
- Formula Workshop -- formula workshop wizard for testing and experimenting with formulas.
- formulacalc -- allows you to evaluate a formula that you were not able to code into the procedure when it was being written.
- formulafields( -- returns a list of fields used in a formula.
- formulaidentfiers( -- returns a list of identifiers (fields and variables) used in a formula.
- Formulas -- basics of formulas: components and grammar.
- formulavalue -- calculates the result of a formula. Usually this is done with an assignment statement (for example `x=2 * y` ), but the *formulavalue* statement gives you more flexibility. You can specify what database is to be used for the calculation (an assignment statement always uses the current database) and you can specify the formula using a variable, making it easy to change on the fly. This statement also gives you more control over how errors are handled.
- formulavariables( -- returns a list of variables used in a formula.
- Functions -- index of all functions available for use in Panorama formulas.
- Linking with Another Database -- techniques for relating multiple database files so that they work together.
- makemergeformula( -- builds a formula from an “auto-wrap" style merge template.
- Non Decimal Numbers -- working with numbers in alternative (non-decimal) bases, including binary, octal and hexadecimal.
- Numbers -- Working with numeric values in a formula, and converting between numbers and text.
- Operators -- index of all operators available for use in Panorama formulas.
- Quotes -- text constants embedded into a formula
- Recompiling Code -- recompiling code & formulas
- Statements -- index of all statements available for use in Panorama procedures.
- SuperDates -- date and time combined into a single value.
- Tag Parsing -- Panorama functions for working with text that contains data delimited by tags, including HTML.
- Text Formulas -- manipulating text with a formula (concatenation, extraction, rearranging, etc.)
- Time Arithmetic Formulas -- performing calculations on times, and converting between times and text.
- Using Fields in a Formula -- accessing database fields within a formula.
- Values -- the raw material that formulas work with -- numbers and text.
- Variables -- storing and retrieving individual items of data, not part of a database.

History

Version | Status | Notes
10.0 | No Change | Carried over from Panorama 6.0