Formulas enable you to get more out of the rules that you create than using basic, pre-defined rules. For example, formulas are used for things such as calculating the current or future date or even concatenating text.
In this article
Overview
A formula is an XPath (XML Path Language) expression that consists of values, fields or groups, functions, and operators that are used to calculate and display other values. You can use a formula to create a new value for a field and display that value in a control that is bound to that field. Formulas can be used as follows:
Calculate a mathematical value based on values that you specified when designing the template or based on values that a user enters when filling out the form template.
Run a rule based on a value that is calculated using a formula.
Display read-only text that is calculated from other controls or display the value of another control.
A function is an expression that returns a value based on the results of a calculation. The values used in the functions are called arguments. You can use the standard XPath 1.0 functions that are included in InfoPath, in addition to some InfoPath-specific functions. Functions consist of the following three parts:
-
Name The name of the function usually provides a hint as to the action that the function will perform.
-
Return value The result of the function.
-
Arguments The values used 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.
The following illustration shows the relationship between functions, expressions, and formulas.
Add a calculated value control
You can use a calculated value control to display read-only text, display the value of another control on the form, and create formulas that are based on XPath expressions. For example, if you are developing an expenses tracking form template, you can add a calculated value control to display the total of the expenses that have been entered.
Note: A calculated value control only displays data and does not store it, so you should use a calculated value control only if you do not want to save its value or reference it in another formula.
-
On the form template, place the cursor where you want to insert the control.
-
On the Home tab, in the Controls group, click the calculated value control.
-
In the Insert Calculated Value dialog box, do one of the following:
-
To type text or an XPath expression directly into the calculated value control, type in the XPath box.
-
To insert a formula, click Edit Formula
-
To insert a field or group into the formula, click Insert Field or Group, click the field or group in the Select a Field or Group dialog box, and then click OK.
-
To insert a function into the formula, click Insert Function, select the function in the Insert Function dialog box, and then click OK.
If the function requires parameters, select the function in the Insert Function dialog box, click OK, and then, in the Formula text, double-click where indicated and select the desired parameter before clicking OK.
-
To insert a value or mathematical operator into the formula, type the value or symbol for the mathematical operation in the Formula box.
Mathematical operators include add (+), subtract (-), multiply (*), and divide (/).
Mathematical formulas usually depend on integer or decimal values as arguments. To avoid blank values in your formula, on the File tab, click Form Options then select Advanced and make sure Treat blank values as zero is selected.
-
-
Add a formula to a control
While you can use a calculated value control to display the results of a formula, you are not limited to using a calculated value control. You can also use a formula to set the default value of other controls, such as text boxes. There are two ways that you can set the default value of a control. You can either use a static default value, by simply entering the desired value into the Value field of the Field or Group Properties dialog box, or you can use a formula to set the default value of a field based on other values in the form. Do the following to set the default value of a field based on a formula:
-
Select the control that you want to add the formula to then, under Control Tools, on the Properties tab, in the Properties group, click Default Value.
-
Click Insert Formula
-
Enter the desired formula in the Formula text box, making use of the following, as necessary:
-
To insert a field or group into the formula, click Insert Field or Group, click the field or group in the Select a Field or Group dialog box, and then click OK.
-
To insert a function into the formula, click Insert Function, select the function in the Insert Function dialog box, and then click OK.
If the function requires parameters, select the function in the Insert Function dialog box, click OK, and then, in the Formula text, double-click where indicated and select the desired parameter before clicking OK.
-
To insert a value or mathematical operator into the formula, type the value or the symbol for the mathematical operation in the Formula box.
Mathematical operators include add (+), subtract (-), multiply (*), and divide (/).
Tip: Mathematical formulas usually depend on integer or decimal values as arguments. To avoid blank values in your formula, on the File tab, click Form Options, and then select Advanced. Make sure that Treat blank values as zero is selected.
-
-
To edit the formula as an XPath formula, select the Edit XPath (advanced) check box, which will change the formula to the XPath expression version of the formula.
-
To check the formula for the correct syntax, click the Verify Formula button. If your formula contains errors:
Click Show Details in the Microsoft InfoPath dialog box to see the errors in the formula. The following are some suggestions for resolving these errors:
-
If you are using a function in your formula, verify that you are using the correct arguments for the function. Some functions require fields or groups while other functions require specified values as arguments.
-
If necessary, delete and retype your formula to make sure that it is correctly typed.
-
Click OK.
-
If you want to update the default value of the field when the values that it is based on change, select the Refresh value when formula is recalculated check box. If you do this, then click OK, and, under Control Tools, on the Properties tab, in the Properties group, click Control Properties. Click the Display tab, and then select the Read-only check box.
Use a formula in a rule
It is possible, when specifying conditions in validation, formatting, or action rules, to use a formula to control the condition. You can do this by selecting Use a formula from the third drop-down list on the Condition dialog box when assigning a rule to a control or the form template.
Date and time functions
addDays
Adds days to a date or date and time.
Syntax
addDays(date, days)
|
Argument |
Description |
|
date |
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. |
|
days |
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
Adds seconds to a time or date and time.
Syntax
addSeconds(time, seconds)
|
Argument |
Description |
|
time |
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 time in this argument. This argument can be a Date (date) or a Date and Time (dateTime) data type. If it is a Time data type then a Time data type will be returned. If it is a Date data type then a Date and Time data type will be returned. |
|
seconds |
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 current system date and time.
If you want just the current 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 current system date. For SharePoint list date fields, use the now() function.
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
count
Counts the number of instances of a field or group.
Syntax
count(field)
|
Argument |
Description |
|
field |
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)
last
Returns the last occurrence of a repeating field or group.
Note: If you are designing a browser-compatible form template, this function does not appear in the Functions list in the Insert Function 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 local name of a field or group.
Notes:
-
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 Web browser form, a formula that uses this function sends form data to the server to get the return value for this function.
Syntax
local-name(field)
|
Argument |
Description |
|
field |
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 name and namespace prefix of a field or group.
Notes:
-
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(field)
|
Argument |
Description |
|
field |
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 of a field or group.
Notes:
-
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 Web browser form, a formula that uses this function sends form data to the server to get the return value for this function.
Syntax
namespace-uri(field)
|
Argument |
Description |
|
field |
The name of the field whose namespace URI you want to retrieve. |
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 form based 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 a field or group with respect to the other fields or groups in the same parent group.
Note: This function is not available in Web browser form templates.
Syntax
position()
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 a text 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 text box:
position()
Math functions
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, click File > Form Options > Advanced, and then make sure the Treat blank values as zero check box is selected.
avg
Calculates the average of the numerical values in a field that is in a repeating group.
Note: The repeating field must be a numeric data type and be included in a group.
Syntax
avg(field)
|
Argument |
Description |
|
field |
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)
boolean
Returns true if a field or group exists. Otherwise, returns false.
This will return true even if a control that was associated with the field has been deleted, but the field still exists.
Syntax
boolean(field)
|
Argument |
Description |
|
field |
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 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 contains the word "true" or "false":
boolean(grpProduct)
ceiling
Rounds a number up to the nearest integer.
Syntax
ceiling(number)
|
Argument |
Description |
|
number |
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)
If a user creates a form based on your form template and enters 145.87 in the fldExpenseAmount field, the value in the field that contains the formula will be 146.
eval
Returns the values of a field or group. The second argument defines the expression to calculate for the field or group. Usually, the eval function is nested within a function that operates on a field or group, such as the sum or avg.
Syntax
eval(field, expression)
|
Argument |
Description |
|
field |
The name of the field or group whose values will be evaluated by the expression in the second argument. |
|
expression |
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 false.
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 filling out this form, contractors will need to 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
Rounds a number down to the nearest integer.
Syntax
floor(number)
|
Argument |
Description |
|
number |
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)
max
Returns the largest number in a field or group.
Syntax
max(field)
|
Argument |
Description |
|
field |
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)
min
Returns the smallest number in a field or group.
Syntax
min(field)
|
Argument |
Description |
|
field |
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)
not
Returns true if a Boolean value is false or null. Returns false if the Boolean value is true or not null.
Syntax
not(boolean_value)
|
Argument |
Description |
|
boolean_value |
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 filling out this form, contractors will need to completely fill out the contact information section before they can fill 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. You then use the same formula to hide the section control that contains the building permit information if the Boolean field value is false. To set this rule, you use the following formula to set the Boolean field to false:
not(true())
number
Converts a value to a number.
The function returns NaN if the value in the argument cannot be converted to a number.
Syntax
number(value)
ArgumentDescription
valueThe 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 a field or group with all blank fields replaced with zero (0).
Syntax
nz(field)
|
Argument |
Description |
|
field |
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, and automatically set the value of the field to zero if the supplier does not enter a number, use the following formula as the default value for this field:
nz(fldAvailability)
round
Rounds a number to the nearest integer.
If the non-integer value is exactly halfway between two rounded integer values, the return value is the next largest integer value.
Syntax
round(number)
|
Argument |
Description |
|
number |
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)
sum
Returns the sum of all fields in a field or group. Each field is first converted to a number value.
Syntax
sum(field)
|
Argument |
Description |
|
field |
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 a text box control that displays the total expenses. To show the total expenses, the text box control contains the following formula:
sum(fldExpenseAmount)
true
Returns true.
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. When filling out this form, contractors will need to completely fill out the contact information section before they can fill out the building permit section.
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
Combines two or more fields of text strings into one text string.
Syntax
concat(text1, text2, ...)
|
Argument |
Description |
|
text1 |
A field that contains text to be combined into a single line of text with the text in argument2. |
|
text2, … |
Ore or more additional fields that contains text to be combined with the previous field. Separate text fields with a comma. |
Example
You are designing a form template that contains a field named fldFirstName and a field named fldLastName. Add the following formula to a third field to cause it to contain the text “This form was filled out by <firstname> <lastname>.”:
concat("This form was filled out by ", fldFirstName, " ", fldLastName, ".")
Note: Any actual text entered as a textual argument, including spaces or punctuation, needs to be inserted in quotes (").
contains
Returns true if the first field or text string contains the second. Otherwise, returns false.
Syntax
contains(within_text, find_text)
|
Argument |
Description |
|
within_text |
The field that contains the text to be searched. |
|
find_text |
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 with three text fields. The first one will allow a user to enter a lengthy amount of text in the first text control, named fldText. The second one will allow a user to enter a short segment of text and is named fldFindText. The third text box will compare the second text box to the first and indicate whether the value in the second field is found in the first field and display the result. It would have the following formula as the default value:
contains(fldText,fldFindText)
normalize-space
Removes whitespace from a text string.
Note: This will remove any leading, trailing, and repeating blank spaces from a field with a text data type.
Syntax
normalize-space(text)
|
Argument |
Description |
|
text |
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). To normalize any text entered in the first field, and display what the value in the first field without any excess white space, add a second field with the following formula as the default value:
normalize-space(fldText)
starts-with
Returns true if the first field or text string starts with the second. Otherwise, it returns false.
Syntax
starts-with(text, start_text)
|
Argument |
Description |
|
text |
The name of the field that contains the text to be searched. Separate arguments by a comma. |
|
start_text |
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 with three text fields. The first one will allow a user to enter a lengthy amount of text in the first text control, named fldText. The second one will allow a user to enter a short segment of text and is named fldFindText. The third text box will compare the second text box to the first and indicate whether the value in the first field starts with the value in the second field and display the result. It would have the following formula as the default value:
starts-with(fldText, fldFindText)
string
Converts a value to a text string.
Syntax
string(value)
|
Argument |
Description |
|
value |
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 a text string.
Syntax
string-length(text)
|
Argument |
Description |
|
text |
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 that is used to count the number of characters entered into the first form. To do this, the second field would contain the following formula as the default value:
string-length(fldText)
substring
Returns a specific part of a text string. The second argument specifies the starting position and the third argument specifies how many characters to include.
Syntax
substring(text, start_position, char_count)
|
Argument |
Description |
|
text |
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, whichever occurs first. |
|
start_position |
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. |
|
char_count |
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.
-
A text box control that shows the results of the function. The text box control contains the following formula:
substring(fldText, fldStartingPosition, fldNumberOfCharacters)
If a user enters “This form was created from an InfoPath form template.” in the first text box, followed by 4 in the second and 16 in the third, then the fourth text box would contain “s form was creat”.
substring-after
Returns the text in the first text string that follows the first occurrence of the second text string.
Syntax
substring-after(text, find_text)
|
Argument |
Description |
|
text |
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. |
|
find_text |
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.
-
A text box control that shows the results of the function. The text box control contains the following formula:
substring-after(fldText, fldSubstringText)
If a user enters “This form was created from an InfoPath form template.” in the fldText field, and “InfoPath” in the second, then the third text box control would contain “form template.”
substring-before
Returns the text in the first text string that precedes the first occurrence of the second text string.
Syntax
substring-before(text, find_text)
|
Argument |
Description |
|
text |
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, find_text. |
|
find_text |
The value to search for in the text argument. This value can either be the value in a field with a text data type or text that is enclosed in double quotation marks (" "). The function searches the text in the first argument (text) 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.
-
Aa text box control that shows the results of the function. The text box control contains the following formula:
substring-before(fldText, fldSubstringText)
If a user enters “This form was created from an InfoPath form template.” in the fldText field, and “created” in the second, then the third text box control would contain “This form was”.
translate
Returns the first text string with each character in the second text string replaced by the character at the corresponding position in the third text string.
Syntax
translate(text, find_chars, replace_chars)
|
Argument |
Description |
|
text |
The name of the field that contains the text whose characters will be replaced. Separate arguments with a comma. |
|
find_chars |
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. |
|
replace_chars |
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)
URL functions
SharePointListUrl
Returns the address of the SharePoint list or form library where the form is hosted (InfoPath 2010 only).
Syntax
SharePointListUrl()
This function does not use arguments.
Example
You are designing a form template for a SharePoint list and want to add a link to the location where the list is hosted, such as “http://sharepointserver/site/list/”. To do this, add a hyperlink control and set it to Read-Only, and then set the default value of the field that the control is linked to as follows:
SharePointListUrl()
Note: If the form template is not hosted on a SharePoint server, then the SharePointListUrl() function will only return “http://sharepointserver/site/list/”.
SharePointServerRootUrl
Returns the address of the SharePoint server where the form is hosted (InfoPath 2010 only).
Syntax
SharePointServerRootUrl()
This function does not use arguments.
Example
You are designing a form template for a SharePoint list and want to add a link to the root of the SharePoint server that the list is hosted, such as “http://sharepointserver/site/list/”. To do this, add a hyperlink control and set it to Read-Only, and then set the default value of the field that the control is linked to as follows:
SharePointServerRootUrl()
Note: If the form template is not hosted on a SharePoint server, then the SharePointListUrl() function will only return “http://sharepointserver/site/list/”.
SharePointCollectionUrl
Returns the address of the SharePoint site collection where the form is hosted (InfoPath 2010 only).
Syntax
SharePointCollectionUrl()
This function does not use arguments.
Example
You are designing a form template for a SharePoint list and want to add a link to the site collection of the SharePoint server where the list is hosted, such as “http://sharepointserver/sitecollection/”. To do this, add a hyperlink control and set it to Read-Only, and then set the default value of the field that the control is linked to as follows:
SharePointCollectionUrl()
Note: If the form template is not hosted on a SharePoint server, then the SharePointListUrl() function will only return “http://sharepointserver/sitecollection/”.
SharePointSiteUrl
Returns the address of the SharePoint site where the form is hosted (InfoPath 2010 only).
Syntax
SharePointSiteUrl()
This function does not use arguments.
Example
You are designing a form template for a SharePoint list and want to add a link to the SharePoint site where the list is hosted, such as “http://sharepointserver/site/”. To do this, add a hyperlink control and set it to Read-Only, and then set the default value of the field that the control is linked to as follows:
SharePointSiteUrl()
Note: If the form template is not hosted on a SharePoint server, then the SharePointListUrl() function will only return “http://sharepointserver/site/”.
The userName function
userName
Returns the user name of the person filling out the form (Microsoft InfoPath 2007 and later).
Note: If the user is filling out a form in a Web browser, the user name is retrieved from Microsoft Windows SharePoint Services 3.0 or later.
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 user name 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 user name 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 user name, you bind the field to a text box control. To display the user name 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 text box control:
userName()
