Using Combination and Formula Fields in Outlook Applications

Ask the Exchange Pro 10-Minute Solution

Using Combination and Formula Fields in Outlook Applications
By Ben M. Schorr

Many of you have created custom Outlook forms for a variety of applications and you're probably familiar with creating and inserting standard fields like text fields or number fields. You may not have fully appreciated, however, the power of the combination and formula fields in Outlook. This 10-Minute Solution takes a closer look at them.

Combination Fields
As the name implies, combination fields are used to combine the values of multiple Outlook fields. There are two main options that control how this combination will be done.

Joining fields and any text fragments to each other. This option takes the content of the fields, plus any text that you type into the formula yourself, and displays it as a single string.

Here's an example. If you're using an item where the [Fullname] field contains the data "John Smith" and your [Business Phone] field contains the data "(808) 555-1212," you could create a combination formula that reads like this:

"Call [Fullname] at [Business Phone]"

The result for this item would be "Call John Smith at (808) 555-1212."

You can think of it as sort of a mail-merge field. It simply takes the values of all fields you specify—in the order you specify them—and displays them along with any text you might enter.

Showing only the first non-empty field, ignoring subsequent ones. This option takes a different approach. Here you specify a list of fields in a specific order, and the value of the first one of those fields that isn't empty is what gets displayed in the combination field.

For example, if you want a field that shows the best phone number to use to reach a customer, you might decide to try calling him or her first at work, then at home, and finally on a mobile number. However, you might not have three such phone numbers for every customer. In that case, you could create a combination field that quickly shows the best number you actually have.

The formula looks like this:

"[Business Phone][Home Phone][Mobile Phone]"

There's no need to type any connecting or explanatory text in this formula because Outlook is only going to ignore it anyhow.

Assuming the customer's Home Phone number is (808) 555-1212, and the Business Phone number is blank, the combination field displays "(808) 555-1212."

It makes no difference what the Mobile Phone number is because the Home Phone is the first non-empty field.

To create a combination field, just create a new field by clicking the New button in the Field Chooser dialog box. Name the field and set the type to "Combination." Click the Edit button next to the formula field to create the combination formula.

Formula Fields
Unlike combination fields, formula fields let you take values and data and make calculations, much as you might do with Excel. In fact, some of the same functions and formulas are available to Outlook.

You create a formula field by clicking the New button in the Field Chooser dialog box. Name the field and set the type to Formula; then click the Edit button next to the formula field to create the formula.

Once you're in the Formula Editor you can insert fields and/or operators to perform calculations. For example, you might multiply the value of the "mileage" field by .05 in order to calculate a $.05 per-mile charge. The formula for that would look like this:

[Mileage] * .05

One nice feature of formula fields is the use of the IIF function. Basically the same as the IF function in Excel, the IIF function lets you test an expression and take different actions depending on whether or not it's true. Here's its syntax:

IIF(expression, true, false)

This function first evaluates the expression and then determines what value to present if it's true and what value to present if it's false.

For example, here's a Sales Rep field that uses this overly simplistic calculation:

IIF([State] = "HI", "John Smith", "Diane Green")

This states that if the State field contains the value "HI," then the Sales Rep field should contain "John Smith." Otherwise it should contain "Diane Green."

Making the Fields Look Pretty
You may notice that there isn't really any way to format the combination and formula fields. They simply display the results of the formula in a plain manner.

How do you spice them up? Perhaps you want to display the results of a mileage calculation in a Currency field. No worries. The trick to doing that is to create a new Currency field and simply bind it to the formula field. For example, if your formula field is called "calcMiles," all you need to do is go into the properties of your new Currency field, then to the Value tab, and set the initial value of that field to be [calcMiles]. Be sure to check the "Calculate this field automatically" option button so that it updates as the calcMiles field does.

At this point you can even remove the calcMiles field from your form (but not from the folder) because the user doesn't need to see it. Now when you enter the number of miles into the Mileage field, calcMiles multiplies them by .05 and the result is mirrored in the new Currency field you created.

Combination and formula fields are powerful tools that enhance your client-side applications and forms. Spend a little time with them and you'll find new ways to use them to improve your applications and impress your users.

Other 10-Minute Solutions