Spanky's Place

While the solution may not be the most elegant, it does the job pretty well. The "custom" part of the algorithm is in how the array needs to be sorted. It is nothing more than a list of string values. Each string value represents a name, followed by a space-hyphen-space, and finally, a number. The number represents something to do with the name, but that is not important. What is important is that the list be sorted numerically, based on the number.

The values look something like this:

• Billy Ray - 10
• Johnnie Lightning - 37
• Elvis Cantebury - 9
• Chief Wiggum - 13
• Angela King - 6
• Mary Ford - 27

The solution is nothing more than a modified bubble sort, combined with some string manipulation and number conversion. If you want to play with the code, I put a copy of the demo database in the downloads area. If you would prefer to just take my word for it, then read on.

For the demo, I created a simple form with 2 editable fields on it. Both are text, multi-value fields, with value-seperator set as Line Break. I named them SourceList and TargetList. I'll leave it to you to figure out which is which. I put a button on the form, coded it as follows:

'Sort_with_LotusScript:

Sub Click(Source As Button)
     
     Dim nuiwsp As New NotesUIWorkspace
     Dim nuidoc As NotesUIDocument
     Dim varSource As Variant
     Dim varTarget As Variant
     
     
     Set nuidoc = nuiwsp.CurrentDocument
     
     varValue = nuidoc.Document.SourceList
     
     Call SortIt (varValue)
     
     nuidoc.Document.TargetList = varValue
     
End Sub
Sub SortIt(varSource As Variant)
%REM This will sort the array of strings, using a slightly modified bubble sort.

     Given the Format of the string:
     (variable length alphanumeric) space-hyphen-space (variable length numeric, not more than 32767)

     Instead of each string being compared, the final "number portion" of the string will be converted to an integer, and the values of the integers will be compared.
%END REM
     
     Const BREAKER_VALUE = " - " ' space-hyphen-space
     
     Dim intOuter As Integer
     Dim intInner As Integer
     Dim strAlpha As String
     Dim strBeta As String
     Dim intAlpha As Integer
     Dim intBeta As Integer
     Dim boolError As Boolean
     
     On Error Goto ErrorTrap
     
     For intOuter = Ubound(varSource) To Lbound(varSource) Step -1
          For intInner = Lbound(varSource)+1 To intOuter
               boolError = False
               strAlpha = varSource(intInner - 1)
               strBeta = varSource(intInner)
          
               intAlpha = Cint(AT_RightBack(strAlpha, BREAKER_VALUE))
           If boolError Then
                    boolError = False
                    intAlpha = 0
           End If ' boolError
          
               intBeta = Cint(AT_RightBack(strBeta, BREAKER_VALUE))
           If boolError Then
                    boolError = False
                    intBeta = 0
           End If ' boolError
          
           If (intAlpha > intBeta) Then SwapStringEntries varSource, intInner - 1, intInner
      Next ' intInner
     Next ' intOuter
     
ExitPoint:
     Exit Sub
ErrorTrap:
     boolError = True
     Resume Next
End Sub ' SortIt

Sub SwapStringEntries(varSource As Variant, intAlpha As Integer, intBeta As Integer)
     
     Dim strTemp As String
     
     strTemp = varSource(intAlpha)
     varSource(intAlpha) = varSource(intBeta)
     varSource(intBeta) = strTemp
     
End Sub ' SwapStrings
Function AT_RightBack (Byval strSource As String, varSearch As Variant) As String
%REM
     Returns the rightmost characters in a string.
     If varSearch is not found, this sub returns an empty string ("").
     This function emulates @RightBack, with only STRINGS as parameters
%END REM
     On Error Goto ErrorTrap
     
     Dim lngPos As Long
     Dim lngLast As Long
     
     AT_RightBack = ""
     If strSource = "" Then Exit Function
     
     
     Select Case Typename(varSearch)
     Case "STRING"
          lngPos = Instr (strSource, varSearch)
      If lngPos = 0 Then Exit Function
     
          lngLast = lngPos
          lngPos = lngPos + Len (varSearch)
     
      While lngPos > 0
               lngPos = Instr (lngPos, strSource, varSearch)
           If lngPos > 0 Then
                    lngLast = lngPos
                    lngPos = lngPos + Len (varSearch)
           End If ' lngPos
      Wend ' lngPos > 0
     
          lngLast = lngLast + Len (varSearch) - 1
     
          AT_RightBack = Right$(strSource, Len (strSource) - lngLast)
     
     Case "INTEGER"
      If varSearch < 0 Then Exit Function
          AT_RightBack = Mid$(strSource, varSearch + 1, Len(strSource))
     
     Case Else
          Exit Function
     End Select ' Typename(varSearch)
     
ExitPoint:
     Exit Function
ErrorTrap:
     AT_RightBack = ""
     Resume ExitPoint
End Function ' AT_RightBack

Pretty cool, eh? Now for the kicker. After I thought about it a bit, I realized I could do exactly the same thing with just one line of @Formula code.

From the Lotus Designer Help:

customSortExpression
Formula. Required when the [CUSTOMSORT] keyword is specified. A formula that uses the temporary variables $A and $B to compare the values of elements in the list two at a time. If $A is greater than $B, the expression returns @True. If $B is greater than $A, the expression returns @False.

So, what is the formula? Well, it's pretty simple actually. The secret is to think about the values of $A and $B. Re-read the help text. Notice that it doesn't say that I am required to use the entire value of the string. I can instead use any value I wish. I get to access the temporary variables and use them as a basis for my formula. So, without further ado, here it is:

FIELD TargetList := @Sort ( SourceList; [CustomSort]; @If ( @IfError ( @ToNumber ( @Trim ( @RightBack ( $A; " - " ) ) ); 0 ) > @IfError ( @ToNumber ( @Trim ( @RightBack ( $B; " - " ) ) ); 0 ); @True; @False ) );

Hope this helps!
-Devin