Notes of my Lotus Notes Headline Animator

Search My Blog

Showing posts with label Notes Client. Show all posts
Showing posts with label Notes Client. Show all posts

Tuesday, August 2, 2016

Exporting/Logging e-mail details in some excel sheet from any Folder or Inbox

If you want all these details logged in some excel sheet

1.     Select the documents and go to File menu > select Export...

 2.     Select folder in which you want to save.
         Select Save as type as Comma separated value.
         Give some name, say, attachment_logs.csv.
         Then click on Export button.
 3.     It would bring another small dialogue box.
         If you want details of selected mails then select How Much to Export as 'Selected documents'.
         Otherwise you may select 'All documents'.
         Export Character Set keep as 'Default character set'.
         Check the Details to Incorporate as 'Include View titles'.
         Otherwise just with the data you may get confused.
         Especially if you are NOT a lotus notes developer, then you would get confused with the extra column values that get exported.

4.     Once the export is completed you would see the below message in the bottom pane of your mailbox.
        You may delete the extra columns that get populated in the .csv file and keep only those you need.

 The result would look somewhat like this:

Hope this helps!! :)

Friday, December 21, 2012

Notes document to Excel sheet - Exporting data - client form

Code for exporting data from notes documents to an excel sheet:


Sub Initialize
 Dim ws As New Notesuiworkspace
 Dim session As New NotesSession
 Dim view As NotesView
 Dim db As NotesDatabase
 Dim doc As notesdocument
 Dim psno,mth,yr,filename As String

 Set db=session.CurrentDatabase
 Set view = db.GetView( "Vashi_emp" )
 Set doc = view.GetFirstDocument

 CreateExcelObject = True
 Set xlApp = CreateObject("Excel.Application")
 If xlApp Is Nothing Then
  sMessage = "Could not create spreadsheet." & Chr$( 10 ) & _
  "Make sure Excel is installed on this computer."
  Msgbox sMessage, 16, "Creation of Spreadsheet Object Failed"
  CreateExcelObject = False

 End If
 xlApp.DisplayAlerts = False
 Set xlworkbook = xlApp.Workbooks.Add
 Set xlSheet=xlworkbook.ActiveSheet
 xlSheet.Name="ATTENDANCEREPORT"

 row=1
 col=1
 xlapp.visible = True
 Title = " Attendance Report"
 xlsheet.Cells(row,col+2).value=Title
 xlApp.Rows("1:1").select
 xlapp.Selection.font.Bold=True
 xlapp.Selection.font.underline=True
 col = 1

 xlsheet.Cells(row+2,col).value="PSNO"
 xlApp.Rows("3:3").select
 xlapp.Selection.font.Italic=True
 xlapp.selection.Font.Name="Arial Black"
 xlapp.selection.Font.size ="10"
 xlapp.Selection.font.Bold=False
 Print row
 xlsheet.Cells(row+2,col+1).value="name"
 xlApp.Rows("3:3").select
 xlapp.Selection.font.Italic=True
 xlapp.selection.Font.Name="Arial Black"
 xlApp.selection.Columns.Autofit
 xlapp.selection.Font.size ="10"
 xlapp.Selection.font.Bold=False
 Print col
 xlsheet.Cells(row+2,col+2).value="dlgeusername"
 xlApp.Rows("3:3").select
 xlapp.Selection.font.Italic=True
 xlapp.selection.Font.Name="Arial Black"
 xlApp.selection.Columns.Autofit
 xlapp.selection.Font.size ="10"
 xlapp.Selection.font.Bold=False
 xlsheet.Cells(row+2,col+3).value="status"
 xlApp.Rows("3:3").select
 xlapp.Selection.font.Italic=True
 xlapp.selection.Font.Name="Arial Black"
 xlApp.selection.Columns.Autofit
 xlapp.selection.Font.size ="10"
 xlapp.Selection.font.Bold=False
 xlsheet.Cells(row+2,col+4).value="costcode"
 xlApp.Rows("3:3").select
 xlapp.Selection.font.Italic=True
 xlapp.selection.Font.Name="Arial Black"
 xlApp.selection.Columns.Autofit
 xlapp.selection.Font.size ="10"
 xlapp.Selection.font.Bold=False
 xlsheet.Cells(row+2,col+5).value="cader"
 xlApp.Rows("3:3").select
 xlapp.Selection.font.Italic=True
 xlapp.selection.Font.Name="Arial Black"
 xlApp.selection.Columns.Autofit
 xlapp.selection.Font.size ="10"
 xlapp.Selection.font.Bold=False
 xlsheet.Cells(row+2,col+6).value="location"
 xlApp.Rows("3:3").select
 xlapp.Selection.font.Italic=True
 xlapp.selection.Font.Name="Arial Black"
 xlApp.selection.Columns.Autofit
 xlapp.selection.Font.size ="10"
 xlapp.Selection.font.Bold=False
 xlsheet.Cells(row+2,col+7).value="joindate"
 xlApp.Rows("3:3").select
 xlapp.Selection.font.Italic=True
 xlapp.selection.Font.Name="Arial Black"
 xlApp.selection.Columns.Autofit
 xlapp.selection.Font.size ="10"
 xlapp.Selection.font.Bold=False
 xlsheet.Cells(row+2,col+8).value="confirmdate"
 xlApp.Rows("3:3").select
 xlapp.Selection.font.Italic=True
 xlapp.selection.Font.Name="Arial Black"
 xlApp.selection.Columns.Autofit
 xlapp.selection.Font.size ="10"
 xlapp.Selection.font.Bold=False
 xlsheet.Cells(row+2,col+9).value="deptname"
 xlApp.Rows("3:3").select
 xlapp.Selection.font.Italic=True
 xlapp.selection.Font.Name="Arial Black"
 xlApp.selection.Columns.Autofit
 xlapp.selection.Font.size ="10"
 xlapp.Selection.font.Bold=False
 xlsheet.Cells(row+2,col+10).value="deptcode"
 xlApp.Rows("3:3").select
 xlapp.Selection.font.Italic=True
 xlapp.selection.Font.Name="Arial Black"
 xlApp.selection.Columns.Autofit
 xlapp.selection.Font.size ="10"
 xlapp.Selection.font.Bold=False

 grow  = row+3
 Print grow
 row = grow
 col = 1
 j=0

 While Not ( doc Is Nothing )

  Dim currentProduct As Variant
  For  i=0 To 8
   currentProduct = doc.ColumnValues( i )
 
   xlsheet.Cells(row+j,col+i).value=currentProduct
  Next
  j=j+1
  Set doc = view.GetNextDocument( doc )
 Wend

End Sub

Excel sheet to notes Document - Importing Data

Importing excel sheet information into individual documents in LN client application:


Sub Initialize

Dim xlFilename As String
'xlFilename = "C:\Documents and Settings\ggouda\My Documents\EMP_WRS_DETAIL.xls"

'// This is the name of the Excel file that will be imported

xlFilename=Inputbox("Please enter path of the spreadsheet - Example: C:\Excel.xls" & Chr(10) &_
" C:\spreadsheet.xls", "File Path Inquiry Box", " enter path here ....")


Dim session As New NotesSession
Dim db As NotesDatabase
Dim view As NotesView
Dim doc As NotesDocument
Set db = session.CurrentDatabase
Set doc = New NotesDocument(db)
Dim One As String

Dim row As Integer
Dim written As Integer


'// Connect to Excel and open the file. Start pulling over the records.
Dim Excel As Variant
Dim xlWorkbook As Variant
Dim xlSheet As Variant
Print "Connecting to Excel..."
Set Excel = CreateObject( "Excel.Application.9" )
Excel.Visible = False '// Don't display the Excel window
Print "Opening " & xlFilename & "..."
Excel.Workbooks.Open xlFilename '// Open the Excel file
Set xlWorkbook = Excel.ActiveWorkbook
Set xlSheet = xlWorkbook.ActiveSheet

'// Cycle through the rows of the Excel file, pulling the data over to Notes
Goto Records
Print "Disconnecting from Excel..."
xlWorkbook.Close False '// Close the Excel file without saving (we made no changes)
Excel.Quit '// Close Excel
Set Excel = Nothing '// Free the memory
Print " " '// Clear the status line


Records:
row = 1 '// Integers intialize to zero
written = 0

Print "Starting import from Excel file..."
Do While True
Finish:

With xlSheet
row = row + 1
Set view = db.GetView("Import")
Set doc = db.CreateDocument '// Create a new doc
doc.Form = "Person"

