Lab 5: Business Logic in InfoPath 2003

Article
07/11/2006

Microsoft Corporation

April 2004

Applies to:
Microsoft® Office InfoPath™ 2003

Summary: Create a simple schema validation, custom data validation using pattern-matching and script-based validation using the OnValidate event. Learn how to use formulas to calculate totals and construct an e-mail address. Use conditional formatting to call attention to a text box value and use rules to display a message box and to set the value for another field. (10 printed pages)

Scenario
Lab Objective
Level 200 Exercises
Level 400 Exercises
Conclusion

Download the odc_INF03_Labs.exe sample file.

Scenario

Like any organization, Contoso Corporation aims to reduce costs and increase efficiency wherever possible. The finance department has noticed that sales are often delayed because sales representatives enter the wrong information in forms or forget to fill out certain fields. To reduce data entry errors, and to automate the process of collecting data, Contoso's IT department has agreed to add business logic to a sales report form used by employees in the sales department.

Lab Objective

In this lab you learn how to do the following:

Specify that a text box cannot be blank.
Create a data entry pattern for a text box.
Create a formula to automatically calculate totals.
Create a rule to automatically fill in an e-mail address based on values in another field.
Add script-based validation to a form.

Level 200 Exercises

Exercise 1: Prevent Data Entry Errors

When filling out sales report forms, sales representatives occasionally forget to enter their names. In addition, they sometimes enter their personal e-mail address instead of their corporate e-mail address. In this exercise, you learn techniques to help prevent these kinds of data entry mistakes and omissions.

To make the Sales Representative text box required

Start InfoPath.
In the Fill Out a Form dialog box, click Design a Form.
In the Design a Form task pane, click On My Computer.
In the Open in Design Mode dialog box, locate the folder where you installed the training files, and then double-click the Lab 5 folder.
Select Lab5Template200.xsn, and then click Open.

The Contoso sales report form opens in design mode.
Double-click the Sales Representative text box.
On the Data tab in the Text Box Properties dialog box, select the Cannot be blank check box, and then click OK.

When sales representatives open this form, a red asterisk appears in the empty Sales Representative text box.

Figure 1. Sales Representative text box with asterisk

If they attempt to save or submit their form without entering their name, InfoPath prompts them to fix this error.

You can create data entry patterns to do the same thing in a more precise way for the E-mail Address text box. In the following procedure, you make an error alert appear if users inadvertently enter their personal e-mail address instead of their work e-mail address.

To assign a data entry pattern to the E-mail Address text box

With the form still open, double-click the E-mail Address text box.
In the Text Box Properties dialog box, on the Data tab, click Data Validation.
In the Data Validation dialog box, click Add.
Make sure that the first box under If this condition is true contains the control's field ("email"), and then in the second box, click does not match pattern.
In the third box, click Select a pattern.
In the Data Entry Pattern dialog box, in the Standard patterns list, select Custom pattern to clear the existing text in the Custom pattern box. Patterns use the syntax defined by the W3C XML Schema pattern restrictions.
In the Insert special character list, select Any Letter.
In the Insert special character list, select One or more.
In the Custom pattern box, type @contoso after the existing characters**.**
In the Insert special character list, select Period.
In the Custom pattern box, type com after the existing characters. The custom data entry pattern should look like this: .+@contoso\.com
Click OK to exit the Data Entry Pattern dialog box.
In the ScreenTip box in the Data Validation dialog box, type Please use your Contoso e-mail address. This text appears when a user points to or right-clicks the control.
Close all open dialog boxes.
To test your changes, click Preview Form on the Standard toolbar. Type name@example.com in the E-mail Address text box. A dashed red line appears around the text box, which indicates that there's a problem with the data you entered. When you move your mouse pointer over the text box, the ScreenTip that you specified in step 13 appears.

Exercise 2: Automate the Form

The sales report form contains a table that contains four columns named item name, item price, units sold, and total sales. The process of filling out this table can be time-consuming and prone to error. In this exercise, you learn how to make it easier for users to avoid simple mistakes and improve their overall experience of filling out a form.

First, you can make the form calculate totals automatically.

To calculate total sales automatically

With the form still open in design mode, double-click the text box inside the Total column in the repeating table.
In the Text Box Properties dialog box, on the Data tab, select Decimal (double) from the Data type list.
Click Format.
Under Format in the Decimal Format dialog box, click Currency.
In the Decimal Places list, click 2, and then click OK.
In the Text Box Properties dialog box, click Apply.
Under Default Value, click the fx button.
In the Insert Formula dialog box, click Insert Field or Group.
In the Select a Field or Group dialog box, click itemPrice, and then click OK.

The field name itemPrice appears in the Formula box. This is the first part of the expression.
To continue building the formula, type the multiplication symbol (*) after itemPrice.
Click Insert Field or Group again.
In the Select a Field or Group dialog box, click unitsSold. Click OK.

The finished expression, itemPrice * unitsSold, appears in the Formula box.
To close the Insert Formula dialog box, click OK.
In the Text Box Properties dialog box, click the Display tab, select the Read-only check box, and then click OK.
To test your changes, on the Standard toolbar, click Preview Form. In the Price and Units Sold fields, type numbers. Note that the Total field automatically shows the results of multiplying unit price by number sold.

In addition to calculating totals automatically, you can use conditional formatting in conjunction with rules to congratulate sales representatives when they sell more than 100 units of a particular product.

To draw attention to quantities over 100

