November 14, 2005

Dealing with NA in Formulas

ADT will report NA for an automatic property when that property does not have a value assigned for a particular object or style. The actual text displayed in controlled by the Property Data Format assigned to the property on the Formatting tab, in the General section, in the Not Applicable edit box. When reading a property that could have such an "empty" value into a formula property, VBScript will interpret the value as being "Empty", the formatting applied in the Property Data Format will not follow through. You can not test the value of the automatic property for "NA", or whatever string you assign to it. Update, 9/3/2006: If you enclose the text in the Not Applicable edit box in the Property Data Format with explicitly typed double quotation marks, the string value will be returned, rather than "Empty". See this article for additional information on this.

Attempting to perform mathematical operations, or even to convert the value to a particular type [using the CDbl function, for example] will result in an error and the formula will fail.

One solution for this, assuming the original automatic property has a numeric value when a value is assigned, would be to create an intermediate formula that uses either the VBScript TypeName or VarType function to test the automatic property value to see if it holds an empty value [TypeName returns the string "Empty"; VarType returns the integer 0]. If it does, have the RESULT of the formula be a number that is either meaningful to an "Empty" condition, or is at least unique - meaning that it would not be used when a numeric value is assigned. Then you can use the value of the intermediate formula in your final formula, testing, if necessary, for the value assigned for the "Empty" condition and taking the appropriate action.

A sample file demonstrating this condition and solution can be found in this thread in the Autodesk Architectural Desktop 2005 Discussion Group. The automatic property in question here is the door thickness. For cased opening door styles [using the Pass Through door type], there is no door, and the thickness property is "Empty" for doors using the Pass Through type. The request was to be able to have the thickness of existing doors show as "--" on the schedule while having the thickness show for new doors. The problem was the formula would fail when a new cased opening was scheduled, as applying the CDbl to the value of the automatic property in the formula property [to force whole numbers to be interpreted as reals] does not work when the value is empty.

The intermediate property in the example file, ThicknessForFormula, sets its value to 0 if the automatic property is "Emtpy", otherwise it passes through the value of the automatic property without doing anything to it. If there is no door leaf, then 0 would seem to be an appropriate value for the thickness. The final formula, ThicknessForSchedule, then tests that value and I chose to display "--" in the schedule if it is zero, otherwise the value is forced to be a double and the Length - Short Property Data Format is applied to get imperial architectural formatting.

No comments: