I was asked to take a look at an existing application, and come up with suggestions to improve performance.
In a first look I found that most of the views, especially the default to be opened view, were set to refresh the index automatically… you can imagine how much the users love to start working with this application which has readers fields on all documents (NOT!).
When I asked were all the views were needed for, I got as reply: to have it in a format we want to make graphics in Excel.
Okej, why not delete these ‘views to be exported for Excel’ and replace them with an export functionality?
So what where the needs?
- a set of fields for different types of forms that should be manageble by the application manager
- an export-mechanism to Excel which should be easy to understand for end-users and give them the opportunity to create customizable exports.
1) Export profiles
In order to create so-called Export profiles for different Notes forms I needed an additional form (ExcelFormExport) where the administrator can:
- select the desired form for export (FieldA)
- select the form-fields that should be available for export (FieldB)
This last field is being calculated when FieldA is being exited:
Sub Exiting(Source As Field)
Dim workspace As New NotesUIWorkspace
Dim uidoc As NotesUIDocument
Set uidoc = workspace.CurrentDocument
fieldvalue = Lcase(uidoc.FieldGetText(“Tx_FormSelected”))
Dim doc As NotesDocument
Set doc = uidoc.document
If fieldvalue <> “” Then
Dim session As New NotesSession
Dim db As NotesDatabase
Set db = session.CurrentDatabase
Forall form In db.Forms
If Lcase(form.Name) = fieldvalue Then
If Isempty(form.Fields) Then
Messagebox form.Name & ” has no fields…”
‘collecting the form fields
Dim arrFieldNames() As String
Dim iCount As Integer
iCount = 0
Forall field In form.Fields
Redim Preserve arrFieldNames(iCount)
arrFieldNames(iCount) = field
iCount = iCount + 1
Call doc.ReplaceItemValue(“Tx_FormFields”, BubbleSort(arrFieldNames))
Messagebox “The form “”” & formNameIn & “”” does not exist”
Bubblesort is a function which sorts the array with fieldnames:
Function BubbleSort(vtList As Variant) As Variant
Dim tmpValue As Variant
Dim x As Integer, y As Integer
If Ubound(vtList) = 1 Then
BubbleSort = vtList
For x = 0 To Ubound(vtList)
For y = (x + 1) To Ubound(vtList)
On Error Resume Next
If vtList(x) > vtList(y) Then
tmpValue = vtList(x)
vtList(x) = vtList(y)
vtList(y) = tmpValue
BubbleSort = vtList
Luckily I could make use of a solution from my colleague Tomas Ekström (his so-called ‘tablewalker’) which made the selection of fields and giving them for the end-user understandable ‘labels’ in the wizard:
The next step was creating a dialog between the available ‘Export profiles’ and the Export to Excel solution written by Ken Pespisa.
In order to display the ‘Export profiles’ in the dialoglist I had to re-write Ken’s solution a bit. I advice you to use Ken’s standard solution in stead.
Well, the rest of this export wizard is just following Ken’s work.
What I tried to achieve with this solution is:
- having an easy way for an administrator to define ‘Export profiles’ based upon information on Notes forms.
- presenting an understandable interface for the end-user that enables to create customizable exports to Excel
- get rid of a large set of nasty Notes views!
A demo you can download here.