Add subtract multiply and divide values in a control InfoPath 2007

Author: mety Labels::



You can help your users fill out forms based on your form template by using formulas to calculate the value for a control, based on data that the user enters into other controls. The calculations can include adding, subtracting, multiplying, and dividing values. For example, if you are designing a form template that electrical contractors will use to apply for permits, you can add a formula to a control that automatically calculates the total price that the contractor has to pay for the application by multiplying the number of permits in the application by the price of each permit.

A formula is an XPath expression made up of values, fields or groups, functions, and operators used to calculate and display other values. Formulas can be used to do the following tasks:
  • Calculate mathematical values from values that you specify when designing the form template or values that users enter into controls when they fill out forms based on your form template..
  • Display specific dates and times.
  • Display the values that users enter into one control in another control.
  • Set the default value of a field or control.
  • Run a rule based on a value calculated by using a formula.

Each XPath expression that is used in a formula is a combination of values, functions, and operators that evaluates to a single value. A formula can contain several expressions. You can think of a formula as a sentence made up of one or more phrases, with each phrase representing one expression in the formula.
The following illustration shows the relationship between a formula and an expression.
Diagram showing relationship between formulas and expressions
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, as well as some InfoPath-specific functions. Find links to more information about InfoPath functions in the See also section.

Using two or more operators in a formula

When a formula contains two or more mathematical operators, InfoPath performs the calculation according to the precedence of the operator. The following list shows the order in which operations are performed:
  1. Calculations within parentheses
  2. Multiplication and division calculations
  3. Addition and subtraction calculations
If the formula contains two operators that have the same precedence, the calculations are performed from left to right.

For example, imagine that you are creating a permit application form template that has a text box that displays the total cost of all of the permits submitted in an application. The values required by this formula come from other text boxes in the form. The text box that shows the total cost contains the following formula:
txtPermit1Qty * txtPermitCost1 + txtPermit2Qty * txtPermitCost2 /txtNumberOfPermits
This formula includes addition (+), multiplication (*), and division (/) operators. According to the precedence of operators, the multiplication and division calculations are performed before the addition calculation. Since the multiplication and division operators have the same precedence, the multiplication calculation is done before the division operator because the multiplication operator is located to the left of the division operator. The formula is calculated in this way:
  1. The value in txtPermit1Qty is multiplied with the value in txtPermitCost1.
  2. The value in txtPermit2Qty is multiplied with the value in txtPermitCost2, and that result is then divided by the value in txtNumberOfPermits.
  3. The result of the calculation in step 1 is added to the results of the calculation in step 2.
To control calculation order, enclose the calculation that you want done first in parentheses. The calculations in the parentheses are performed before calculations outside the parentheses. Put calculations with parentheses to the left of calculations without parentheses. Calculations in nested parentheses are performed working from the inner to the outer parentheses.

For example, consider the following formula:
((txtPermit1Qty * txtPermitCost1) + (txtPermit2Qty * txtPermitCost2))/txtNumberOfPermits
In this calculation, the value that results from multiplying the values in txtPermit1Qty and txtPermitCost1 is added to the value that results from multiplying the values in txtPermit2Qty and txtPermitCost2. The sum of that calculation is then divided by the value in txtNumberOfPermits.

Insert a mathematical formula into a control

  1. Double-click the control or field for which you want to create the formula.
  2. Click the Data tab.
  3. Click Insert Formula Button image.
  4. To insert a value or mathematical operator into the formula, type the value or the symbol for the mathematical operation in the Formula box. ShowList of mathematical operations
    OperationSymbol
    Add+
    Subtract-
    Multiply*
    Divide/
    Note If your formula uses the division (/) operator, make sure that there is a space before and after the division operator. If the division operator does not have a space before and after it, InfoPath may interpret '/' as a separator for XPath location steps rather than as a division operator.
     Tip Mathematical formulas usually depend on integer or decimal values as arguments. To avoid blank values in your formula, select the Treat blank values as zero check box in the Advanced category in the Form Options dialog box.
  5. To check the formula for the correct syntax, in the Insert Formula dialog box, click Verify Formula. ShowMy formula contains errors
    Click Show Details in the Microsoft Office 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. Find links to more information about functions in the See also section.
    • Delete and retype your formula to make sure that it is correctly typed.
  6. To test your changes, click Preview on the Standard toolbar, or press CTRL+SHIFT+B.


0 comments |
Subscribe to: Post Comments (Atom)

Labels

Blog Archive

Powered by Blogger.

I made these pages for me and my friends to help solving the problem we face regarding Computer & internet, if anyone wants me to answer a question or find out about some information please send me email and I will try to reply.*P.S. some of the article I wrote and the other I found on the internet I posted them in sprit of learning and shearing, please forgive me if you found something you don’t want to be in my blog, email me and I will delete them. Thank you for your interest in my pages.امل نجم Amal Nagm

banner 1 banner 2