May 14, 2012

For(mat) Your Eyes Only

Helix ALM
James Bond "007"Everyone knows who James Bond is, and everyone knows his code name is “007”. Everyone that is, except for QA Wizard Pro. When QA Wizard Pro reads in a numeric value from a datasheet it strips off the leading zeros, the left-most digits in positional notation. In James Bond’s case he would go from “007” to simply “7”, which is somehow just not the same. This does not affect values used in calculations, where “007” is equal to “07” is equal to “7", but the extra padding is required in many cases. Some applications have data fields that expect a specific number of digits as input, such as a ten-digit phone number or a nine-digit Social Security number. It’s easy to replace the stripped digits using the Format statement, which takes a format string and an array of values as its arguments. Arrays, variables that contain a series of values, are declared explicitly here for use in the Format statement. Assuming that the script is associated with a datasheet in the Data Source drop-down menu, the contents of the “SSN” column cell are assigned to the first element in the array. The entire array is then specified as an argument in the Format statement.

Dim A(1) A(1)= Cell("SSN") SSN = Format("{0:000000000}", A)

The above steps will assign the correct value to the SSN variable, whether the datasheet contains “123456789” or “001221234”. There is no need to worry about losing any trailing zeros because they are significant digits and will not be stripped. If the SSN variable itself needs to be in the familiar dashed notation, the Format statement can be modified to comply:

Dim A(1) A(1)= Cell("SSN") SSN = Format("{0:000-00-0000}", A)

The Format statement can also be used to round numbers. For example, to round numbers in a datasheet from eight decimal places to two decimal places:

Dim A(1) A(1)= 002342.93889982 VALUE = Format("{0:00.00}", A)

The number assigned to the VALUE variable will be 2342.94. This sample script on the Seapine Labs web site has more examples of the Format statement.