September 03, 2006

NA in Formulas Revisited

I came across an old article on dealing with NA as a potential value in a Formula property. The workaround I suggested, as illustrated in a sample file posted in a reply in the Autodesk Architectural Desktop Discussion Group, made use of the VBScript TypeName function to test whether the value of an automatic property was "Empty", which results in the dreaded NA being displayed in a property, and causes a formula that tries to perform mathematical operations on that value to fail.

That workaround remains valid, but today it occurred to me that the same approach that Scott Arvin used for the "Cost" examples in his Property Data Enhancements Brain Dump might also work for NA. In the "Cost" example, the ? returned when a property is not attached to a scheduled object will also cause a formula that tries to evaluate it to crash. The ? character that is displayed for Undefined properties is specified in the Property Data Format assigned to the property, as is the NA for Not Applicable properties. You can avoid the crash in either case by creating a Property Data Format that assigns an explicit string to the Undefined and/or Not Applicable properties. You do that by typing in opening and closing double quotes around the text you enter into the associated edit box, as shown for the Not Applicable value in the Standard-8-NA Property Data Format shown in the image below.

I have made a new reply to the previously mentioned thread which has a sample ZIP file attached that contains NA-in-Formula-Test2.dwg. This is a copy of the previously posted drawing file [2004 format], and contains all of the properties used for the original method, along with two new properties and a new Property Data Format. The Property Data Format is called Standard-8-NA, and is as depicted above. One of the new properties is an Automatic property referencing the Thickness property of doors, called ThicknessUnformatted2, and has the new Standard-8-NA Property Data Format applied. The other new property is a Formula property called ThicknessForSchedule2, and looks like this:

If TypeName( [ThicknessUnformatted2] ) = "String" Then
RESULT = "--"
RESULT = CDbl( [ThicknessUnformatted2] )
End If

A direct test for "NA" in [ThicknessUnformatted2] worked when the value was Not Available, and therefore "NA", but did not work when the value was a real number, so I changed the test to simply determine whether the type was a string [the TypeName function always returns a string, so a comparison to another string should not cause a failure]. This alternate approach does not require the ThicknessForFormula Formula property that the original one did, but does require a custom Property Data Format. I would call it a draw and suggest you use whichever method seems best to you.

No comments: