LotusScript and Lotus Notes 8.5.3

2510 pts.
Tags:
Lotus Notes
LotusScript
How to get field names and corresponding count of empty fields from a Lotus Notes view. I want all the field names and corresponding count of empty/ null/ blank values from the documents in a view.

Software/Hardware used:
Lotus notes

Answer Wiki

Thanks. We'll let you know when a new response is added.

I was trying to do this and came with following script , it works:

Dim s As NotesSession
Dim db As NotesDatabase
Dim doc As NotesDocument
Dim Totalfields As Variant
Dim i As Integer
Dim count As Integer
Dim keyview As NotesView
Dim view As NotesView
Dim check As variant
Dim sum As Integer
Dim myobject As Variant
Dim mysheet As Variant
Dim j As integer
‘Dim doccoll As notesdocumentcollection
Dim arrayfields() As String
Dim arrayfldcount() As integer
Dim uplimit As integer
Const keyvwname =”view stroring field names”              ‘keyword view name where field names  are stored
Const viewname=”vwtest”                  ‘ view where the documents are tracked to find field count
Const fieldsarrayname=”field list”         ‘name of the field where field names are stored
Const primarykey=”//KeyFields” ‘ primary keyword name ie. category name under which key fields are stored.
Sub Initialize
Set s=New NotesSession
Set db=s.currentdatabase
On Error GoTo hndlError
Set keyview =db.getview(keyvwname)
Set doc=keyview.Getdocumentbykey(primarykey, true)
Totalfields=doc.Getitemvalue(fieldsarrayname)
uplimit=UBound(Totalfields)
ReDim arrayfields(uplimit) As String 
ReDim arrayfldcount(uplimit) As Integer
Set view=db.Getview(Viewname)
Set doc=view.getfirstdocument
sum=0
While Not doc Is Nothing
i=0
ForAll x In Totalfields
check=doc.getitemvalue(x)
Print check(0)
If CStr(check(0))=”” Then
sum=arrayfldcount(i)
arrayfldcount(i)=sum+0
Else
sum=arrayfldcount(i)
arrayfldcount(i)=sum+1
End If
i=i+1
End ForAll
Set doc=view.Getnextdocument(doc)
sum=0
Wend
Print “Please Be Patient as the Excel Spreadsheet is being created…”
Set myobject=createObject(“Excel.application”)
On Error GoTo theError
myobject.statusbar=”Creating Excel Sheet”
‘add the object
myobject.workbooks.add
Set mysheet=myobject.workbooks(1).Worksheets(1)
mysheet.name=”DATA”
mysheet.cells(1, 2).value=”Field non empty count display  “
mysheet.cells(1, 2).Font.Size=14
mysheet.cells(1, 2).Font.ColorIndex=5
mysheet.cells(1, 2).Font.Bold = True
mysheet.Range(“A1:F1″).MergeCells = True
i=2
j=0
ForAll x In Totalfields
mysheet.cells(i, 1).value=x
mysheet.cells(i, 2).value=arrayfldcount(j)
mysheet.cells(i, 1).Font.Size=14
mysheet.cells(i, 1).Font.ColorIndex=1
mysheet.cells(i, 1).Font.Bold = True
mysheet.cells(i, 2).Font.Size=14
mysheet.cells(i, 2).Font.ColorIndex=1
mysheet.cells(i, 2).Font.Bold = True
i=i+1
j=j+1
End ForAll
mysheet.Columns.autofit
mysheet.rows.autofit
Print “Spreadsheet created”
myobject.visible=True
Exit sub
theError:
myobject.quit
Print Error()
End
hndlError:
MsgBox (“Error:===>Error number “+ CStr(Err)+” due to “+Error )
Exit sub
End Sub

Discuss This Question:  

 
There was an error processing your information. Please try again later.
Thanks. We'll let you know when a new response is added.
Send me notifications when members answer or reply to this question.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy

Forgot Password

No problem! Submit your e-mail address below. We'll send you an e-mail containing your password.

Your password has been sent to:

To follow this tag...

There was an error processing your information. Please try again later.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy

Thanks! We'll email you when relevant content is added and updated.

Following