We help IT Professionals succeed at work.

Sort dialog list

Pennywisdom
Pennywisdom asked
on
I have a dialog list that gets it's possible values from multiple fields and I want it to be sorted alphabetically.

How can I do this?
Comment
Watch Question

Author

Commented:
What I need is to sort them in a formula because there's only the option "Use formula for choices". I can't use lotus script here.
set a hidden field value with the script and then refer that field value in your keyword formula.

Author

Commented:
I don't know how this would be done or where it would be, can you give me an example in code?

This is my formula that determines the choices:
@Unique(
@DbColumn(""; ""; "myView"; 1) :
@DbColumn(""; ""; "myView"; 2) :
MyField
)

Thanks in advance
Commented:
Hello Pennywisdom,

here how to do that.

Create an agent to perform the sort. The advantage of the agent is that you can use it for NotesClient and for web forms.
Agent name: "SortMyFieldChoices"
ActOn: "RunOnce(@CommandsMaybeUsed)"
Sub Initialize
  Dim session As New NotesSession
  Dim doc As NotesDocument
  Dim sArray As Variant
  Set doc = session.DocumentContext
  sArray = doc.GetItemValue( "myFieldChoices" )
  Call DoInsertSort ( sArray, Lbound ( sArray ), Ubound ( sArray ) )
  Call doc.ReplaceItemValue( "SortedChoices" , sArray )
End Sub
Sub DoInsertSort ( sA As Variant, Byval bottom As Long, Byval top As Long )    
  Dim i As Long
  Dim x As Long
  Dim v As String
  Dim Found As Integer
  For i = bottom+1 To top
    x = i
    v = sA (i )
    Do While (sA(x-1) > v)
       sA ( x ) = sA ( x-1 )
        x = x - 1
        If x=0 Then
          Exit Do
        End If
    Loop
    sA (x) = v
  Next
End Sub

On your form you need two fields: "myFieldChoices" and  "SortedChoices"
Both have to have the option check on: "AllowMultipleValues" and to be "Computed".
In the field  "myFieldChoices" you place your values formula:
@Unique(
   @DbColumn(""; ""; "myView"; 1) :
   @DbColumn(""; ""; "myView"; 2) :
   MyField
)

Field "SortedChoices" can have empty formula, like this:
""

In your DialogList field you place simply the name of destination field as formula for choices:
SortedChoices

So, now you have to call the sort agent on form open and at recalc event. Call it with this formula:
@Command([ToolsRunMacro]; "SortMyFieldChoices")

By the way: the sort function itself is taken from names.nsf from Groups form as an form action.

Good luck,
zvonko

Author

Commented:
I tried your code zvonko and the field "SortedChoices" stays empty.

In debug I can see that this field is changed to the correct value but on the form it's empty.

Any ideas why?
Pennywisdom,

Instead of, Call doc.ReplaceItemValue( "SortedChoices" , sArray ) use,

doc.SortedChoices = sArray

and I hope it is not for web, if so then use this instead of, Set doc = session.DocumentContext

Dim ws as New NotesUIWorkSpace
Dim uidoc as NotesUIDocument
set uidoc = ws.CurrentDocument
Set doc = uidoc.Document

~Hemanth


Author

Commented:
It worked but I had to use a NotesUIDocument to set the field correctly (thanks Hemanth) so here's how it looks:

Sub Initialize
     Dim ws As New NotesUIWorkSpace
     Dim uidoc As NotesUIDocument
     Dim doc As NotesDocument
     Dim sArray As Variant

     Set uidoc = ws.CurrentDocument
     Set doc = uidoc.Document
     sArray = doc.GetItemValue( "myFieldChoices" )
     Call DoInsertSort ( sArray, Lbound ( sArray ), Ubound ( sArray ) )
     doc.BodyAreaSortedChoices = sArray
End Sub

Thank you both for your help!

Commented:
Hello  Pennywisdom,

thank you for the points and for the feedback <|;-)

I am happy that you are getting the code to work. My code is allways tested by me and tuned before posted here as answer to a question. As you can read in start of my comment I decided to present you a code solving both situations, web and client, in one code. So is Heman's code toatally missleading this.
Definitly is NotesUIDocument not available in web. But this is a separate story. I am happy you got your code working and can now exploit the rest on your own.

Good luck,
zvonko

Explore More ContentExplore courses, solutions, and other research materials related to this topic.