Amal Nagm: Functions in InfoPath

Functions in InfoPath

Author: mety Labels:: Functions in InfoPath

You can use functions in Microsoft Office InfoPath form templates to perform a variety of tasks, such as counting the number of fields or calculating the sum of a column in a repeating table (repeating table: A control on a form that contains other controls in a table format and that repeats as needed. Users can insert multiple rows when filling out the form.). You use functions in formulas (formula: An XPath expression made up of values, fields or groups, functions, and operators. Formulas can be used to calculate mathematical values, display dates and times, and reference fields.) that you enter in the Insert Formula dialog box for controls or fields on your form template.

An InfoPath function is an expression that returns a value that is based on the results of a calculation. You use a function in a formula that you enter in the Insert Formula dialog box. Functions consist of the following three parts:
Name The name of the function. The name usually provides a hint as to the action that the function will perform.
Return value The result of the function.
Arguments The values required by the function to perform the calculation.
A function may require arguments to return a value. If the function requires arguments, you need to know how many arguments are required and the data type of each argument. The function will not work if you supply the wrong number or type of required arguments.
An argument in a function is an XPath (XML Path Language (XPath): A language used to address parts of an XML document. XPath also provides basic facilities for manipulation of strings, numbers, and Booleans.) expression that is a combination of values, functions, and operators that evaluates to a single value. The following illustration shows the relationship between functions, expressions, and formulas.
Diagram showing relationship between formulas and expressions

Diagram showing relationship between formulas and expressions

Date and time functions

addDays

addSeconds

now

today

To see how these functions can be used in a form template, you can download a sample form template that contains fields in the data source that use the date and time functions. To download the sample form template, do the following:

Click the link Date and Time Function Example Template.
In the File Download dialog box, click Save. Note Do not click Open, because doing this will display a warning message and open a form that is based on this form template.
In the Save As dialog box, click Save.
After the file download is complete, right-click the form template and then click Design.

addDays

Returns a new date that is the result of adding the number of days in the second argument to the date in the first argument. The data type of the return value depends on the data type of the first argument.

First argument data type	Data type of return value
Date (date)	Date (date)
Date and Time (dateTime)	Date and Time (dateTime)

Syntax

addDays(argument1, argument2)

Argument	Description
*argument1*	The number of days in the second argument is added to the date in this argument. This argument can be a Date (date) or a Date and Time (dateTime) data type.
*argument2*	The number of days that you want to add to the date in the first argument. This parameter must be a Whole Number (integer) data type. If you use a negative number in this argument, the return value is an earlier date.

Example

You want a field in your form template to contain a date 60 days from today's date. Your form template contains a field named fldToday, with today's date as the default value. To calculate the new date by using this function, use the following formula in the new date field:

addDays(fldToday,60)

addSeconds

Returns a new date and time or time value that is the result of adding the number of seconds in the second argument to the date and time or time value in the first argument. The data type of the return value depends on the data type of the first argument.

First argument data type	Data type of return value
Time (time)	Time (time)
Date (date)	Date and Time (dateTime)

Syntax

addSeconds(argument1, argument2)

Argument	Description
*argument1*	A date and time value or a time value that is either a reference to another field on the form template or the result of a date or time function, such as now() or today(). The number of seconds in the second argument is added to the date in this argument. This argument can be a Date (date) or a Date and Time (dateTime) data type.
*argument2*	The number of seconds that you want to add to the date and time value or time value in the first argument. The field in this parameter must be a Whole Number (integer) data type. If you use a negative number in this argument, the return value is either an earlier date and time or an earlier time.

Example

Your form template contains a field named fldCurrentTime that uses the current time as the default value and a second field that uses the addSeconds function in a formula to display the time 120 seconds from the current time. To calculate the new time value by using this function, enter the following formula in the second field:

addSeconds(fldCurrentTime, 120)

now

Returns the date and time in a Date and Time (dateTime) data type.

Note If you want just the date returned, use the today function.

Syntax

now()

This function does not use arguments.

Example

To get the date and time when a form that is based on your form template was created, use the following formula in a field:

now()

today

Returns the date in a Date (date) data type.

Note If you want both the date and time returned, use the now function.

Syntax

today()

This function does not use arguments.

Example

To get the date when a form that is based on your form template was created, use the following formula in a field:

today()

Field functions

To see how these functions can be used in a form template, you can download a sample form template that contains fields in the data source that use the field functions. To download the sample form template, do the following:

Click the link Field Function Example Template.
In the File Download dialog box, click Save.
Note Do not click Open, because doing this will display a warning message and open a form that is based on this form template.
In the Save As dialog box, click Save.
After the file download is complete, right-click the form template and then click Design.

