I want to create a pick list of the 5 or 10 most common names (entries) in a database.
What’s the best way to do this? Categorize a view based on this field and then use notesviewnavigator to figure out the highest totals?
populate and array based on this info and then sort it?
Anyone have any other thoughts?
Subject: 10 Ten List? ok Top 5
Boy, this is a toughie. How many documents are we talking here?
I can’t think of any way to use @functions or views, which I’d think would be necessary for a runtime solution unless there are few documents.
Perhaps you can use a scheduled agent to gather this and stick it in a profile document. Is it ok if the data gets a bit stale?
With script I’d start with a view categorized on the NAME key. Use that column and get the total documents with each key and stick the KEY-TOTAL pair in an array. Then sort the array and get the top 5.
Function GetTop5 As String
' This is SO untested.
Dim db As NotesDatabase
Set db = session.CurrentDatabase
Dim lookupView As NotesView
Set lookupView = db.SomeView
' Get the list of names to use as lookup keys
Dim keyList As Variant
keyList = Evaluate(|@Unique(@DbColumn(""; ""; "SomeView"; 1))|) ' 64K Limit alert!
' Make an array to hold our counts
Dim totalArray(1,0) As Array
Dim rowcounter As Integer
rowCounter = 0
Forall key In keyList
Dim nameCollection As NotesDocumentCollection
Set nameCollection = view.GetAllDocumentsByKey(key, True)
Redim Preserve totalArray(1, rowCounter)
totalArray(0, rowCounter) = key
totalArray(1, rowCounter) = nameCollection.Count
rowCounter = rowCounter+1
End Forall
'Sort them my COUNT
Call ShellSortMulti(totalArray, 1)
' Return a Variant
Dim returnList(4) as String
returnList(0) = totalArray(0, 1)
returnList(1) = totalArray(1, 1)
returnList(2) = totalArray(2, 1)
returnList(3) = totalArray(3, 1)
returnList(4) = totalArray(4, 1)
GetTop5 = returnList()
End funtion
The agent that called it periodically would be like this…
Sub StoreTop5
’ This is SO untested.
Dim db As NotesDatabase
Set db = session.CurrentDatabase
Dim profileDoc As NotesDocument
Set profileDoc = db.GetProfileDocument("Configuration")
profileDoc.NameValuesPicklist = GetTop5
End Sub
And lastly on the forms, use a formlua for your choices…
@GetProfileField(“Configuration”; “NameValuesPicklist”)
Subject: RE: 10 Ten List? ok Top 5
Here’s the sort function BTW. Just something I found here and cleaned up a tad.
Sub ShellSortMulti (sourceArray As Variant, sortColumn As Integer)
' Sorts a Multi-Dimensional Array by the sortColumn.
' Array Format: sourceArray (cols, rows)
Dim upperBoundRows As Integer
Dim lowerBoundRows As Integer
Dim lowerBoundCols As Integer
Dim upperBoundCols As Integer
Dim tmpArray () As Variant
Dim numberOfElements As Integer
Dim increment As Integer
Dim rowCounter As Integer
Dim colCounter As Integer
Dim loopRowCounter As Integer
Dim loopUpperBound As Integer
upperBoundCols = Ubound(sourceArray, 1)
lowerBoundCols = Lbound(sourceArray, 1)
upperBoundRows = Ubound(sourceArray, 2)
lowerBoundRows = Lbound(sourceArray, 2)
Redim tmpArray (lowerBoundCols To upperBoundCols)
numberOfElements = upperBoundRows - lowerBoundRows + 1
increment = 1
Do While increment < numberOfElements
increment = (3 * increment) + 1
Loop
Do While increment > 0
increment = increment \ 3
loopUpperBound = lowerBoundRows + increment - 1
For rowCounter = loopUpperBound + 1 To upperBoundRows
For colCounter = lowerBoundCols To upperBoundCols
If Isobject(sourceArray(colCounter, rowCounter)) Then
Set tmpArray(colCounter) = sourceArray(colCounter, rowCounter)
Else
tmpArray(colCounter) = sourceArray(colCounter, rowCounter)
End If
Next
loopRowCounter = rowCounter
Do While sourceArray(sortColumn, loopRowCounter - increment) > tmpArray(sortColumn)
For colCounter = lowerBoundCols To upperBoundCols
If Isobject(sourceArray(colCounter, loopRowCounter - increment)) Then
Set sourceArray(colCounter, loopRowCounter) = sourceArray(colCounter, loopRowCounter - increment)
Else
sourceArray(colCounter, loopRowCounter) = sourceArray(colCounter, loopRowCounter - increment)
End If
Next colCounter
loopRowCounter = loopRowCounter - increment
If (loopRowCounter <= loopUpperBound) Then Exit Do
Loop
If (loopRowCounter <> rowCounter) Then
For colCounter = lowerBoundCols To upperBoundCols
If Isobject(tmpArray(colCounter)) Then
Set sourceArray(colCounter, loopRowCounter) = tmpArray(colCounter)
Else
sourceArray(colCounter, loopRowCounter) = tmpArray(colCounter)
End If
Next colCounter
End If
Next rowCounter
Loop
End Sub
Subject: RE: 10 Ten List? ok Top 5
You are awesome – I will try! Thanks!!!