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