All things TrackVia!

Read, write, comment.

This is a public Category  publicRSS

Archived FAQ

    • How do I use logic functions?
      Archived FAQ posted 12/19/08 by Admin, last edited Aug 05 by Matt Strenz, tagged Field Types
      652 Views, 0 Comments
      question:
      How do I use logic functions?
      answer:
      Logic Functions

      Logic functions allow you to perform a test on your record, and return different results from the formula based on whether the test passed or failed. The simplest example is with the if function, which returns one value if the test is true, and another if it's false. For example, a calculated percentage field called Discount could have a formula of

      if(total sale > 1000, 10%, 0)

      which should be interpreted as meaning, "If the total sale is more than $1000, then use a 10% discount, otherwise use zero discount."

      Tests can also be nested:

      if(total sale > 5000, 20%, if(total sale > 1000, 10%, 0))

      Here the "otherwise" value of the first if(...) is another if(...) function. The formula means, "If the total sale is greater than $5000, then use 20%, otherwise if it's greater than $1000, use 10%, otherwise use zero."

      Comparisons can be made with text fields:

      if(sex = "Male", "Mr.", "Ms.")

      To compare a text value with many different choices, use the map function instead of nesting a bunch of if(...) functions. A Full State Name field could have a formula of

      map(state abbreviation, "CA", "California", "NY", "New York", "DC", "Washington D.C.")

      This formula will examine the value of the State Abbreivation field, and return California if it matches CA, otherwise New York if it matches NY, and so on. You can put in a final value that will be returned in none of the values match:

      map(state abbreviation, "CA", "California", "NY", "New York", "DC", "Washington D.C.", "fly-over country")

      You can also test multiple conditions using the function and(test1, test2, ...), which is true only if all of the tests in it are true, or or(test1, test2, ...), which is true if any of the tests in it are true. For example, to give a discount to both large purchases and good clients, we could use

      if( or(total sale > 5000, client type = "best"), 20%, 0%)

      Note that the = test compares values as if they were text. Therefore, 10.0 and 10 are not considered equal by the = operator. This is usually not a problem in numeric calculations, because TrackVia represents all numbers internally in a consistent way. (That is, it will use 10 for calculations instead of 10.0, even if your display options in a particular view are set to show 10.0, or $10.00.) However, it can be a problem if your numeric values are coming from a text field, like a short answer or drop-down field. If you want to force TrackVia to test the equality of two values as if they were numbers, you can use the == operator, as in

      if(my dropdown field == 10, "yes", "no")

      If you use = with text fields, the comparison is case sensitive, so Bob does not equal bob. To make a case-insensitive test between fields A and B, you can convert them both to lowercase first, like lower(A) = lower(B).

      Finally, for information about the parentlookup function, which allows you to pull data from records linked by a link to parent record field into a child record, see the Relational Fields article.

      TrackVia has many different functions for testing your data and performing complex logic. For help setting up TrackVia to solve your particular problem, please feel free to contact us!

      Built-in functions. TrackVia's built-in logic and comparison functions are shown below. The quantities in parentheses are inputs that the the function requires. In your formulas, these should be database field names, numbers, text inside quotation marks, etc.

      Function or Operator Description
      A = B Tests whether A is equal to B. A and B can be text, numbers, dates, or TrackVia user fields.
      A == B Tests whether A is equal to B, but forces a numeric interpretation of A and B. For example, 10.0 == 10 is true. If A or B is text, it will be treated as zero. (You will usually not have to use this operator, as plain old = does the right thing in the majority of situations.)
      A > B
      A < B
      A >= B
      A <= B
      		 Tests whether A is greater than B numerically.
      Tests whether A is less than B numerically.
      Tests whether A is greater than or equal to B numerically.
      Tests whether A is less than or equal to B numerically.
      if(test, A, B) Returns A if test is true, otherwise returns B.
      map(value,A1,A2,B1,B2,...) Returns A2 if value is equal to A1, B2 if value is equal to B1, and so on. See discussion above.
      and(test1, test2, ...)
      or(test1, test2, ...)
      not(test1)      		 Returns true if all tests in it are true.
      Returns true if any test in it are true.
      Returns the opposite of the test, that is, returns true if test is false, and false if test is true.
      isblank(A) Returns true if the field in A is blank, that is, appears in a record detail page as (none). If A has any value in it, will return false.
      ischecked(field, choice) Returns true if the checkbox field field has the choice option checked.

      Example: ischecked(properties, "gets discount") returns true if the gets discount option is checked in the checkbox group field called Properties.
      isselected(field, choice) Same as ischecked, but for drop-down fields instead of checkbox group fields.

      Example: ischecked(status, "late") returns true if the Status drop-down field is set to Late.

      Note that for drop-down fields, a simple test of status = "late" is sufficient. For checkbox groups, however, you must use ischecked().
      isnotblank(A)
      isnotchecked(field, choice)
      isnotselected(A)      		 Returns opposite of isblank()
      Returns opposite of ischecked()
      Returns opposite of isselected()
      parentlookup("field","field")
      parentlookup("field","field","field",...)
      		 Returns the value of a field in a record linked to by one or more link to parent record fields. That is, it pulls a parent record's value, or a grandparent record's value, and so on, into the child record. The inputs to this function are fields names enclosed in quotation marks (note this differs from how fields are referred to by all other functions). The first field name must refer to a link-to-parent-record field in the current database. If additional link-to-parent-record fields are provided, TrackVia will follow those links from one database to the next. The last field name specifies the individual value that will be returned. For more details on how to use this function, see the Two Relational Tricks article.