May 01, 2006

Using Property Data Formats to Force Real Number Interpretation

When passing a numeric property through as the RESULT of a formula property to which you want to apply real-number formatting, you may have found that if the value is a whole number, the real number formatting is not applied. This is because the value gets interpretted as an integer, since it is a whole number. One common way around that is to use the CDbl conversion function to force the numeric value to be interpreted as a real number.

But what if the Reference Property value is numeric sometimes, but could be a string value other times? If you use the CDbl conversion, the formula will fail when the value is a string. One occasion when you could have this is if you change the "Undefined" value in a Property Data Format from the default ? [without quotation marks] to a string value. You would need to do this if you try to use a Formula property to combine properties from a number of different Property Set Defintions that apply to different object types, so the values can be combined into one column of a schedule. That Formula property would need to reference properties from each of the various object types, so there would be undefined references on every object. If you leave the undefined value as ?, then every single formula will fail, and you would not get any usable results. By replacing the ? with a string value, you avoid the automatic failure, but now you can not use the CDbl function to force whole numbers to be read as real numbers. See the CostObjects example in Scott Arvin's Property Data Enhancements Brain Dump for an example of this.

What to do? In the Property Data Format which is applied to the source Property being referenced, turn off zero suppression of trailing zeros, and be certain to select a Precision with at least one decimal place. Then make certain that the source Property value is forced to a real number. That way, a whole number will be formatted with a decimal point and as many trailing zeros as you specify for your Precision, and the value will be interpreted as a real number by any Formula property that references it.

No comments: