Usability - Productivity - Business - The web - Singapore & Twins

Fields in Forms Matrix

When cleaning up existing applications it is good to have an overview what fields are used across forms. Unfortunately the synopsis isn't very helpful there. However with a few lines of LotusScript one can create a matrix that serves as an overview. It is a comparison by name only and doesn't tell you anything about computation mode or data-type. But it is a start. YMMV
'CreateFieldMatrixReport: Option Public Option Declare Dim fieldList List As Integer 'The row for a specific field name Dim fieldCount As Integer 'How many unique fieldnames do we have 0 based Sub Initialize Dim db As NotesDatabase Dim s As New NotesSession Dim out As NotesStream Dim curForm As NotesForm Dim filename As String Dim formList List As Variant 'A list with arrays of fiields Dim formCount As Integer 'How many forms do we have 0 based Dim fieldArray ( ) As Boolean On Error Goto Err_Initialize fileName = Inputbox ( "Where to store the result" ) If fileName = "" Then Exit Sub End If Set out = s .CreateStream Call out . Open (fileName ) Call out .Truncate formCount = -1 fieldCount = -1 Set db = s .CurrentDatabase 'Extract all the fields and forms Forall f In db .Forms formCount = formCount + 1 Set curForm = f formList (curForm . Name ) = curForm .Fields Call extractFields (curForm .Fields ) End Forall 'Now size the array Redim fieldArray (fieldCount ,formCount ) 'Header out .WriteText ( |<html><head><title>Field Matrix</title></head><style>.yes {background-color : red; color : white }| ) out .WriteText ( | .no { color : white} table {width : 100%} td {text-align : center}</style></head><body><table>| ) 'Now fill the array Dim i As Integer Dim j As Integer 'Now fill the array and the first table out .WriteText ( |<tr><th>Field/Form</th>| ) i = -1 'Reset i as Rowcounter Forall curFields In formList i = i + 1 For j = 0 To Ubound (curFields ) fieldArray (fieldList (curFields (j ) ) ,i ) = True Next Call out .WriteText ( |<th>| ) out .WriteText Listtag (curFields ) Call out .WriteText ( |</th>| ) End Forall Call out .WriteText ( |</tr>| ) 'Now the matrix in its full beauty Forall curFields2 In FieldList out .WriteText ( |<tr><td>| + Listtag (curFields2 ) + |</td>| ) 'curFields2 has the row, we loop through the columns For j = 0 To formCount If fieldArray (curFields2 ,j ) Then 'it is boolean after all out .WriteText ( |<td class="yes">+</td>| ) Else out .WriteText ( |<td class="no">-</td>| ) End If Next out .WriteText ( |</tr>| ) End Forall 'Footer out .WriteText ( |</table></body></html>| ) Exit_Initialize : On Error Resume Next Call out . Close Exit Sub Err_Initialize : Msgbox Error$ Resume Exit_Initialize End Sub Sub extractFields (allFields As Variant ) 'fieldList, fieldcount are globla Forall curFieldName In allFields If Not Iselement (fieldList (curFieldName ) ) Then 'We only add fields we don't have in the list fieldcount = fieldCount + 1 fieldList (curFieldName ) = fieldCount End If End Forall End Sub
This LotusScript was converted to HTML using the ls2html routine,
provided by Julian Robichaux at nsftools.com.

Posted by on 15 September 2008 | Comments (3) | categories: Show-N-Tell Thursday


  1. posted by Marius Neumann on Monday 15 September 2008 AD:
    Thanks for the nice piece of code. Its working fine here!

    Unfortunately, for me it's usually not sufficient to search for forms when it comes to cleaning up or renaming Notes fields. I prefer searching then whole DB design, e.g. with a free tool like this one: { Link }
  2. posted by Theo Heselmans on Monday 15 September 2008 AD:
    Very interesting. Tx Stephan
  3. posted by Kevin Pettitt on Monday 15 September 2008 AD:
    Great idea Stephan. Certainly a good free option. You can get a similar data using one of the "Design views" in SuperNTF, which shows all fields categorized by Form/Subform.

    However, I normally use Teamstudio Analyzer for this, which does provide information about datatype, and I even created a custom view for the Analyzer template to show more of this detail. You can download my "Analyzer Plus" custom views from OpenNTF here: { Link }

    One idea for enhancing this general technique here would be to parsing the form DXL, which also provides datatype and fieldtype detail (and much more). For example:

    <field type="text" kind="editable" name="Form" allowmultivalues="false" protected="false" sign="false" computeaftervalidation="false" defaultfocus="false" showdelimiters="true" allowtabout="false" useappletinbrowser="false" storelocally="false" dataconnectionfield="data">