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

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_')

$$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.

$$QuerySaveAgent (4.5) or the form event WebQuerySave (4.6)
The Notes Help 4.6 says about WebQuerySave: A WebQuerySave event runs the agent before the document is actually saved to disk. The agent can modify the document or use the document data to perform other operations.
Create a shared agent that runs manually. Then write a formula that uses @Command({ToolsRunMacro}) to run the agent and attach it to the WebQuerySave form events.
This simulates the LotusScript QuerySave form event that isn't supported on the Web.
The $$QuerySaveAgent field works in the same way. Works also in 4.6.


$$Return
After Web users submit a document, Domino responds with the default confirmation "Form processed." To override the default response, add a computed text field to the form, name it $$Return, and use HTML as the computed value to create a customized confirmation.
(source: Notes Help 4.6)


$$ViewBody
Value is view name (in quotes) or a formula that computes the view name. Same as Embedded View.
(source: Notes Help 4.6)
This is the field you put in a form to display a view. See also the list of $$ forms below.


$$ViewList
Has no value. Same as Embedded Folder Pane.
(source: Notes Help 4.6)
This is the field you put in a form to display the list of available views and folders. See also the list of $$ forms below.


$$NavigatorBody, $$NavigatorBody_n
Value is navigator name (in quotes) or a formula that computes the navigator name.
Same as Embedded Navigator. To create multiple $$NavigatorBody fields on a form, append an underscore and a character to each subsequent field name.
(source: Notes Help 4.6)
This is the field you put in a form to display a navigator. See also the list of $$ forms below.




The following fields are not $$ fields, but they are reserved names in a Web context.


Table of CGI variables from the Notes Help 4.6


Domino captures the following CGI variables through a field or a LotusScript agent. You can also capture any CGI variable preceded by HTTP or HTTPS. For example, cookies are sent to the server by the browser as HTTP_Cookie.


Auth_Type
If the server supports user authentication and the script is protected, this is the protocol-specific authentication method used to validate the user.

Content_Length
The length of the content, as given by the client.

Content_Type
For queries that have attached information, such as HTTP POST and PUT, this is the content type of the data.

Gateway_Interface
The version of the CGI spec with which the server complies.

HTTP_Accept
The MIME types that the client accepts, as specified by HTTP headers.

HTTP_Referer
The URL of the page the user used to get here.

HTTPS
Indicates if SSL mode is enabled for the server.

HTTP_User_Agent
The browser that the client is using to send the request.

Path_Info
The extra path information (from the server's root HMTL directory), as given by the client. In other words, scripts can be accessed by their virtual path name, followed by extra information that is sent as PATH_INFO.

Path_Translated
The server provides a translated version of PATH_INFO, which takes the path and does any virtual-to-physical mapping to it.

Query_String
The information that follows the ? in the URL that referenced this script.

Remote_Addr
The IP address of the remote host making the request.

Remote_Host
The name of the host making the request.

Remote_Ident
This variable will be set to the remote user name retrieved from the server. Use this variable only for logging.

Remote_User
Authentication method that returns the authenticated user name.

Request_Method
The method used to make the request. For HTTP, this is "GET," "HEAD," "POST," and so on.

Script_Name
A virtual path to the script being executed, used for self-referencing URLs.

Server_Name
The server's host name, DNS alias, or IP address as it would appear in self-referencing URLs.

Server_Protocol
The name and revision of the information protocol accompanying this request.

Server_Port
The port to which the request was sent.

Server_Software
The name and version of the information server software running the CGI program.

Server_URL_Gateway_Interface
The version of the CGI spec with which the server complies.


Associated with a lot of the $$ fields are the following forms:

$$ViewTemplate for viewname
Requires a embedded view or $$ViewBody field
Associates the form with a specific view. The form name includes viewname, which is the alias for the view or when no alias exists, the name of the view. For example, the form named "$$ViewTemplate for By Author" associates the form with the By Author view.

Domino requires an Embedded View or the $$ViewBody field on the form, but ignores the value.
(source: Notes Help 4.6)

$$NavigatorTemplate for navigatorname
Requires a embedded navigator or $$NavigatorBody field.
Associates the form with a specific navigator. The form name includes navigatorname, which is the navigator name. For example, the form named "$$NavigatorTemplate for World Map" associates the form with the World Map navigator.
Domino requires an embedded navigator or the $$NavigatorBody field on the form, but ignores the value. Domino ignores create and read access lists on the form.
(source: Notes Help 4.6)

$$ViewTemplateDefault
Requires a embedded view or $$ViewBody field.
Makes this form the template for all Web views that aren't associated with another form.
Domino requires an embedded view or the $$ViewBody field on the form, but ignores the value.
(source: Notes Help 4.6)

$$NavigatorTemplateDefault
Requires a embedded navigator or $$NavigatorBody field.
Makes this form the template for all Web navigators that aren't associated with another form.
Domino requires an embedded navigator or the $$NavigatorBody field on the form, but ignores the value.
(source: Notes Help 4.6)

$$SearchTemplate for viewname
Associates the form with a specific view. Domino requires the $$ViewBody field, but ignores the value. The form name includes viewname, which is the alias for the view, or, when no alias exists, the name of the view. For example, the form named "$$SearchTemplate for All Documents" associates the form with the All Documents view.
(source: Notes Help 4.6)

$$SearchTemplateDefault
Domino requires the $$ViewBody field, but ignores the value. This form is the default for all Web searches that aren't associated with a specific form.
(source: Notes Help 4.6)

$$SearchSiteTemplate
In a site search this form is used in stead of $$SearchTemplateDefault
(source: posting by Andrew S Grant on Notes.Net)

$$Search
When a user initiates a text search from the Web, Domino looks in the current database or in the search site database in a multiple-database search for a form with the actual name or the alias name $$Search. If the form exists, Domino opens it; otherwise, Domino displays the default search.htm file stored in the domino\icons directory.
(source: Notes Help 4.6)


And another set of very useful forms (i didn't even know they exsisted until a few moments
before):

$$ReturnDocumentDeleted
Displays to Web users when the user successfully deletes documents. Create an editable text field named MessageString to hold the error message.
(source: Notes Help 4.6)

$$ReturnAuthenticationFailure
Displays to Web users when the user's name and password can't be verified. Create an editable text field named MessageString to hold the error message.
(source: Notes Help 4.6)

$$ReturnAuthorizationFailure
Displays to Web users when the user doesn't have a high enough access level to access the database.
Create an editable text field named MessageString to hold the error message.
(source: Notes Help 4.6)

$$ReturnGeneralError
Displays to Web users when any other errors occur.. Create an editable text field named MessageString to hold the error message.
(source: Notes Help 4.6) 

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.

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

When you reopen the document, you find that changes are lost. None of those are saved.
Even the signature, if you update any, are missing.

Thus this issue results in data loss.
This error is recently found in two of my friend's application.

The Notes Admin guy had upgraded the application from R7 to R8.
Thus, you may think it happened because of the version change.
Or there must be some problem with the template / form's code.

However, the solution reveals that it's not a notes development issue.
The issue would even show if the version is R7 template.

After debugging the form's code and it's Script libraries; it's found that the script is looking for the person's name in the ACL as an individual entry i.e. as Owner of the application.
Logically only Owner should make any changes to the Preferences, not just everyone.
Now, if there are no Owners of the applications and everyone/anyone tries to update the preferences, they it should not show the changes. And that's what it does in this case.

If you are trying to debug the issue and you add your name in ACL individually, apart from the Group entry, and then save the changes it would save the data. And the error message would not show.

After the same is done for those two applications and it worked.
Just make sure every application has its Owner assigned in the ACL. So, that when the owner tries to update the preferences document, he would not get any error message and would not escalate the issue.

http://www-10.lotus.com/ldd/nd8forum.nsf/4b9931b774db788c85256bf0006b5e6d/d41e903c42b2c93a852577bb0048a59f?OpenDocument

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.

Then she created two subforms, ReadBody and EditBody and used the following formula in my main form.
@If(@IsMember("[< Role >]";@UserRoles);"< ReadOnlySubform >";"< EditOnlySubform >")

In the EditBody subform she created a RichText field with name as Body and Type as Editable, with default value as Body.

In the ReadBody subform she created a RichText field with name as dispBody and Type as Computed, with default value as Body.

In the main form, she added the subform as Computed one; set the formula as:

@If(@IsMember("[Roster Admin]";@UserRoles); "ReadBody"; "EditBody")

Thus, for the people with a specific role, the RichText field is editable and for other it's a read-only field.

It's now so easy to hide RichText fields or just making them Read-Only.

Hope you find this solution valuable.

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.

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.

A. To achieve version tracking through form you need to designate the form as version-tracking form. When you design the form, in the Form Properties > Form Info tab, you can see a section called Versions. It has two drop downs to select. The first one is Versioning with the values as:

i.           New versions become responses

ii.          Prior versions become responses

iii.         New versions become siblings

These descriptions are self-explanatory. After you select a value another drop down, Create versions, will be enabled with the following values:

a. Manual – File, New Version

b. Automatic – File, Save

This would enable the form for version tracking and any changes made to the documents out of this form would be saved as responses / siblings as per the selection made.

B. The other way of enabling version tracking/control is to use the reserved field $VersionOpt.

$VersionOpt field allows you to have version control facility. It allows users to create new version of edited documents on a document-by-document basis.

You make this field as either Editable or Computed. The Computed for display / Computed when composed field types do not work for this reserved field.

If you want all documents created from a form to have same version control option then make it as Computed Text field and specify the Value as any of the numbers from 0 to 6.

The numbers are values for different types of version tracking; i.e.

0 ~ No version tracking
1 ~ New versions become responses if users choose File - Save As New Version when saving
2 ~ New versions automatically become responses when saved
3 ~ Prior versions become responses if users choose File - Save As New Version when saving
4 ~ Prior versions become responses when saved
5 ~ New versions become siblings if users choose File - Save As New Version when saving
6 ~ New versions automatically become siblings when saved

If you want users to have different version methods for different documents created from the same form, then have this field as Editable and select field type as Dialogue List. In the Control tab select Choices as Enter choices (one per line) and add the options 0 to 6 with the user understandable descriptions; e.g.

Don't track versions | 0
Create response if File - Save as New Version is used | 1
Create response automatically | 2
Promote to main document if File - Save as New Version is used | 3
Promote to main document automatically | 4
Create additional main document if File - Save as New Version is used | 5
Create additional main document automatically | 6

Thus, user can make a selection for the type of version controlling that she wants.

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?

The answer of the questions a>, b> is: Deselect the option Replicate or copy documents privilege from the ACL for the users, groups etc.

This option when selected/deselected allows/prohibits respectively the following:

1. Users to create local replicas or local copies of a database.
2. Select content from a document opened in read mode.
3. Copying, printing, or forwarding documents in the database.
4. Controls the replication of the documents with $KeepPrivate.

Deselecting the option prohibits users from printing, copying, forwarding documents. However, still there are chances that user may take a print screen and print the data from the image file. They can also copy the data from the document to the clipboard and save it as word file or text file and print that.

That’s how you can control this via design specifications i.e. through ACL in a particular database.

Now, how to restrict the user from copying / forwarding / printing the documents / e-mails that you sent to another database / someone’s mailbox? They have usually manager access to their mailbox; in that case you cannot make the change in their mailbox directly. Doing so would stop them from creating their archive databases too.

To answer the above question (c>) all you have to do is, before sending your e-mail,

1. Click on the Delivery Options… button.
2. In the Delivery Options dialogue box, in the Basic tab, you will see a check box Prevent copying.
3. Select that option and press OK. Send your e-mail now.

Now the recipient would not be able to copy / forward that particular e-mail. The print option will also be disabled for the e-mail.

However, the recipient may still use print screen option or may use some script to reset the option.

Now, how do you reset the restriction / prohibition?

When you select the “Replicate or copy documents” option in the ACL, any documents created by people / groups that do not have this option checked will then be marked with a reserved field $KeepPrivate. $KeepPrivate field is set to “1” in that case; thus, prohibiting the documents from getting copied/forwarded/printed.

The same happens in case of selecting the “Prevent copying” check box in Delivery Options dialogue box.

You must have guessed by now that if you want to get rid of the prohibition, then you need to either reset the $KeepPrivate field or need to remove it completely from the document.

To reset the field here’s the code:

@If(@IsAvailable($KeepPrivate);

      @If($KeepPrivate = "1";

            @Do(

                     @SetField("$KeepPrivate";"0");

                     @Prompt([Ok];"Copy/Print/Forward Enabled";

                                    "The selected message can now be forwarded/copied!")

             );

            @Prompt([Ok];"Restriction is already disabled";

                           "The selected e-mail does not seem to have printing/forwarding/copying disabled.")

       );

      @Prompt([Ok];"No Restriction Found";

      "The selected e-mail doesn't seem to have any restriction at all.")

);

SELECT @All

To remove the field completely:

FIELD $KeepPrivate := @Unavailable;

SELECT @All

The above settings are applicable only to the Notes Client application and not to the Notes Web application.

Lotus Notes designers if you want to put this restriction through the design, then you can add a hidden field with the name $KeepPrivate and set it's Default Value as "1". Otherwise if you want some already created documents to be prohibited, then add the $KeepPrivate through an agent:

FIELD $KeepPrivate := "1";

SELECT @All

Beside this we can restrict documents created with certain forms from being printed/copied/forwarded. This we can achieve by selecting the option "Disable printing/forwarding/copying to clipboard" in Form properties > Security Tab.

In case you know any other way then do let me know.

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. 

Basics about Authors and Readers field

Authors

Authors field works in conjuction with Author access i.e. it controls only people with Author access in the ACL.

If you have only Author access you can Read, but you cannot Edit that document.

However, if you have Author access and your name is listed in the Authors field of that document, then you can edit that document.

Authors field affects only users who have Author access in the ACL. If you have Read access in ACL and your name is in Authors field, then you cannot edit the documents. Thus, Authors field cannot override the ACL.

Editor access or higher access in ACL are not affected by the Authors field.

Readers

Readers field controls who can read the documents. Readers field doesn't give you more access than what you are given in the ACL. 

If you have No Access and are listed in the Readers field then you cannot read the document.

However, if you have Editor or higher access and your name is not listed in the Readers field then even if you have Editor or higher access you are not be allowed to read the documents.

If you have Editor or higher access and you want to read the documents with Readers field in it then have your name listed in the Form Properties > Security Tab > Default read access for documents created with this form >

a. have your name added to the ACL and tick the All readers and above checkbox.

b. have your name added to the ACL and tick the names who should read documents cerated with that form.

c. have your name added to the Readers field in the form design.

It doesn't affect the read access list or the ACL. Readers field can control/restrict the access of the document before it is created. Readers field also affects the Replication of the documents.

Authors & Readers

Authors field doesn't affect Edit Access or higher. Readers field does affect Edit Access or higher.

Authors field overrides the Readers field. i.e. if your name is not there in the Readers field, but is there in the Authors field of that same document, then you will be able to read the document.

A Readers field with a blank value mean that everyone can Read the document.

However, an Authors field with a blank value doesn't allow anyone to Edit the document.

"*" value in the Readers / Authors field enables everyone to Read / Edit, respectively, the documents.

If you go to the Document properties and check the Fields Tab, you can see the Field Flags.

The Readers field shows field flags as: READ-ACCESS NAMES

The Authors field shows field flags as: READ/WRITE-ACCESS NAMES

Things need to be taken care of while creating Readers/Author field

The right way of adding values to the Readers / Authors fields are:

a. Use canonical form of the name, i.e. CN=Gayatri Gouda/OU=IND/O=CS.

b. Use roles with the square brackets, i.e. [HelpdeskAnalyst].

c. Do not use the Domain name with the canonical name i.e. no @DOMINO.

d. Avoid any extra spaces before or after or in between the names in quotes. Use @Trim.

The field type of these fields should be Editable / Computed / Computed when composed.

e. Do not make these fields as Computed for Display. Otherwise the notes document would fail to calculate your access.

f. If you want multiple users to edit or read the documents, in the field properties, you must tick the Allow multiple values in the field properties. This stores the names and the roles as a Text-list and in the form of:

"CN=Gayatri Gouda/OU=IND/O=CS":"CN=Suchi Gouda/OU=IND/O=CS":"[HelpdeskAnalyst]"

g. Use colon (:) to separate values and not semicolon (;) or any other character.

h. Enclose the values in parenthesis ("").

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.

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.

I have found one way which I have used in my earlier web-based projects. I got that from the Microsoft website. Now, of course they don't use that feature any more, I guess. However, they have left a beautiful one-liner for us.

Here's the code:

Here the startColorStr and endColorStr describe themselves what they do. You have to just choose your color code in hex decimal code.

You can check the hex codes in any tool used for photograph editing. Or you can get the code from some of the websites which generate hex code for the color you choose. One such website is: http://www.2createawebsite.com/build/hex-colors.html

Now, about the gradientType; this value can be set to "0" or "1".

1 would give you a horizontal gradient i.e. the start color would be in the left and the end color would be in the right .

0 would give you a vertical gradient i.e. the start color would be at the top and end color would be at the bottom.

You can implement this code as HTML code in your form. Or, if you have drawn a table and you want apply the gradient effect then you can use this in the table properties. All you have to do is go to the Table Properties > Table Programming tab and in the Cell HTML Tags > Style field add the following code:

filter:progid:DXImageTransform.Microsoft.Gradient(startColorStr='#FFFFFF', endColorStr='#98B2E6', gradientType='0')

Thus, when you preview your application in web the cells would show the gradient effect.

If there are any other way of using gradient effect in forms, then please do let me know.

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.

Problem Solution

Solution:

Step 1: Find out the Form name from the document properties which when opened/edited/saved/closed throws the error.

Step 2: Find out how many hidden Text fields are present in the Form/Subform that you found out in the Step 1. These hidden fields usually have some look up code which is used in the other Listbox field of the form. Some time these Text fields if are doing look up of more than it’s maximum limit i.e. 32K then it would throw the exception that field too large.

Step 3: Now, you have to find out which are the field(s) that have the look up code in their Default Value option. E.g. say field name is schMainCompList and the lookup formula used is:

LookupList:= @Unique(@Trim(@DbColumn("":"nocache"; ""; "LUCSCEntityName"; 1)));
@If(@IsError(LookupList) | LookupList = "“; "“; LookupList : schMainCompExtra)

Every time the document is opened/edited/saved/closed the field does a lookup as the form is getting refreshed; thus triggering the error.

Step 4: Change the code to the following:


@If( @IsNewDoc | @IsDocBeingEdited;
               @Do(LookupList:= @Unique(@Trim(@DbColumn;"":"nocache"; ""; "LUCSCEntityName"; 1)));
               @If(@IsError(LookupList) | LookupList = ""; ""; LookupList : schMainCompExtra));
"")

Now this would let the field to do the look up only when the edited and not when they are opened/saved/closed.

Step 5: Now, change the field type to Listbox and check the option Allow multiple values as shown below.

Step 6: Sometime, making the code change till Step 5 resolves the issue. But the issue may still re-occur after some other time. May be this time with some other form name but with the same subform, may be. Thus, to avoid this issue further, add the @DbLookup(…) code to the Formula Window of the field properties:

This completely resolves the issue of 32K exception triggered by the hidden text fields.

In short: Check if Text field > Change to Listbox > Place the @DbLookup code in Formula Window > Use proper validation.