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 Stephan H Wissel on 15 September 2008 | Comments (3) | categories: Show-N-Tell Thursday

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 }

posted by Theo Heselmans on Monday 15 September 2008 AD:
Very interesting. Tx Stephan

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">