count

Returns the number of instances that a repeating field or a repeating group occurs in a form.

Syntax

count(argument)

Argument	Description
*argument*	The name of the repeating field or repeating group to count in the form.

Example

You are designing a form template whose forms will be used by suppliers to report their products and inventory. The form template contains a repeating group named grpProduct for the products that are submitted by the supplier. Each group contains information about the product and its inventory level. When a supplier adds a product in a form that is based on this form template, InfoPath creates a new instance of the repeating group.

Your form template has a field in the data source containing the number of products that are submitted by the supplier. To automatically count the number of products submitted by the supplier, which is the number of instances of the repeating group grpProduct, use the following formula in the number of products field:

count(grpProduct)

current

Returns the name of the current field or group in a group. You can use this function to refer to a current row in a repeating table or to a repeating section in a larger expression.

Note This function is not available in the Insert Function dialog box. To use this function, type the function as an XPath expression in the Formula box in the Insert Formula dialog box.

Syntax

current()

This function does not use arguments.

Example

You are designing a form template that suppliers will fill out to report their product inventories. The inventory and other data about each product is stored in the fields in a repeating group that is named grpProduct. The repeating group is bound to a repeating table control in the form template. The data and inventory for each product is shown as a row in the repeating table.

When the supplier selects a row in the repeating table control, you want that row to have a different highlight color to show that the supplier selected a row. To add this functionality to your form template, you use conditional formatting to change the background color of the row that was selected by the user when the following expression is true:

current()/grpProduct

id

Returns the XPath expression for a field or a group whose ID attribute has the value that is specified in the argument. This function searches only field attributes that have the ID data type.

This function does not work in browser-compatible form templates (browser-compatible form template: A form template that is designed in InfoPath by using a specific compatibility mode. A browser-compatible form template can be browser-enabled when it is published to a server running InfoPath Forms Services.).

Syntax

id("argument")

Argument	Description
*argument*	The value of the ID attribute for the field or group. This value must be enclosed in double quotation marks (" ").

Example

You are designing a form template that suppliers will fill out to report their product inventories. Each product has a unique identification number that consists of characters and letters. When the supplier creates a form that is based on your form template, the form contacts a Web Service through a data connection to retrieve the inventories of other suppliers. The Web Service returns the product inventory information in XML format. Infopath then puts this information in several fields in the main data source of the form. One of the fields has a field attribute that has an ID data type.

The supplier needs inventory information from other suppliers to fill out the form. Every supplier who reports their inventory needs a product with an identification number of FC9802.

You want to configure the form template to look for that product by searching for that identification number in the field attributes of each product every time that the form uses the Web Service. You configured a rule that changes the value of a field if the function finds a field attribute that has the specified identification number. The rule uses the following formula to search for the product with the identification number FC9802:

id("FC9802")

last

Returns the value of the last occurrence of the repeating field or field in a repeating group.

Note If you are designing a browser-compatible form template, this function does not appear in the Functions list in the Insert Functions dialog box.

Syntax

field name[last()]

Although the function itself does not have any required parameters, you can use this function as a predicate for a specified field name.

Example

You are designing a form template that suppliers will fill out to report their product inventories. The form template has a data connection to a Web service that has a method that checks if the product information for all the products from a supplier have been completed. The method needs the last product in the list of products that was submitted by the supplier.

The product information is stored in different fields of a repeating group that is named grpProduct. When a supplier adds a product to the form, InfoPath adds a new repeating group called grpProduct. If the supplier reports on several products, the data source of the form will contain multiple instances of the grpProduct repeating group.

To make sure that you submit the correct information to the Web service, you add a field that will contain information about the last product in the group of products from the supplier. To configure that field to automatically retrieve the last product, use the following formula as the default value for the last product field:

grpProduct[last()]

local-name

Returns the name of the field without the namespace or the last name of the XPath expression. If the argument is a repeating field or repeating group, the function returns the value of the first repeating field or repeating group.

When a user fills out a form based on a browser-enabled form template, a formula that uses this function sends form data to the server to get the return value for this function.

Syntax

local-name(argument)

Argument	Description
*argument*	The name of the field whose local name attribute you want to return.

Example

You are designing a form template that is used for permit applications. The form template has a data connection to a method in a Web service. This method requires the value of the local-name attribute of the field for the license number of the contractor. The name of the license number field is fldLicenseNumber.

You have a field that will contain the value of the local name attribute. To automatically retrieve the value of the local name attribute of the license number field, use the following formula as the default value for the field that will contain the value of the local name attribute:

local-name(fldLicenseNumber)

name