doc.Employee_ID = .Cells( row, 1 ).Value
doc.Prj_Cost_Centre= .Cells(row, 2 ).Value
doc.WRS_Number= .Cells(row, 3).Value
doc.Proj_Title = .Cells( row, 4 ).Value
doc.Project_Location = .Cells(row, 7).Value
doc.Dispatcher = .Cells( row, 8).Value
doc.Project_manager = .Cells(row, 9).Value
doc.Wk_Ending = .Cells( row, 10).Value
doc.Employee_DOJ = .Cells( row, 11).Value
doc.Employee_Name = .Cells( row, 12).Value
doc.Employee_Role = .Cells( row, 13).Value
doc.ST_Hrs = .Cells( row, 14).Value
doc.OT_Hrs = .Cells( row, 15).Value
doc.Total_Hrs = .Cells( row, 16).Value
doc.Misc_Exp = .Cells( row, 17).Value
doc.Travel = .Cells( row, 18).Value
doc.Total_Bill = .Cells( row, 19).Value

Call doc.Save( True, True ) '// Save the new doc
written = written + 1
Print Str(written)
If .Cells( row, 1 ).Value = "" Then
Goto Done
End If
End With

Loop
Return
Done:
Messagebox "Import Complete - Total number of WRS documents imported ---> " & written


End Sub

Friday, October 14, 2011

To refresh the document

