Sorting Character Strings Like Numbers

A question came up in a thread in the Autodesk Architectural Desktop 2006 Discussion Group regarding the sorting of a window schedule where the column to be sorted uses an Auto Increment - Character manual property type. The problem is that the column sorting treats the values as text string, and if you have more than 26 items, "AA" will be sorted between "A" and "B", rather than after "Z".

You could simply start your first value with the maximum number of characters needed to label all of the windows, for example, starting with "AAA" would allow you to have up to 17,576 windows before "AAAA" would appear between "AAA" and "AAB". You could also add another manual property to handle the sorting, but that would defeat the purpose of using an Auto Increment manual property type.

Formula properties to the rescue! I suggested that if padding the string was not acceptable, a formula property could be used to convert the character string to an integer value that could then be included in the schedule as a hidden column, with the sorting based on the numerical column. It is one thing to suggest doing so, and another altogether to write a formula that can do it, so I followed up with a sample file that demonstrates that such a formula can be written. Here is what I did in my sample file. The file was created in ADT 2004.

1. I imported the out-of-the-box WindowObjects Property Set Definition and modified the Number property, changing its Type to Auto Increment - Character, its Default value to "A" [without the quotation marks] and its Format to "Case - Upper". I did this so I could use the out-of-the-box window tag without need for modification. I would recommend giving anything that you customize from the out-of-the-box content a different name, to avoid the potential for conflict when files already have the out-of-the-box version. You may have to modify copies of other content to coordinate with your renamed content, but in the long run that extra work will pay off, particularly when migrating to future releases.
   
   
2. I created a new Property Set Defintion, WindowObjects2 to hold the formula property which will convert the character string in the WindowObjects:Number property to a number that can be sorted. There is no need for this to be in a separate Property Set Definition; it could just as easily been in the WindowObjects Property Set Defintion. If I had used a renamed copy of the WindowObjects definition for the changed Number property, I most likely would have included the formula in that definition as well. For the purposes of this example, separating it out here makes it easier to find it both in the Style Manager and the Properties palette and to minimize the changes in WindowObjects.
   
   
3. The CharToNumber formula looks something like the following.
   
   

   strID = UCase("[WindowObjects:Number]")
   iLengthString = Len(strID)
   iTotal = 0
   
   For iCount = 1 to iLengthString
       iTotal = iTotal + (Asc(Right(strID, 1))
                 * (10^(2*(iCount - 1))))
       strID = Left(strID, (iLengthString - iCount))
   Next
   
   RESULT = iTotal

   
   
   • Note that the iTotal line in the For Next loop is shown here on two lines, to fit the blog format, but appears on one line in the actual formula.
     
     
   • strID is a variable that is initially assigned the value of the WindowObjects:Number property, converted to upper case by the VBScript UCase function. As always, remember that you must select properties referenced by your formula from the lower panel [lower left panel in 2006], you can not simply type or cut-and-paste the name into the formula. Also be certain to enclose the property reference in double quotes so that the value is interpreted as a string, or the UCase function will fail.
     
     
   • iLengthString holds an integer indicating the initial number of characters in strID, as returned by the VBScript Len function.
     
     
   • iTotal holds the value that will be used for sorting the schedule, and is initialized at 0.
     
     
   • A For Next looping construct is used to process the string and calculate the final number to be returned. The loop is run once for every character in the strID string. The value of iCount is automatically incremented by the For Next construct on each pass.
     
     
   • Two things are done on each pass of the loop. A value based on the ASCII character code for the rightmost character, obtained using the VBScript Asc function, is added to the previous value in iTotal. The rightmost character is then stripped from the string in strID, and the loop repeats while additional characters remain.
     
     
   • Initially I was going to treat the characters just like decimal numbers, so that the rightmost character position would be the "ones" position, the one to the left of that the "tens" position, etc. Since the decimal values of the ASCII codes for capital letters in the English each have two digits, I chose to avoid any sorting problems caused by adding the overlapping positions by treating the left position as "ones", the next as "hundreds", the next as "ten thousands", etc. The (10^(2*(iCount - 1))) part of the formula provides the factor that accomplishes this. "^" is the exponentiation operator in VBScript. If you need to sort lower case letters differently from upper case letters or have any other characters above ASCII 99 in your string, you can allow three digits for each character by using a "3" instead of a "2" in that part of the formula: (10^(3*(iCount - 1)))
     
     
   • The RESULT line returns the final value of iTotal as the value of the formula.
   
   
   
4. I set up some test windows, tagged them with the out-of-the-box window tag to add the WindowObjects Property Set and then manually added the WindowObjects2 Property Set by selecting all of the windows using QSelect and using the "Add property sets" button in the lower left corner of the Extended Data tab of the Properties palette. I edited the values of some of the number properties so that there would be some three-character values to include in the sort.
   
   
5. I created a Schedule Table Style, WindowSortingTestSchedule, to display the WindowObjects:Number property as well as the WindowObjects2:CharToNumber property, with the schedule sorting on the latter column. I left the WindowObjects2:CharToNumber column visible in my test schedule so I could see the calculated value; in a "finished" Schedule Table Style, I would modify that column and make it hidden.

Download the sample file from the Autodesk Architectural Desktop 2006 Discussion Group, to see that the windows are sorted, based on the character values assigned to the WindowObjects:Number property, but in "numerical" style, starting from the right character, rather than alphbetically, starting with the left character.