Returns the expanded name of the field name that is specified in the argument. The expanded name contains the existing namespace prefix and the name of the specified field or group. If the argument is a repeating field or repeating group, the function returns the name and prefix of the first repeating field or repeating group in the form.

When a user fills out a form based on a browser-enabled form template, a formula that uses this function sends form data to the server to get the return value for this function.

Syntax

name(argument)

Argument	Description
*argument*	The name of the field whose namespace prefix and name you want to return.

Example

You are designing a form template that is used for permit applications. The form template has a data connection to a method in a Web service. This method requires the name attribute of the field for the contractor's license number. The name of the license number field is fldLicenseNumber.

You have a field that will contain the value of the name attribute. To automatically retrieve the value of the name attribute, use the following formula as the default value for the field:

name(fldLicenseNumber)

namespace-uri

Returns the full namespace Uniform Resource Identifier (URI) (Uniform Resource Identifier (URI): A character string used to identify a resource on the Internet by type and location.) of the argument. If the argument is a set of fields or groups, the function returns the namespace of the first field or group in the form.

When a user fills out a form based on a browser-enabled form template, a formula that uses this function sends form data to the server to get the return value for this function.

Syntax

namespace-uri(argument)

Argument	Description
*argument*	The name of the field whose namespace URI you want to retrive.

Example

You are designing a form template that is used for permit applications. The form template submits data to a Web service. To process the data from a formbased on your form template, the Web service requires the namespace URI from a specific element field in the data source. The field in your form template that contains the namespace URI data is named fldComplete.

You have a field that will contain the namespace URI of the element field. To automatically retrieve the namespace URI, use the following formula as the default value for the field that will contain the namespace URI:

namespace-uri(fldComplete)

position

Returns the index or position of the repeating field or repeating group with respect to the other repeating fields or repeating groups in the same group.

Note This function does not work in browser-compatible form templates.

Syntax

position()

This function does not use arguments.

Example

You are designing a form template that suppliers will fill out to report their product inventories. The product information is stored in fields in a repeating group. The repeating group is bound to a repeating table, which allows a supplier to add new or revise existing product information.

In the first column of the repeating table, you want to display the row number in an expression box. To automatically display the row number when the supplier adds a new product to the repeating table, use the following formula as the default value for the expression box:

position()

Math functions

Note Formulas that contain math functions usually require arguments that are integers or decimal values. To ensure that arguments are integer or decimal values instead of null values, select the Treat blank values as zero check box in the Advanced category in the Form Options dialog box (Tools menu).

avg

max

min

not

number

round

sum

true

avg

Returns the average value of all the values in a repeating field. The repeating field must be a numeric data type and be included in a group.

Syntax

avg(argument)

Argument	Description
*argument*	The names of the repeating field in the group for which you want to calculate the average value.

Example

You are designing a form template whose forms will be used by suppliers to report their products and inventories. The form template contains a repeating field named fldPrice, which is in a repeating group that contains the data about each product that is sold by the supplier. The group is bound to a repeating table control.

You have a field in the form template that will contain the average price of all the products that are sold by that supplier. To calculate the average price, use the following formula in the field for the average price:

avg(fldPrice)

A supplier creates a form based on your form template and reports the products and prices in the following table:

Products	Price
LL Mountain Seat Assembly	$98.77
ML Mountain Seat Assembly	$108.99
HL Mountain Seat Assembly	$145.87
LL Road Seat Assembly	$98.77
ML Road Seat Assembly	$108.99
HL Road Seat Assembly	$145.87

The return value for this function is $117.88.

boolean

Returns true if the repeating group or repeating field in the argument contains at least one field or group. Returns false if the repeating field or group in the argument does not contain at least one field or group.

Syntax

boolean(argument)

Argument	Description
*argument*	A repeating field or repeating group to be checked by this function.

Example

You are designing a form template that suppliers will use to report their product inventories. The product information is stored in different fields in a repeating group that is named grpProduct. Each repeating group contains fields that have information about each product. If the supplier provides information about seven products, the form will contain seven repeating groups.

The repeating group is bound to a repeating table control, which enables the supplier to add new or revise existing product information. The supplier can add or delete rows from the repeating table.

You want to display a dialog box if the user removes all the rows in the repeating table. You add a field to the data source that contains the word "true" if the repeating table control has at least one row and the word "false" if the repeating table control does not have any rows.

You configure a rule (rule: A condition or action, or a set of conditions or actions, that automatically performs tasks based on events and values in the form.) in the repeating table control to display a dialog box if the value in the field is false. To automatically determine whether the repeating table contains at least one row, use the following formula as the default value for the field the contains the word "true" or "false":

boolean(grpProduct)

ceiling

Returns the smallest integer that is equal to or larger than the value in the field that is specified in the argument. A noninteger value is rounded to the next highest integer value.

Syntax

ceiling(argument)

Argument	Description
*argument*	The name of the field, which has a numeric value.

Example

You are designing an expense report form template for your company. The expenses are submitted to a Web service that has a parameter that accepts only integer numbers. The expense amount that is submitted to this parameter must be rounded to the higher integer value. The expense amount is stored in a field that is named fldExpenseAmount, and the integer value is stored in another field. To calculate the higher integer value, use the following formula as the default value for the other field:

ceiling(fldExpenseAmount)

A user creates a form based on your form template. When the user enters 145.87 in the fldExpenseAmount field, the value in the field that contains the formula is 146.

eval

Returns the result of applying the specified expression in the second argument to the field or group that is specified in the first argument.

Syntax

eval(argument1, argument2 )

Argument	Description
*argument1*	The name of the field or group whose values will be evaluated by the expression in the second argument.
*argument2*	The expression that will be applied to the first argument. The expression can either be an XPath function or an expression that is enclosed in double quotation marks (" ").

Example

You are designing an expense report form template for your company. The form template contains a field named fldTotal that contains an expense amount. The fldTotal field is part of a repeating group named grpExpenses. Another field is bound to a text box that contains the sum of all the expenses. To display the sum of all the expenses as the user enters an expense amount, use the following formula in the total expense field:

eval(grpExpenses,sum(fldTotal))

false

Returns the Boolean value false. Use this function as part of a series of rules that is run when a condition does not exist.

Syntax

false()

This function does not use arguments.

Example

You are designing a form template whose forms will be used by contractors to apply for building permits. The form template has two sections: one section for contact information and another section for information related to the building permit.

When the building department used paper forms to process building permit applications, permits were often delayed because contractors did not completely fill out the contact information section. To prevent this, you design a form template that requires that contractors completely fill out the contact information section before they can fill out the building permit section.

To accomplish this, you create a rule to set the value of a Boolean field to false if any one of the fields in the contact information section is blank. A Boolean value field can have either a Boolean true value or a Boolean false value. You also configure the conditional formatting to hide the section control containing the building permit information if the Boolean field value is false.

To hide the section control that contains the fields for the building permit information, configure a rule to run this function in the Boolean field if any one of the fields in the contact information section are blank.

floor

Returns the smallest integer that is equal to or less than the value in the field specified in the argument. A noninteger value is rounded to the next lowest integer value.

Syntax

floor(argument)

Argument	Description
*argument*	The name of the field, which has a numeric value.

Example

You are designing an expense report form template for your company. The expenses are submitted to a Web service that uses a parameter that accepts only integer numbers. The expense amount that is submitted to this parameter must be rounded to the lower integer value. The expense amount is stored in a field that is named fldExpenseAmount, and the integer value is stored in another field. To calculate the lower integer value, use the following formula as the default value for the other field:

floor(fldExpenseAmount)

A user creates a form based on your form template. When the user enters 145.87 in the fldExpenseAmount field, the value in the field that contains the formula is 145.

max

Returns the highest value in a group of repeating fields or a field in a repeating group.

Syntax

max(argument)

Argument	Description
*argument*	A repeating field in a group or a field in a repeating group for which you want to find the highest value.

Example

You are designing a form template whose forms will be used by suppliers to report their product inventories. The form template contains a repeating group that includes several fields that contain data about the suppliers' products. A field named fldPrice in the repeating group contains the price of a product.

Another field in the data source of the form template will contain the highest price of all the products sold by that supplier. To return the highest price, use the following formula in the highest price field:

max(fldPrice)

A supplier creates a form based on your form template and reports the products and prices in the following table:

Products	Price
LL Mountain Seat Assembly	$98.77
ML Mountain Seat Assembly	$108.99
HL Mountain Seat Assembly	$145.87
LL Road Seat Assembly	$98.77
ML Road Seat Assembly	$108.99
HL Road Seat Assembly	$145.87

The return value for this function is $145.87, which is the highest price in the Price column.

min

Returns the lowest value in a group of repeating fields or a field in a repeating group.

Syntax

min(argument)

Argument	Description
*argument*	A repeating field in a group or a field in a repeating group for which you want to find the highest value.

Example

You are designing a form template whose forms will be used by suppliers to report their product inventories. The form template contains a repeating group that includes several fields that contain data about the suppliers products. A field named fldPrice in the repeating group contains the price of a product.

Another field in the data source of the form template will contain the lowest price of all the products sold by that supplier. To return the lowest price, use the following formula in the lowest price field:

