January 12, 2005

"Unformatted" Properties for Formulas

Scott Arvin's Brain Dump on Property Data Enhancements discusses using unformatted properties when doing numeric calculations in a Formula Property. You may wonder how to create an unformatted property - here is what I have done, with success, when creating a Formula Property that performs a numeric calculation on the value of another property.

You make a property "unformatted" by assigning the out-of-the-box "Standard" Property Data Format [PDF], which no one has modified, right? You could also use an equivalent PDF of your own. The goal is to get numeric properties as a raw number, for example, 36.5, not 3'-0 1/2" or 36.50 SQ FT. Trying to convert formatted data to a number will likely have unpredictable results at best, and may just cause the formula to fail completely, at worst. The important settings in the out-of-the-box Standard PDF are having no Prefix or Suffix settings, Integer Zero Padding set to 0 [integer padding may not be a problem; I have never tried to use a padded integer in a formula] and real number unit format set to Decimal [Architectural and Engineering should not be used; I am not certain about the others, but would advise sticking to Decimal].

The challenge comes when you also want to reference that same property with a format, particularly for properties that you are already displaying in a tag. [Schedules have their own format settings.] There are two cases to consider: a manual property [which you do not want to have the user enter more than once] or an automatic property that is already in the PSD and has an applied format other than Standard.

For a manual property, set the PDF to Standard, set up a separate Formula Property that reads the manual property and put your desired formatting on the Formula Property. Use the formatted Formula Property in any tags you make. You can also use it in a schedule, and the PDF applied in the PSD will be the initial default PDF in the Schedule Table Style.

For a pre-existing automatic property, you need to add the same automatic property again. To do so, you need to "trick" ADT into thinking that you have not already added that property by temporarily renaming the existing one. I usually just add a single character like "x" to the end of the name. Now add the automatic property, rename the newly added property [I like to add "Unformatted" to the end of the property name, as Scott did in his examples], set its format to Standard and finally rename the original property back to its original name. I find this process easier than taking the existing property, writing down the current PDF, renaming the property [by appending "Unformatted"], resetting the PDF to Standard, creating a new automatic property of the same type and applying the original PDF format to that, but if you prefer that method, it should work just fine as well.

Now that you have your unformatted property, you are all set to use it in your Formula Property. One other piece of advice I will pass along is that, even in its unformatted state, I often find that ADT will interpret the value as a string. Depending upon exactly how you are using the reference to the numeric property, it may automatically convert that string to a numeric value. If it does not, and having the wrong data type causes your formula to fail, you can use one of the VBScript data conversion functions to get the proper data type.

For integer values, use CInt([UnformattedPropertyName]) and
for real numbers, use CDbl([UnformattedPropertyName]), where
[UnformattedPropertyName] is your unformated property, selected from the lower pane.

When creating a Formula Property to create a formatted version of a manual property as noted above, you will want to use this data conversion technique to be certain that the PDF is applied appropriately. For example, if you are manually entering a distance that you want formatted in architectural units, simply clicking on the manual property name will likely result in ADT treating the value as a string, and the architectural units specified in the PDF applied to that Formula Property will be ignored. But if you use the CDbl conversion, ADT will treat the value as a real number and will apply the architectural formatting. I have also found that when a property that is meant to be a real number hold a whole number, that ADT will treat it as if it were an integer [and not apply architectural formatting, for example] unless I use the CDbl conversion to make it a real number.

UPDATE: Users of ADT 2007 or later can avoid creating a separate unformatted property for use in formula properties, as you can assign a different Property Data Format to any property, including automatic ones, for use in a formula, in the sample values area of the formula editing dialog. More detail on this can be found in this blog article.

No comments: