Outlook Formulas, Part 3

This issue of Outlook UPDATE concludes our three-part series about using formulas in Outlook folders and forms. The first two parts covered formula-type custom fields in Outlook folders and validation formulas on Outlook forms.

You can create formulas in two more places on Outlook forms. On the Validation tab for a form control, you can use a formula to build a custom validation message to complement your validation formula. For example, if you have a field named EmployeeID that requires exactly 10 characters, you can instruct the user by using this validation message formula:

"The value you entered for EmployeeID -- " & [EmployeeID] & " -- is wrong because it must be exactly 10 characters long."

In Outlook forms, formulas can also play a role in the properties of a control, on the Value tab, under Initial Value. You can enter a formula and make it either a default value or an automatically calculated value. If you create a formula field in a folder view, then add that field to an Outlook form, Outlook automatically fills in the formula.

However, you can also use these formula settings to set the values for built-in fields. If you check the box for "Calculate this formula when I compose a new form," your formula will be the default value, but the user can change the value. For example, on a task form, you might set the default value for the Start Date field to Date(), a function that returns today's date, and the Due Date field to Date() + 7, a formula that returns the date 7 days from today.

If you choose "Calculate this formula automatically," the user will not be able to override the value that your formula calculates. Let's say that you want the default value of the Subject field for a custom contact form for employee data to include the contact's last name and employee ID, which you've added as a custom EmployeeID field. Add the Subject field to the P.2 page, and then try this formula:

[Last Name] & " -- " & [Employee ID]

After the formula is working, you can hide the P.2 page, so that the user doesn't see the extra page.

Another application might be to control the value of the File As field on a custom form that you plan to use in a public folder, so that the form uses either the full name in last name, first name order or the company name or both, if both are present. You can use this formula to achieve that goal:

IIf([Full Name] <> "", IIf([Company] <> "", [Last Name] & ", " & [First Name] & Chr(13) & Chr(10) & [Company], [Last Name] & ", " & [First Name]), [Company])

The expression Chr(13) & Chr(10) separates the contact name and the company name with a carriage return and line feed in order. It took me a long time to figure out that you must use this exact separator if you want the File As information generated from a formula to duplicate the behavior that you get in the default form. If you use this separator, the contact and company name will appear on two lines in a card view, separated by commas in a table view and with the company name enclosed in parentheses in the File As field on an open contact item.

For more examples of formulas, open Help in Outlook so that you see the Contents, then work your way down the hierarchy to Advanced Customization, Working with Fields and Controls, Working with Custom Fields, Examples of Formulas and Combination fields.