With the form still open in design mode, double-click the text box inside the Units Sold column.
In the Text Box Properties dialog box, on the Data tab , click Rules.
In the Rules dialog box, click Add.
In the Name box, type Congratulate, and then click Set Condition.
In the Condition dialog box, make sure that the first box under If this condition is true contains the control's field ("unitsSold").
In the second box, select is greater than.
In the third box, select Type a number, type 100, and then click OK.
In the Rule dialog box, click Add Action.
Type Well done! in the Message box, and then click OK.
Click OK twice.
In the Text Box Properties dialog box, click the Display tab, and then click Conditional Formatting.
In the Conditional Formatting dialog box, click Add.
In the Conditional Format dialog box, make sure that the first box under If this condition is true contains the control's field ("unitsSold").
In the second box, select is greater than.
In the third box, select Type a number, and then type 100.
Under Then apply this formatting, select the Bold check box.
In the Font Color list, select a shade of green, and then close all open dialog boxes.
To test your changes, on the Standard toolbar, click Preview Form. In the Units Sold field, type 101, and then tab away from the field.

Contoso e-mail addresses use a standardized form: the employee's last name followed by @contoso. You can use rules in conjunction with formulas to automatically fill in the e-mail address field so that users don't have to.

To fill in e-mail addresses automatically

With the form still open in design mode, double-click the Sales Representative text box.
On the Data tab in the Text Box Properties dialog box, click Rules.
In the Rules dialog box, click Add.
In the Name box, type Set e-mail, and then click Set Condition.
In the Condition dialog box, make sure that the first box under If this condition is true contains the control's field ("salesRepresentative").
In the second box, select is not blank, and click OK.
In the Rule dialog box, click Add Action.
In the Action list in the Action dialog box, click Set a field's value.
Next to the Field box, click the Data Source button.
In the Select a Field or Group dialog box, select email, and then click OK.
Next to the Value box, click the Insert Formula button.
In the Insert Formula dialog box, click Insert Function.
In the Insert Function dialog box, in the Categories list, click Text.
In the Functions list, click concat, and then click OK. The formula should look like this:

Figure 2. Insert Formula dialog box
In the Formula box, click the first occurrence of double click to insert field, and then click Insert Function.
In the Insert Function dialog box, in the Categories list, click Text
In the Functions list, click translate, and then click OK. The formula should look like this:

Figure 3. Insert Formula dialog box
Replace the first three occurrences of double click to insert field with: ., " ", "_" (meaning to replace all spaces with underscores in the current field, which in this case is salesRepresentative, after the first space). The formula should look like this:

Figure 4. Insert Formula dialog box
Replace the next occurrence of double click to insert field with "@contoso.com" (including the quotes). The formula should look like this:

Figure 5. Insert Formula dialog box
Delete the second comma and last occurrence of double click to insert field*.* The formula should look like this:

Figure 6. Insert Formula dialog box
Close all open dialog boxes.
To test your changes, click Preview Form on the Standard toolbar. In the Representative field, type "FirstName LastName", and then tab away from the field.

Level 400 Exercises

Exercise 1: Add Script-Based Validation

When built-in data validation features are not sufficient, you can create an event handler to respond to a data validation event. In this exercise, you learn how to validate the price of an item by adding an OnValidate event handler to your form.

To add an OnValidate event handler

Start InfoPath.
In the Fill Out a Form dialog box, click Design a Form.
In the Design a Form task pane, click On My Computer.
In the Open in Design Mode dialog box, locate the folder where you installed the training files, and then double-click the Lab 5 folder.
Select Lab5Template400.xsn, and then click Open.

The Contoso sales report form opens in design mode.
In the repeating table, double-click the text box inside the Price column.
In the Text Box Properties dialog box, under Validation and Rules, click Data Validation.
In the Data Validation dialog box on the Events list, select OnValidate, and then click Edit.

Note If prompted to install scripting components, click Yes. When the installation is complete, you are prompted to restart InfoPath to use script debugging. For the purposes of this exercise, you do not need to restart InfoPath.

The Microsoft Script Editor (MSE) program starts.

In MSE, locate the following comment in the code:

function msoxd_my_itemPrice::OnValidate(eventObj) 
{ 

    // Write your code here 

}

Replace the above code with the code you want to run when the event occurs. For the purposes of this exercise, you can use the following sample code:

function msoxd_my_itemPrice::OnValidate(eventObj)
{
    // Get the value of the itemPrice field.
    var price = Number(eventObj.Site.text);

    if (isNaN(price))
    {
        // If the price is not a number, report an error.
        eventObj.ReportError(
            eventObj.Site,                      // Node with the error
            "Price must be a number.",          // Message for the error
            false);                             // Site dependent error
    }
    else if (price < 0)
    {
        // If the price is less than zero, report an error.
        eventObj.ReportError(
            eventObj.Site,                      // Node with the error
            "Price must be greater than zero.", // Message for the error
            false);                             // Site dependent error
    }
}

Save the script, and then close MSE.

Now that you have added an OnValidate event handler to your form, you need to preview your form in order to test that the script-based validation works as expected.

To preview a form

In design mode, click Preview Form on the Standard toolbar.
In the Price field, type abc, and then press TAB.
Notice that a red-dashed border appears around the text box, indicating that an error is associated with this field. Move the pointer over the field to display a hint for fixing the error, as shown in the following illustration:

Figure 7. Price field with red-dashed border
In the Price field, type -10, and then press TAB. When you move the pointer over the field to display a hint for fixing the error, a different message is displayed:

Figure 8. Price field with greater than zero message

Conclusion

In this lab, you created simple schema validation, custom data validation using pattern-matching, and script-based validation using the OnValidate event. You used formulas to calculate totals and to construct an e-mail address. You also used conditional formatting to call attention to a text box value. Finally, you used rules to display a message box and to set the value for another field.