Types of Formulas

Working with Formulas > Types of Formulas

Formulas can be used in many types of data operations and you can create formulas that return any valid data type, including numbers, characters, dates, and true/false conditions.

Numbers

This type of formula returns a numeric value as its result.  As an example, assume you have two fields, one with a "height" value and one with a "width" value.  To identify the area of the item, you would use the following formula:

height * width

This formula uses two numeric values and results in a numeric value.  However, if we only had a single field called "area" that contained character values like "16x10" and "20x4", we would need to first parse the values (using the function STRPART())and then covert them to numeric type (using the function VAL()), before multiplying them together.  For example, assuming the field area has one record with the value "16x10":

Create a calculated field called "height" with the following formula:

strpart(area,1,"x")  [this would produce the character "16"]

Then, create a second calculated field called "width" with the following formula:

strpart(area,2,"x")  [this would produce the character "10"]

Finally, because both of these formulas result in "character" types, we need to convert them to numbers and multiply them together:

val(height) * val(width)  [this would produce the number "160.00"]

Alternatively, you could just combine all of this into a single formula as follows:

val(strpart(area,1,"x")) * val(strpart(area,2,"x"))  [this would produce the number "160.00"]

You typically use this type of formula format with calculated fields.

Characters/Strings

This type of formula returns a character or a string as its result.  As an example, assume you have two address fields, "Address1" and "Address2" that you wish to combine into a single field.

Let's assume that Address1 contains "123 Happy Lane" and Address2 contains "Apt 4", you might use the following formula:

Address1 + Address2 [this would produce the character "123 Happy LaneApt 4"]

You'll notice that you need to add a comma and space between the two fields, to make this full address legible, like this:

Address1 + ", " + Address2  [this would produce the character "123 Happy Lane, Apt 4"]

In some cases, you may find that you have a numeric field that you wish to convert to a character field.  For instance, maybe a zip code field "Zipcode" was mischaracterized as a numeric value, such as "60640".  You could convert this as follows using the STR() function:

STR(Zipcode)  [this would produce the character "60640"]

This would then let you add together the entire address (where "Citystate" = " Chicago, IL "):

Address1 + ", " + Address2 + Citystate + STR(Zipcode)  [this would produce the character:  "123 Happy Lane, Apt 4 Chicago, IL 60640"

You typically use this type of formula format with calculated fields.

Dates

This type of formula returns a date as its result.  As an example, assume you have a field called "Invoice date" and want to determine the actual date when your payment is due 30 days later.  Assuming your date was 01/20/2008:

[Invoice date] + 30  [this would produce the date "02/19/2008"]

Often you may see a date that is mischaracterized as a character, such as "02192008" in a field called "Invoicedate".  You could convert this as follows using the Date() function:

Date([Invoice date], "MMDDYYYY")  [this would produce the date "02/19/2008"]

You typically use this type of formula format with calculated fields.

True/False (Logical Conditions)

This is a logical condition that expresses a value of either "True" or "False" (and also the result of a Boolean expression).

As an example, assume you have a field named "State" that contains the three state names "IL", "NY" and "CA."  If the variable called "State" is equal to "IL" then the statement is true, otherwise it is false:

State = "IL" is a true statement

State = "NY" is a true statement

State = "OH" is a false statement

In addition to calculated fields, the following data operations also use expressions that are logical conditions: