OL: How to Use Formulas in a Custom Outlook Form (319911)


The information in this article applies to:

  • Microsoft Outlook 2000
  • Microsoft Outlook 2002
This article was previously published under Q319911

SUMMARY

With formulas, you can use functions on standard and custom fields to calculate values. Use formulas when you have to calculate and store a value that is based on the value of other fields in your application.

If you add formula fields, you may affect the performance of Outlook. When you use formulas, Outlook must recalculate the value each time that the dependent field's value changes. Performance is affected based on the complexity of the formulas that you use.

Typically, it is best not to mix formulas in conjunction with Microsoft Visual Basic Scripting Edition (VBScript). The functions and methods that are available when you use the Microsoft Visual Basic expression service can also be used in VBScript.

MORE INFORMATION

There are three ways to use formulas in a custom Outlook form. Each method can use the Visual Basic expression service that is built into Outlook:
  • Create a custom field of type Formula. These fields are read-only in Outlook.
  • Create a formula on the Validation tab in the properties of a control. This formula checks the validity of the data in the field before saving the data.
  • Create a formula on the Value tab in the properties of a control. This formula instantiates the initial value of the control.

Creating a Formula

The formula editor is the same for all three methods of using formulas in Outlook fields. The editor provides a way to gain access to both standard Outlook fields and custom user-defined Outlook fields. The editor also includes frequently used functions that are associated with math, date and time, and text.

Sample Idea for using a Formula

This formula calculates the price of buying a certain quantity of items at a particular price:
  1. Open a new standard Message form.
  2. On the Tools menu, point to Forms, and then click Design this Form.
  3. Click the (P.2) tab to open a new blank design page. Initially, this page is not displayed when running. After you add controls, this page is displayed automatically.
  4. In the Field Chooser, click New to create three new fields of type Number and format 2 Decimal. (These fields are referred to as field1, field2, and field3 throughout the rest of this article.) Add them to your form by dragging them to the blank P.2 page.
  5. Right-click the text box of field1, and then click Properties. Click the Value tab, click to select the Set the initial value of this field to check box, and then type 3.50 in the text box. Click Calculate this formula when I compose a new form.
  6. Right-click the text box of field3, and then click Properties. Click the Value tab, click to select the Set the initial value of this field to check box, and then click Calculate this formula automatically. Click Edit, and then add the following formula in the text box.

    NOTE: Do not copy the following code as-is. Change field1 and field2 to the actual names of the fields that you created in step 4 and added to your form:
    [field1] * [field2]
  7. On the Form menu, click Run this Form. Add a number in field2. When you use the TAB key to move out of the text box, field3 automatically changes based on the values in field1 and field2.
  8. To save your changes, publish according to the instructions in the following Microsoft Knowledge Base articles:

    257796 OL2000: How to Determine Where to Publish a Form

    290802 OL2002: How to Determine Where to Publish a Form

Using a Formula in a Custom View

You can create custom views that use the custom fields that contain formulas for grouping. If you want to group by a field, the field cannot be of the type Formula because fields of the type Formula do not contain any data. The data that is displayed is calculated on the fly. Therefore, the field that you use for grouping must exist on the custom form. The formula is added in the Properties tab of the control that is associated with the custom field.

Sample Idea for using Fields That Contain Formulas in a Custom View

To create a custom view that groups and displays custom Contact forms by the birthday month of the Contact:
  1. Create the custom Contact form:
    1. Open a new standard Contact form.
    2. On the Tools menu, point to Forms, and then click Design this Form.
    3. Click the (P.2) tab to open a new blank design page. At first, this page is not displayed when running. After you add controls, this page is displayed automatically.
    4. In the Field Chooser, locate Personal fields or All Contact fields. Add the standard Outlook field Birthday by dragging the field to page P.2 on the form.
    5. In the Field Chooser, click New to create a new field BirthMonth of type Text. Drag the new field to page P.2 on the form.
    6. Right-click the text box of BirthMonth, and then click Properties. Click the Value tab, click to select the Set the initial value of this field to check box, and then click Calculate this formula automatically. Click Edit to add the following formula in the text box: 
      Format( [Birthday], "mmmm" )
      Alternatively, you can use the Field and Function buttons to insert the formula for you. Insert the Format function by clicking Function. Select Text, and then click the Format function that labeled Format( expr , fmt ). Replace "expr" with the Birthday field by clicking Field, and then moving through the field list. Select All Contact fields, and then click Birthday. Replace "fmt" with "mmmm" (including the quotation marks).
    7. Repeat steps 1e and 1f to create a field that is named BirthDayNum by using the same settings. In the formula editor, add the following formula:
      Format( [Birthday], "dd" )
    8. On the Tools menu, point to Forms, and then click Publish Form As. Set the Look In folder to Contacts, set the Display Name to BirthFormula, and then click Publish. If you are prompted to save the form definition with the item, click No.
    9. Close the form and do not save changes.
  2. Create the Custom view. This view will display Contacts based on the birthday of the Contact, regardless of the birth year of the Contact:
    1. Locate your Contacts folder. On the Actions menu, click New BirthFormula. This installs, caches, and displays your new custom Contact form on your computer.
    2. Add test data in fields such as Full Name and Company.
    3. Add a date to Birthday on page P.2.
    4. Click Save and close to save and close the item without adding the birthday to your calendar.
    5. Save a few test items that use the new custom Contact form by repeating steps 2a through 2d.
    6. In the Contacts folder, click the View menu, point to Current View, and then click Define Views.
    7. Click New, and then name the new view "Birth Month". Set Type of View to Table, and Can be used on to This folder, visible to everyone.
    8. Click Fields, and then remove all of the fields except Full Name and Company. To do so, select the field name, and then click Remove. In the Select available fields from list, click Personal Fields, and then add the Birthday field to the list by clicking Add. When you are finished, click OK.
    9. Click Group By. In the Select available fields from list, click User-defined fields in folder. In the Group items by list, click BirthMonth, and then click OK.
    10. Click Sort. In the Select available fields from list, click User-defined fields in folder. In the Sort items by list, click BirthDayNum, and then click OK. When you are prompted about whether you want to add the field to the view, click No.
    11. Click Apply to apply the new defined view to the Contacts folder.

      NOTE: Some items may not seem to be sorting correctly. Only the items that share the same message class as the new Contact form are properly displayed according to the sort.

REFERENCES

For additional information about available resources and answersto frequently asked questions about Microsoft Outlook solutions, click the article numbers below to view the articles in the Microsoft Knowledge Base:

287530 OL2002: Questions About Custom Forms and Outlook Solutions

146636 OL2000: Questions About Custom Forms and Outlook Solutions

Modification Type:MinorLast Reviewed:2/27/2004
Keywords:kbhowto KB319911
©2004 Microsoft Corporation. All rights reserved.