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: OptionPublicOptionDeclareDim fieldList ListAsInteger'The row for a specific field nameDim fieldCount AsInteger'How many unique fieldnames do we have 0 basedSubInitializeDim db AsNotesDatabaseDim s AsNewNotesSessionDim out AsNotesStreamDim curForm AsNotesFormDim filename AsStringDim formList ListAsVariant 'A list with arrays of fiields Dim formCount AsInteger'How many forms do we have 0 basedDim fieldArray ()AsBooleanOnErrorGoto Err_Initialize fileName =Inputbox("Where to store the result")If fileName =""ThenExitSubEndIfSet out = s .CreateStream Call out .Open(fileName )Call out .Truncate formCount =-1 fieldCount =-1 Set db = s .CurrentDatabase 'Extract all the fields and formsForall f In db .Forms formCount = formCount + 1 Set curForm = f formList (curForm .Name)= curForm .Fields Call extractFields (curForm .Fields )EndForall'Now size the arrayRedim 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 arrayDim i AsIntegerDim j AsInteger 'Now fill the array and the first table out .WriteText (|<tr><th>Field/Form</th>|) i =-1 'Reset i as RowcounterForall curFields In formList i = i + 1 For j = 0 ToUbound(curFields ) fieldArray (fieldList (curFields (j )),i )=TrueNextCall out .WriteText (|<th>|) out .WriteText Listtag(curFields )Call out .WriteText (|</th>|)EndForallCall out .WriteText (|</tr>|)'Now the matrix in its full beautyForall curFields2 In FieldList out .WriteText (|<tr><td>|+Listtag(curFields2 )+|</td>|)'curFields2 has the row, we loop through the columnsFor 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>|)EndIfNext out .WriteText (|</tr>|)EndForall'Footer out .WriteText (|</table></body></html>|) Exit_Initialize :OnErrorResumeNextCall out .CloseExitSub Err_Initialize :MsgboxError$Resume Exit_Initialize EndSubSub extractFields (allFields AsVariant)'fieldList, fieldcount are globlaForall curFieldName In allFields IfNotIselement(fieldList (curFieldName ))Then'We only add fields we don't have in the list fieldcount = fieldCount + 1 fieldList (curFieldName )= fieldCount EndIfEndForallEndSub
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">