min(fldPrice)

A supplier creates a form based on your form template and reports the products and prices in the following table:

Products	Price
LL Mountain Seat Assembly	$98.77
ML Mountain Seat Assembly	$108.99
HL Mountain Seat Assembly	$145.87
LL Road Seat Assembly	$98.77
ML Road Seat Assembly	$108.99
HL Road Seat Assembly	$145.87

The return value for this function is $98.77, which is the lowest price in the Price column.

not

Returns the Boolean value true if the Boolean field specified in the argument contains the Boolean value false and returns the Boolean value false if the Boolean field specified in the argument contains the Boolean value true.

You can use this function as an alternative to doing either of the following:

Setting the value of a field to the Boolean value true by using the true() function if the argument contains the Boolean value false.
Setting the field value to the Boolean value false by using the false() function if the argument contains the Boolean value true.

Syntax

not(argument)

Argument	Description
*argument*	A field with a Boolean data type.

Example

You are designing a form template whose forms will be used by contractors to apply for building permits. The form template has one section for contact information and another section for information related to the building permit.

When paper forms were used to process building permit applications, permits were often delayed because contractors did not completely fill out the contact information section. To prevent incomplete forms from being submitted, you design a form template that requires contractors to completely fill out the contact information section before they can begin filling out the building permit section.

To accomplish this, you create a rule that sets the value of a Boolean field to false if any one of the fields in the contact information section is left blank. A Boolean value field can have either the Boolean value true or the Boolean value false. To set this rule, you use the following formula to set the Boolean field to false:

not(true())

You also use the same formula to hide the section control that contains the building permit information if the Boolean field value is false.

To hide the section control that contains the fields for the building permit information, configure a rule to run this function with the true function in the argument if any one of the fields in the contact information section are left blank.

number

Returns the result of converting the value of the field in the argument into a number. The function returns NaN if the value in the argument cannot be converted to a number.

Syntax

number(argument)

Argument	Description
*argument*	The field with a value to convert into a number.

Example

You are designing a form template whose forms will be used by contractors to apply for building permits. The form template contains a section where the contractor can enter his business address. To verify that the contractor is entering a valid address, you use a data connection to a Web service that can verify the address. If the address is verified, the contractor can submit the form to a SQL database. The SQL database uses a text field for both the address number and the street name. The Web service requires that the address number be a numeric data type and that the street name be a text data type.

To submit data to both the Web service and the SQL database, the address needs to be stored as two different data types:

To submit the address to the Web service, the address number must be a numeric data type and the street name must be a text data type.
To submit the address to the SQL database, both the address number and the street name must be a text data type.

You also want the contractor to enter his address only once. To convert the address to the correct data types and help ensure that the contractor enters his address only once, the form template contains a field named fldAddressNumber for entering the address number and another field for entering the street name. Both fields are configured as text data types.

In order to submit the address number to the Web service, you need to convert the data in the fldAddressNumber field (stored as a text data type) to a numeric data type. The value of the address number converted to a numeric data type is stored in another field that is configured to store numeric data types.

To convert the address number from a text data type to a numeric data type, use the following formula as the default value for the fldAddressNumber field:

number(fldAddressNumber)

nz

Returns zero if the field specified in the argument is blank, or returns the value in the field that is specified in the argument.

Syntax

nz(argument)

Argument	Description
*argument*	The field that you want to check for a value.

Example

You are designing a form template that suppliers will fill out to report their product inventories. The supplier will submit their product inventories to a Web Service through a form based on your form template. The method of the Web Service requires that all elements that contain numeric data have a numeric value. The Web Service rejects a form that contains an empty numeric element.

Your form template contains a field named fldAvailability that contains a number corresponding to the supplier's ability to provide this product. The supplier can enter a number in this field. To help make sure that the supplier can submit his form based on your form template to the Web service, use the following formula as the default value for this field:

nz(fldAvailability)

If the supplier enters a number in this field, the number is submitted to the Web service. If the supplier does not enter a number in this field, the formula automatically enters zero as the value for this field.

round

Returns a number that is rounded to an integer value. If the noninteger value is exactly halfway between two rounded integer values, the return value is the next largest integer value.

Syntax

round(argument)

Argument	Description
*argument*	The field containing the number that will be rounded by using this formula.

Example

You are designing a form template whose forms will be used by suppliers to report their product inventories. The form template contains a repeating group that includes several fields that contains data about the suppliers' products. One of the fields, called fldPrice, in the repeating group contains the price of a product.

The form template has a submit data connection to a Web service. The Web service method requires that each price be rounded to an integer value. To send the appropriate value to the Web service method, you add a repeating field to the data source. This field uses the following formula to round the price in the fldPrice field to an integer value:

round(fldPrice)

A supplier creates a form based on your form template and reports the products and prices in the following table. The table shows the results of the formula for each price:

Products	Price	Return value
LL Mountain Seat Assembly	$98.77	99
ML Mountain Seat Assembly	$108.49	108
HL Mountain Seat Assembly	$145.50	146
LL Road Seat Assembly	$98.77	99
ML Road Seat Assembly	$108.99	109
HL Road Seat Assembly	$145.47	145

sum

Returns the sum of the values of a field in a repeating group or a repeating field in a group.

Syntax

sum(argument)

Argument	Description
*argument*	The name of a field in a repeating group or a repeating field in a group whose values will be added. To add fields from two different groups, use the union operator () to separate the arguments. For example: *sum( field name 1* field name 2).**

Example

You are designing an expense report form template. The form template has a group that contains expense items. The amount of each item is stored in a field that is named fldExpenseAmount. The group is bound to a repeating table that displays each expense item as a row. The form template contains an expression box control that displays the total expenses. To show the total expenses, the expression box control contains the following formula:

sum(fldExpenseAmount)

A user creates a form based on your form template and reports the following expenses:

Description	Price
Airline tickets	$547.98
Hotel	$236.46
Meals	$198.77

The function returns 983.21 as the total of the expenses.

true

Returns the Boolean value true. Use this function as a part of a series of rules that is run when a condition that is specified in a rule is met.

Syntax

true()

This function does not use arguments.

Example

You are designing a form template whose forms will be used by contractors to apply for building permits. The form template contains one section for contact information and another section for information related to the building permit.

To accomplish this, you create a rule that sets the value of a Boolean field to true if all the fields in the contact information section contain data. A Boolean value field can have either the Boolean value true or false. If the Boolean field value is true, the section control that contains the building permit information is displayed.

To show the section control that contains the building permit information, you configure a rule to run this function in the Boolean field if all the fields in the contact information section contain data.

Text functions

concat

Returns text that is the result of combining the text specified in two or more arguments. Each argument can be either a specified field or text enclosed in double quotation marks (" ").

Syntax

concat(argument1, argument2, ...)

Argument	Description
*argument1*	A field that contains text to be combined into a single line of text with the text in argument2. Separate arguments with a comma.
*argument2*	A field that contains text to be combined into a single line of text with the text in argument1. Separate arguments with a comma.
*...*	One or more additional arguments that refer to fields containing text to be combined with the first two arguments. Separate arguments with a comma.

Example

You are designing a form template that contains a field named fldFirstName and a field named fldLastName. A third field contains the following formula:

concat("This form was filled out by ", fldFirstName, " ", fldLastName, ".")

A user creates a form based on your form template and enters the text Robin in the fldFirstName field and Counts in the fldLastName field. The function returns the text This form was filled out by Robin Counts. in the third field.

contains

Returns true if the field specified in the first argument contains the text specified in the second argument. Returns false if the text in the second argument is not contained in the field specified in the first argument.

Syntax

contains(argument1, argument2)

Argument	Description
*argument1*	The field that contains the text to be searched.
*argument2*	The field that contains the text or text enclosed in double quotation marks (" ") to search for in the first argument.

Example

You are designing a form template that contains a field named fldFindText, which is bound to a text box control. The form template also contains a field that is named fldText, which is bound to another text box control, and a third field bound to a text box that has the following formula as the default value:

contains(fldText,fldFindText)

A user creates a form based on your form template and enters the text This form was created from an InfoPath form template. in the text box control that is bound to the fldText field. The following table shows the function result displayed in the text box control that is bound to the third field for each word entered by the user:

fldFindText value	Function result
InfoPath	True
form	True
document	False

normalize-space

Returns text with all leading, trailing, and repeating blank spaces removed.

When a user fills out a form based on a browser-enabled form template, a formula that uses this function sends form data to the server to get the return value for this function.

Note In a field that has a text data type, leading, trailing, and repeating blank spaces are automatically removed from the value.

Syntax

normalize-space(argument)

Argument	Description
*argument*	The text that contains the leading, trailing, or repeating blank spaces that you want to remove. Enclose the text in double quotation marks (" ").

Example

You design a form template that contains a field named fldText (which is bound to a text box control) and another field that has the following formula as the default value:

normalize-space(fldText)

A user creates a form based on your form template and enters the text This form was created from an InfoPath form template . in the fldText field (the text contains three leading spaces and four repeating spaces in the middle of the string). After you run the normalize-space function on the text that contains the leading and repeating spaces, the text in the field is displayed as This form was created from an InfoPath form template.

starts-with

Returns true if the text in the field specified in the first argument starts with the text specified in the second argument. Returns false if the text in the second argument does not appear at the start of the text in the first argument.

Syntax

starts-with(argument1, argument2)

Argument	Description
*argument1*	The name of the field that contains the text to be searched. Separate arguments by a comma.
*argument2*	The text to be searched in the beginning of the field specified in the first argument. This argument can be either a field or text that is enclosed in double quotation marks (" ").

Example

You are designing a form template that contains a field named fldText, which is bound to a text box control. In addition, the form template contains a second field named fldFindText (which is bound to another text box control) and a third field that has the following formula as the default value:

starts-with(fldText, fldFindText)

A user creates a form based on your form template and enters the text This form was created from an InfoPath form template. in the fldText field. The value for the third field is set according to the value in the fldFindText field. The following table shows the function result displayed in the text box control that is bound to the third field for each word entered by the user:

fldFindText value	Function result
The	False
form	False
This	True

string

Returns the value of the field that is specified in the argument as text.

Syntax

string(argument)

Argument	Description
*argument*	The field that contains the value to convert to text.

Example

You are designing a form template whose forms will be used by contractors to apply for building permits. The form template contains a section in which the contractor can enter his business address. To verify that the contractor is entering a valid address, the form template has a data connection to a Web service that can verify the address. If the address is verified, the contractor can submit the form to a SQL database. The SQL database uses a text field for the street address. The Web service requires that the address number be a numeric data type and that the street name be a text data type.

To submit data to both the Web service and the SQL database, the address needs to be stored in two different data types:

To submit the address to the Web service, the address number must be a numeric data type.
To submit the address to the SQL database, both the address number and the street name must be a text data type.

You also want the contractor to enter his address only once. To convert the address to the correct data types and help ensure that the contractor enters his address only once, the form contains a field for entering the address number that is named fldAddressNumber and another field for entering the street name. The fldAddressNumber field is a numeric data type and the street name field is a text data type.

In order to submit the complete address (both the address number and the street name) to the SQL database, you need to combine the values in the fldAddressNumber field and the street name field into one value that is a text data type. You first need to convert the numeric data in the fldAddressNumber field, which stores the data as a numeric data type, to a text data type. You add a field to the data source that will contain the complete address as a text data type.

To convert the text data in the fldAddressNumber field to a text data type so that you can use the Web Service, you configure another field that will contain the address number as a text data type. To convert the address number from a numeric data type to a text data type, use the following formula as the default value for this field:

string(fldAddressNumber)

string-length

Returns the number of characters in the field that are specified in the argument.

Syntax

string-length(argument)

Argument	Description
*argument*	The field whose value is the text that you want to count.

Example

You are designing a form template that contains a field named fldText, which is bound to a text box control. Your form template includes a second field, which is bound to another text box control, and the field has the following formula as its default value:

string-length(fldText)

A user creates a form based on your form template, and then enters text in a text box that is bound to the fldText field.

For each string that is entered in the text box, the function returns the length of the string. The function result appears in the text box control that is bound to the field containing the function.

The following table shows the function result for these words:

fldText value	Function result
The	3
InfoPath	8
form	4
An InfoPath form	16

substring

Returns all the characters from the position specified in the second argument up to the number of characters specified in the third argument.

Syntax

substring(argument1, argument2, argument3)

Argument	Description
*argument1*	A field with a text data type or text that is enclosed in double quotation marks (" "). The function searches this text and returns all the characters from the position specified in the second argument up to the number of characters specified in the third argument or to the end of the text, which ever occurs first. Separate arguments with a comma.
*argument2*	The starting position of the text to retrieve from the first argument. This argument must be a whole number or a reference to a field configured to store whole number (integer) data types. Separate arguments with a comma.
*argument3*	The number of characters that you want to retrieve, beginning at the starting position specified in the second argument. This argument must be a whole number or a reference to a field configured to store whole number (integer) data types.

Example

You are designing a form template that contains the following fields and controls:

A field named fldText that is bound to a text box control. This field contains the text that the function searches.
A field named fldStartingPosition that is bound to a text box control. This field is configured as a whole number data type. The field contains the starting position for the function.
A field named fldNumberOfCharacters that is bound to a text box control. This field is configured as a whole number data type. The field contains the number of characters that the function returns.
An expression box control that shows the results of the function. The expression box control contains the following formula:
```
substring(fldText,fldStartingPosition,fldNumberOfCharacters)
```

A user creates a form based on your form template and enters the text This form was created from an InfoPath form template. in the fldText field. The following table shows the text that is displayed in the expression box control when the user enters the following values:

Starting position	Number of characters	Function result
4	16	s form was creat
0	10	This form
20	23	ed from an InfoPath for

substring-after

Returns the text in the first argument that follows the text specified in the second argument. The text in the second argument is not included in the return text.

Syntax

substring-after(argument1, argument2)

Argument	Description
*argument1*	A field with a text data type or text that is enclosed in double quotation marks (" "). The function searches the text in this argument, and then returns all the characters that follow the text in the second argument. Separate arguments with a comma.
*argument2*	The text to search in the text of the first argument. The text can either be the value in the field with a text data type or text that is enclosed in double quotation marks (" "). The function searches the text in the first argument for this text, and then returns all the characters that follow the text in this argument.

Example

You are designing a form template that contains the following fields and controls:

A field named fldText that is bound to a text box control. This field contains the text that the function searches.
A field named fldSubstringText that is bound to a text box control. This field contains the text that is the second argument of the function.
An expression box control that shows the results of the function. The expression box control contains the following formula:
```
substring-after(fldText,fldSubstringText)
```

Substring Text	Function result
created	from an InfoPath form template.
form	was created from an InfoPath form template.
InfoPath	form template.

substring-before

Returns the text in the first argument that precedes the text specified in the second argument. The text in the second argument is not included in the return text.

Syntax

substring-before(argument1, argument2)

Argument	Description
*argument1*	A field with a text data type or text that is enclosed in double quotation marks (" "). The function searches the text in this argument and then returns all the characters before the text in the second argument. Separate arguments with a comma.
*argument2*	The text to search in the text of the first argument. The text can either be the value in the field with a text data type or text that is enclosed in double quotation marks (" "). The function searches the text in the first argument for this text and then returns all the characters before the text in this argument.

Example

You are designing a form template with the following fields and controls:

A field named fldText that is bound to a text box control. This field contains the text that the function searches.
A field named fldSubstringText that is bound to a text box control. This field contains the text that is the second argument of the function.
An expression box control that shows the results of the function. The expression box control contains the following formula:
```
substring-before(fldText,fldSubstringText)
```

Substring text	Function result
created	This form was
form	This
InfoPath	This form was created from an

translate

Returns a modified version of the text in the field that is specified in the first argument. The function replaces each character specified in the second argument with the character specified in the third argument.

Syntax

translate(argument1, argument2, argument3)

Argument	Description
*argument1*	The name of the field that contains the text whose characters will be replaced. Separate arguments with a comma.
*argument2*	A character or the value of a field with a text data type that will be replaced by the characters in the third argument. Separate arguments with a comma.
*argument3*	A character or the value of a field with a text data type that will replace every instance of the character in the second argument.

Example

You are designing a form template whose forms will be used by contractors to apply for building permits. The form template contains a section in which the contractor can enter his business address. The section contains a field named fldStreetAddress that is bound to a text box control in which the contractor can enter his street address.

To verify that the contractor is entering a valid address, you have a data connection to a Web service that can verify the address. The Web service requires lowercase characters in the address.

To convert any uppercase character in the street address to lowercase, you add another field to the data source to contain the street address that is converted to lowercase characters. The default value of this field contains the following formula:

translate(fldStreetAddress,ABCDEFGHIJKLMNOPQRSTUVWXYZ,abcdefghijklmnopqrstuvwxyz)

The following table shows the result of using this function on typical addresses:

Address	Function result
123 Elm Street	123 elm street
4427 Wallingford Avenue North	4427 wallingford avenue north
9204 Woodlawn Boulevard	9204 woodlawn boulevard

The userName function

The userName function belongs to none of the categories listed in the Insert Function dialog box. To locate the userName function in the Functions list, click All in the Categories list.

userName

Returns the e-mail alias of the user that is filling out the form. The e-mail alias is retrieved from the name that the user entered when he logged into the network.

Note If the user is filling out a form in a Web browser, the e-mail alias is retrieved from Microsoft Windows SharePoint Services 3.0.

Syntax

userName()

This function does not use arguments.

Example

You are designing an expense report form template for your company. Your credit card company sends records of your employees' expenses electronically to a database that your company maintains. To retrieve the expense records for an employee, the database needs the e-mail alias of the employee that is filling out a form that is based on your form template.

You add a field to the data source of the form template to store the e-mail alias of the employee. You also create a query data connection that uses the value of this field in a query to retrieve the expenses.

To make sure that the employee filling out the expense report uses the correct alias, you bind the field to an expression box control. To display the e-mail alias of the user who creates a form based on this form template, use the following formula as the default value of a field bound to the expression box control:

userName()

0 comments |