Working with Expression Boxes
The final type of control to look at is the expression box, which is used to perform calculations using formulas written as XPath expressions. XPath is a query language for XML documents that can be used to address different parts of XML schemas, so you can select the specific part you want.
This is similar to how spreadsheet formulas are created. If you have two columns of data and want to add them together, you reference them using their cell addresses (for instance, +E4 +F4). In InfoPath, you use the same method to reference parts of your form, but instead of using cell references, you use XPath to describe where these elements can be found on your form. In addition, XPath also provides functions and operators for working with the data held in these elements.
For example, in this section, you are going to be working with an InfoPath form that is a simple commercial invoice. This invoice has a repeating table with data entry objects that can be used to enter the Item Number, Description, Price, Ship Cost, and so on. These objects tie directly back into the underlying data source, which is shown in Figure 7-22.
Figure 7-22: The invoice form and underlying data source
If you want to create a simple expression to add the Price and Ship Cost together, you could simply use the expression shown here:
my:shipping+my:amount
When referencing fields in your data source, you will always use the “my” prefix, which refers to the XML namespace, along with the field name:
my:shipping
or
my:price
You can also refer to groups using the full path:
my:items/my:item/my:price
If this all seems a bit confusing at first, don’t worry! When working with expression fields, you can use the GUI tools provided within InfoPath to select the field or group without having to write the entire expression yourself. The easiest way to learn how to create and modify expressions is to create some of your own, which you will do next.
Inserting Expression Boxes
To insert a new expression into your form, select View | Design Tasks and then click Controls and drag an Expression Box control onto your form. This opens the dialog box shown in the following illustration and allows you to enter an XPath expression to be displayed on your form.
If you need some help creating your XPath expression, you can use the icon to the right of the XPath text box to select a field or group to use as the basis for your expression. The following sections look at some of the most common types of XPath expressions and how they are created and used.
Simple Arithmetic Calculations
Most of the expressions you will write within InfoPath will be simple arithmetic calculations: adding, subtracting, multiplying, and dividing one or more fields or constants. There are five simple arithmetic operators, listed in Table 7-1.
| Operator | Use |
|---|---|
| + | Add two or more fields or constants |
| - | Subtract between two or more fields or constants |
| * | Multiply two or more fields or constants |
| Div | Divide two or more fields or constants |
| Mod | Return the remainder when dividing between two or more fields or constants |
The reason the Description column states “two or more fields or constants” is that you can use expressions to perform calculations by using the fields that appear in your data source, by using constants that you enter (for example, 3.14), or by using both. From the form we have been working with in this section, you could use an expression box to calculate the sales tax for each item on your invoice. If we knew the sales tax to be 10 percent, you could insert an expression such as this into a repeating table:
my:amount*.10
Since you placed this expression in a repeating table, it would be evaluated and displayed for each row in the table. Likewise, if you wanted to add the price of each item together with the shipping costs, you could add the two fields together:
my:amount + my:shipping
You may notice that, when working with expressions, InfoPath sometimes displays “NaN” or “1. #QNAN” as the result of an expression. This is shorthand for “Not A Number” and this message will be displayed when the results of an expression cannot be identified as numeric. To fix this problem, make sure the fields that are used on your expression have default numeric values (for example, 0). To set the default values for a particular field, right-click the field and select Properties. Enter a value in the Default Value text box. This should eliminate the error message and allow you to continue working with your expression.
Using Numeric Functions
In addition to the simple arithmetic operators listed in Table 7-1, InfoPath also has a number of functions that you can use to create formulas, as listed in Table 7-2.
| Function | Use |
|---|---|
| Sum | Sum a column of numeric fields |
| Count | Count the items in a column |
| Round | Round numerical values up to the nearest integer |
To sum a column of numbers that appears in a repeating table or section, you need to insert an expression box outside of the table or section boundaries. In this example, you are going to look at summing the Amount column of your invoice. After you insert an expression box below the repeating table, you can click the icon in the Insert Expression Box dialog box to select the path to use in your expression. In this case, you are summing up the Amount field, so the path would look like this:
my:items/my:item/my:amount
To use any of the functions mentioned earlier, you would need to enter the function name and surround the field reference in parentheses, as shown here:
Sum(my:items/my:item/my:amount)
This would return the sum of the Amount column that appears in the repeating table, so your form might look something like the one shown in Figure 7-23.
Figure 7-23: A sum of column values
The other numeric functions work in the same manner—the Count function can be used to calculate the occurrences of a certain field. You could use this function to count the total number of items shown in the invoice by using the expression shown here:
count(my:items/my:item/my:itemnumber)
The Round function is used to round off numbers to the nearest integer. For example, if you wanted to sum the total shipping costs and then round off to the nearest dollar, you could write an expression like this:
round(sum(my:items/my:item/my:itemnumber)))
 |
Your actual results may be different depending on the formatting you have applied to your expression box. The preceding example assumes that you have formatted your expression box to display a currency format with two decimal places. More information about formatting options is provided in the upcoming “Formatting Expression Boxes” section.
|
If the sum of the shipping costs is $320.56, for example, the expression would round this number up to the nearest whole integer and display $321.00.
Working with String Functions
The last category of XPath functions you’ll look it is for manipulating strings, using the functions listed in Table 7-3.
| Function | Use |
|---|---|
| Concat | Concatenate two or more strings together |
| String-length | Return the length of the string |
| Substring | Return part of a string based on a starting position and length |
The most commonly used function is Concat, which can be used to concatenate two or more strings together. These strings aren’t limited to fields on your form— you could combine fields with string literals that you enter yourself. The following example uses the Concat function to concatenate a First Name field and a Last Name field that appear on a form, with a space in between:
concat(my:firstname, " ", my:lastname)
| Note | Any string literals that you want to concatenate have to be enclosed in double quotes. |
You can also use this technique to concatenate multiple fields and string literals, as in the following example, where the e-mail address is built and displayed based on the pattern of firstname.lastname@company.com:
concat(my:firstname, ".", my:lastname, "@company.com")
In addition to putting strings back together, you can also pull them apart and return parts of a string using the Substring function. To be able to use Substring, you need to imagine that the string itself is an array and that each letter within the string is assigned an index number. For example:
John Smith
12345678910
|
You also may notice that when you are working with expression boxes, the manner in which the results are displayed isn't quite as you expect. The reason is that when you create an expression box, InfoPath has no way of knowing what the output from the expression box will look like—whether it will be a string, currency, number, or something else—so there are no formatting attributes applied to the object when you create it.
You can format expression boxes and the results they contain by right-clicking the box and selecting Properties. On the General tab, you can use the Format As drop-down box to select a generic format type (for instance, Text, Decimal, Whole Number, and so on) or you can click the Format button to open a standard formatting dialog box in which you can select the format for your field by example.
|
Substring can work in two ways. First, you can specify a string and a starting point, and the Substring function will return the part of the string from the starting point to the end. For example, using the preceding string, the following expression would return the substring “Smith”:
substring("John Smith", 6) The other way that Substring works is to specify both the start and end point. The following example specifies to start at the sixth character and continue to the ninth character:
substring("John Smith", 6,9) This would return a substring of “Smit”. You can use this function on fields and string literals, and it is handy to use if you have product codes or other fields that are made up of components (for example, where the first two digits are the department).
| Tip | The formatting options for the different fields are identical to the formatting options for data entry objects, as covered in Chapter 6. |
EAN: 2147483647
Pages: 142