To refresh the entire document in client : @Command([ViewRefreshFields)
And to refresh entire document in web : _doClick('$Refresh', this, '_self', '#_RefreshKW_')

Monday, August 22, 2011

$$Fields and Reserved Notes Field Names Glossary



I got this content long back from some website.. can't remember.
I found it very useful. So, here it is for your use..


$$Fields and Reserved Notes Field Names Glossary
Anonymous on 08/26/1999 at 07:51 PM

Category: Notes Developer Tips
Forms/Subforms, Formulas
$$ Fields

$$HTMLhead
If you don't use the "For Web Access: Treat document contents as HTML" form property, adding a $$HTMLHead field to a form allows you to pass HTML information, such as Meta tags and JavaScript, to the Head tag for a document. The field can be any data type, but a hidden computed-for-display text field is the best choice.
(source: Notes Help 4.6)

$$QueryOpenAgent (4.6) or the form event WebQueryOpen (4.6)
The Notes Help 4.6 says about WebQueryOpen: A WebQueryOpen event runs the agent before Domino converts a document to HTML and sends it to the browser. Domino ignores any output produced by the agent in this context.
Create a shared agent that runs manually. Then write a formula that uses @Command({ToolsRunMacro}) to run the agent and attach it to the WebQueryOpen form events.
This simulates the LotusScript QueryOpen form event that isn't supported on the Web.
The $$QueryOpenAgent field works in the same way. Works also in 4.6.

Thursday, August 4, 2011

Validating a rich text field

Validating a rich text field:

http://www.ibm.com/developerworks/lotus/library/rich-text-field-notes/

I have copy pasted the three methods from the above link for my reference.

Method 1

In the first method, if the field contains any input (even only a single space character), then it can pass the validation. This validation uses the Querysave event of the form that contains the field. The following LotusScript sample code performs this validation:


Sub Querysave(Source As Notesuidocument, Continue As Variant)
            If ( Source.FieldGetText( "rtfield" ) = "" ) Then
                 Messagebox( "Please enter some text." )
                 Call Source.GotoField( "rtfield" )
                 Continue = False
         End If
End Sub


The code checks for any character in the field rtfield. If the field contains input (even if it consists solely of one or more space characters), then validation succeeds. If the field is empty, then the code returns an error message, and doesn’t save the document (by setting Continue to False).

Method 2

In the second method, the rich text field must contain at least one non-space character (in other words, an input consisting entirely of one or more spaces is not allowed). This check also uses the Querysave event of the form:


Sub Querysave(Source As Notesuidocument, Continue As Variant)
        
         Dim rtitem As NotesRichTextItem
         Set doc = Source.Document
         Set rtitem = doc.GetFirstItem( "rtfield" )
         Dim text As String
        
         text$ = Source.FieldGetText("rtfield")
         trimmed$ = Trim(text)
           
            if ( trimmed$ = "") Then
                 Msgbox "Please enter some text."
                 Continue = False
                 source.GotoField("rtfield")
                 source.Refresh(True)
         Else
                 Continue = True
                
         End If
End Sub


Method 3

Our third method validates a rich text field in which an input consisting solely of an attachment, embedded object, or link is allowed, even if it includes no accompanying text. Once again, we use the Querysave event of the form containing the field:



Sub Querysave(Source As Notesuidocument, Continue As Variant)
        
         Dim rtitem As NotesRichTextItem
         Set doc = Source.Document
         Set rtitem = doc.GetFirstItem( "rtfield" )
         Dim text As String
        
         text$ = Source.FieldGetText("rtfield")
         trimmed$ = Trim(text)
        
         If(doc.Hasembedded)  Then
                 Continue = True
                                  
         Elseif ( trimmed$ = "") Then
                 Msgbox "Please enter some text."
                 Continue = False
                 source.GotoField("rtfield")
                 source.Refresh(True)
         Else
                 Continue = True
        
            End If
End Sub


This code will work if there is an attachment anywhere in the document, even if it's not in the field that is being validated.

Wednesday, October 13, 2010

Object variable not set Error in Preferences/Calendar Profile

When saving some changes to the CalendarProfile, in other words called as Preferences, you may get the following error message repeatedly sometime after opening it / sometime after click the OK button to save the changes.

This happens usually in the mail-in applications.
Object variable not set
On Module: CLICK
On Line: 32
On Method: CLICK

Friday, August 27, 2010

Hiding a Rich-Text field in Lotus Notes

Hiding a rich-text field. This is what my sis has been trying to achieve from past 2 hours now. Ah... it feels so irritating when you try to use all sorts of hide-when condition and it doesn't work. We tried normal field properties hide-when condition, tried sections, tried computed/computed for display etc. It did not hide.

Then I tried to google about it. It showed me several links with all stories in it. In the summary of a link I saw the name "subform". Eureka!!! I found it.


Automatically Moving Return Receipts to Folder

I wanted to move the Return Receipts that I receive to my mailbox, to be moved to the folder I created under Folders, named as "Return Receipts".

Tried some agents - did not work.
Tried rules that check for the Subject field containing "RECEIVED:" - did not work.

Then I forgot about it.

Few days back I again recollected of this task. I tried to check some unique fields or field values that differentiates the Return Receipts from the normal Memo.
I tried hard, could not find any unique field.
And suddenly my attention went to the Form field.
Yes, yes, yes. I found it.

Now, the job is to check if the Rules section takes care of the Form field or not. Or is it just for the Subject field only.

And I found it...



Now all my Return Receipts automatically go to the folder that I created, instead of remaining in Inbox.


Wednesday, April 28, 2010

Ways to do Version Controlling/Tracking

Version tracking is a feature that allows you to maintain the history of changes made in a document. And is another interview question.

Version tracking can be achieved in 2 ways. First through the Form properties and the Second is by using the reserved field $VersionOpt.

Wednesday, April 7, 2010

Replicate or copy documents and $KeepPrivate

a> How to stop users from creating local replicas or local copies?

b> How to stop users from copying / printing / forwarding any documents from the database?

c> How to stop users from copying / forwarding / printing an e-mail that you sent?

Tuesday, April 6, 2010

Authors vs Readers

Readers and Authors fields - a common interview question. I remember whatever interviews I have given in any organisation or who ever I have interviewed I have always asked them questions related to Readers and Authors field with lot of permutation and combination. Today I thought of documenting whatever I know about these fields, so I can refer to it whenevevr I want and brush up my brain a little if I have forget anything in future. 

Monday, April 5, 2010

Manager vs Designer

Manager

  1. Can modify the database ACL.
  2. Can encrypt the database.
  3. Can modify replication settings.
  4. Has the right to delete the database.
  5. Can perform all tasks that are allowed to the lower accesses; i.e. Designer, Editor, Author, Reader, Depositor.
Designer

  1. Can modify all the design elements in the database.
  2. Can create a full-text search index in the database properties.
  3. Can perform all the tasks that are allowed to the lower accesses; i.e. Editor, Author, Reader, Depositor.

Friday, March 26, 2010

Gradient effect in Lotus Notes Client and Web applications

Creating Gradient effect in Lotus Notes client is very easy. You just have to go to create a table in the form/page. Then in the Table Properties > in the Table/Cell Background tab you select Cell color. Then you select the type of style and choose the colors for the gradient. And your table cells are ready with the gradient effect.

However, this gradient effect is not visible in web. Thus, you have to find some other way to color the cells with gradient effect.

Thursday, March 25, 2010

Field is too large - 01

Problem Description

Error Encountered: “Field is too large (32K) or View’s column & selection formulas are too large”

Problem Scenario:
You have recently deployed some of your changes and suddenly every document gets locked. You can’t edit any existing documents or save a new document. When you close or open any document you get the following error:


You can’t do anything with the database. You check in your test copy and everything works perfectly fine there. You check the production copy’s data size and it is not too much to trigger the error. This error doesn’t let anyone to make any changes to the database.