Using Rules to Passively Insure Postive or Negative Values in InfoPath

Several projects that I have completed using InfoPath over the last few months have made use of numerical values for calculations. For example, a recent form was used upon employee termination to determine if the employee owed money to the organization or if the organization owed money to the employee. It makes a significant difference if the form is allowed to present both deductions and additions as positive values, so we pursued a path to transparently pick up the appropriate numbers. If the person completing the form enters a positive number into a field that expects a negative number, the value is silently converted for the employee without any prompts or error messages. If the employee enters the values as negative, then nothing needs to be corrected. Here’s how this was accomplished.

From either the data source view or the form designer, right click on the field that needs to either be a positive or a negative value and select rules. This will bring up the rules designer that will be used for the remaining steps. Add a new rule and give the rule a name (I prefer to name my rules according to the expected outcome).

A condition needs to be created so that the field is only modified if the value entered is a negative value. The formula should be something like the following: [FIELD] is less than 0. This will check the value of the field and if a value less than zero (a negative value) is provided execute the actions of the rule.

Next, add an action to set a field’s value.

Click on the button to the right of the text box to select the field to set. This should be the field that should be a positive value. Next, click on the formula button (fx) for the value text box. The formula should be -1 * [FIELD]. You can easily insert the field by clicking on Insert Field or Group and selecting the field you need changed.

Since any negative number multiplied by -1 is the positive of the same value, you get the positive value of the value entered. This same benefit plays out for places the form must have negative numbers. Now, when entering data into the form as a negative value where a positive value is expected, InfoPath will execute the rule and change the value accordingly.


Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s