Force a Field Value to a Number Using Rules
Page 1 of 2
Written by
Gregory Scot Collins
Friday, 20 March 2009, 9:57 AM
This article has been tested to work with the following products and versions. No guarantee of compatibility, with or without modification, is offered for products or versions other than those listed.
IN THIS ARTICLE:
Most secondary data sources use the string data type for all nodes and sadly, custom data validation is not available, as shown in Dialog 1. While you could use code for custom data validation, you might consider using rules to force a value into compliance.
This article will show how to force a field value to a number using rules. It will also show how to restrict the value further, if desired, to be a whole number, a positive number or a negative number.
Force to number
Use the following steps to create a rule to force a field value to a number:
- Right-click the field, and then choose Text Box Properties.
- In the Text Box Properties dialog box click Rules, and then click Add.
- Name the rule Force to Number, and then click Add Action.
- Select Set A Field's Value in the Action drop-down list.
- Click the Select A Field Or Group button to the right of the Field text box, and then click OK.
- Click the Insert Formula button to the right of the Value text box.
- In the Formula text box type: translate(number(.), "Na", "").
- Click OK three times.
The number function tries to convert the field value to a number. If it fails, the result is NaN, which stands for Not a Number. The translate function converts NaN to an empty string. The result is either a number or a blank value.
The following additional rules can be added if you want to further restrict the value entered to be a a whole number, a positive number or a negative number.
Force to whole number
Use the following steps to create a rule to force a number to be a whole number:
- Add another Rule named Force to Number, and then click Set Condition.
- Select Contains in the second drop-down list.
- Select Type Text in the third drop-down list, and then type: . (i.e. a decimal point).
- Click OK, and then click Add Action.
- Select Set A Field's Value in the Action drop-down list.
- Click the Select A Field Or Group button to the right of the Field text box, and then click OK.
- Click the Insert Formula button to the right of the Value text box.
- Type one the following in the Formula text box:
- If you want to use standard rounding, type: round(.).
- If you want to round down, type: floor(.).
- If you wnat to round up, type: ceiling(.).
- Click OK three times.

Stumble It!
Digg It!
del.icio.us




