Solved

Perform field validation when importing excel document into a form

Posted on 2009-07-07
10
668 Views
Last Modified: 2013-12-18
I am creating a database that has a form that can be used manually or filled out by importing excel documents.  I don't want duplicate records so I put in the validation found in the code section for two fields for a service desk number and change request number.  This works when manually filling out the form, but not when I import the excel documents.  I checked 'Calculate fields on form during document import', but it doesn't seem to make a difference.  
Sub Exiting(Source As Field)

	Dim ss As New notessession

	Dim ws As New notesuiworkspace

	Dim uidoc As notesuidocument

	Dim db As notesdatabase

	Dim view As NotesView          

	Dim curdoc As NotesDocument

	Dim doc As notesdocument

	Dim answer As Integer

	Dim CR As String

	

	Set db = ss.CurrentDatabase

	Set uidoc = ws.CurrentDocument

	Set curdoc = uidoc.Document

	Set view = db.GetView("DupCR")

	CR = uidoc.FieldGetText("Change_Request_No")

	Set doc = view.GetDocumentByKey(CR)

	If Not doc Is Nothing Then

		answer = Messagebox( "CR found.  Switch to existing document?", 1+32, "CR exists: " & CR)

		If (answer = 1) Then

			curDoc.SaveOptions = "0"

			Call uidoc.Close          

			Delete uidoc

			Call ws.EditDocument(True, doc)

		End If      

	End If     

End Sub

Open in new window

0
Comment
Question by:bxe9532
  • 5
  • 3
  • 2
10 Comments
 
LVL 4

Expert Comment

by:chuckalicious
ID: 24801267
When you say you import data from Excel, how do you do this? Are you using Notes own import option from the File menu, or have you written your own routine to do this?

Field validation will only occur on the form on which the field is stored, and forms are only used at open/display on the Notes client, for purposes of representing the underlying data in a given way, so any validation on a form/field will not occur in the background I'm afraid. So from what I know about the built in Notes import functions, they just create NotesDocuments and never interract with the UI.

You'd need to write your own Excel import routine (of which there are many examples on t'internet) in which you can write your own validation. I have done this many times and it works well.
0
 
LVL 22

Expert Comment

by:mbonaci
ID: 24801671
I found this in my code library:

This script agent can be used as an alternative to COL files to parse and build Notes documents. This is especially appropriate when source data includes MEMO fields containing TABs and CR/LF characters. This agent contains a routine for exception processing. As with any load, the process should be tested and with a small, representative subset of source data until the kinks are worked out.

Agent Information:
      Name: Load Delimited Text File
      Type: LotusScript
      Trigger: Manually From Agent List
      Acts On: Run once (@Commands may be used)
Option Public

Option Explicit

Option Base 1
 

Const datetimeField = "DATETIME"

Const richtextField = "RICHTEXT"

Const numberField = "INTEGER"

Const phonemask = "PhoneFormat"

Const ssnmask = "SsnFormat"

Const zipmask = "ZipFormat"

Dim db As NotesDatabase

Dim doc As NotesDocument, errordoc As NotesDocument 

Dim item As NotesItem

Dim rtItem As NotesRichTextItem 

Dim positionStart As Integer, positionEnd As Integer, docLength As Integer, fieldnum As Integer, fieldcount As Integer 

Dim fileNum As Long, counter As Long 

Dim fulltext As Variant 

Dim inputfile As String, formname As String, delimeter As String, recordIndicator As String, fieldname As String, editmask As String, textString As String 

Dim fieldlist List As String
 

Sub Initialize

	Dim session As New NotesSession

	Set db = session.CurrentDatabase

	inputfile = |DriveLetter:DirectorySubDirectoryTestData.txt|    'insert the drive,path, and name of the file to be read for import

	REM  If source data is generated from Excel, Access or FoxPro, you can export with a custom field delimiter.

	REM  If possible don't use TAB as field delimiter because source data may contain embedded TABs otherwise horizontal tab = Chr(9).

	delimeter = "~"    'Privide the field delimeter used in the input file

	REM  If source data is generated from Excel, Access or FoxPro, you can insert append a column/field with a 'known' string to delineate documents.

	REM  You can initialize recordIndicator with this string to delineate documents instead of using CR/LF.

	REM  If this was not done leave 'recordIndicator' commented to disable.

	REM  Common record delimiters Carriage Return/LineFeed; Chr(13) & Chr(10), Custom; "~`^"

	'recordIndicator = "~`^"		    	'provide string that indicates a new record

	formname = "FormName"   			'provide the name of the form for the document to be created in your NOTES database

	Call DefineFields    			'Define input fields in this subroutine

	fileNum = Freefile()   			'initialize file number

	Open inputfile For Input As fileNum

	Do While Not Eof(fileNum)

		Line Input #fileNum, fulltext	'Read each line of the file.

		docLength = Len(fulltext)

		' If Left(fulltext, Len(recordIndicator)) = recordIndicator Then

		If fieldcount = 0 Then

			Call CreateNotesDoc   	'indicates a new record

		Elseif fieldname <> "" And Not (doc Is Nothing) Then

			Call AppendToNotesDoc   	'probably a partial record due to embedded cr/lf

		Else

			Call WriteErrorDoc

		End If

		If fieldcount = fieldnum  Then fieldcount=0

		If Not(session.IsOnServer) Then Print counter

	Loop

	Close fileNum

End Sub
 

Sub CreateNotesDoc

	Set doc = db.CreateDocument

	doc.Form = formname

	positionStart = 1

	Forall f In fieldlist

		If positionStart>docLength Then Goto ProcessDocument    'no more fields to process, hit end of record

		fieldname = Listtag (f)

		If fieldname = "" Then Goto ProcessDocument

		positionEnd = Instr(positionStart, fulltext, delimeter, 5)

		If positionEnd=0  Then positionEnd =docLength+1

		Call BuildField

		positionStart = positionEnd+1

	End Forall

ProcessDocument:

	Call doc.ComputeWithForm( False, False )   'this is optional if form does not contain other fields that need to be computed for a new document  

	Call doc.Save( True, False )

	counter = counter + 1

End Sub
 

Sub BuildField

	textString = Trim(Mid$(fulltext,positionStart,(positionEnd-positionStart)))

	editmask = fieldlist(fieldname)

	Select Case editmask

	Case "" : Set item = New NotesItem(doc, fieldname, textString)

	Case "RICHTEXT"

		Set rtitem = New NotesRichTextItem (doc, fieldname)

		Call rtitem.AppendText(textString)

	Case "DATETIME"

		If textString = "" Then

			Set item = New NotesItem(doc, fieldname, Nothing)

		Else

			If Isdate(textString) Then

				Set item = New NotesItem(doc, fieldname, Cdat(Format$(textString, "mm/dd/yyyy hh:mm am/pm")))

			Else

				Set item = New NotesItem(doc, fieldname, textString)

			End If

		End If

	Case "INTEGER"

		If textString = "" Then

			Set item = New NotesItem(doc, fieldname, Nothing)

		Else

			If Isnumeric(textString) Then

				Set item = New NotesItem(doc, fieldname, Cint(textString))

			Else

				Set item = New NotesItem(doc, fieldname, textString)

			End If

		End If

	Case "PhoneFormat"

		If Isnumeric(textString) Then

			Select Case Len(textString)

			Case 7 : Set item = New NotesItem(doc, fieldname, Format$(textString, "000-0000"))

			Case 10 : Set item = New NotesItem(doc, fieldname, Format$(textString, "(000) 000-0000"))

			Case Else : Set item = New NotesItem(doc, fieldname, textString)

			End Select

		Else

			Set item = New NotesItem(doc, fieldname, textString)

		End If

	Case "SsnFormat"

		If Isnumeric(textString) And Len(textString) = 9 Then

			Set item = New NotesItem(doc, fieldname, Format$(textString, "000-00-000"))

		Else

			Set item = New NotesItem(doc, fieldname, textString)

		End If

	Case "ZipFormat"

		If Isnumeric(textString) And Len(textString) = 9 Then

			Set item = New NotesItem(doc, fieldname, Format$(textString, "00000-0000"))

		Else

			Set item = New NotesItem(doc, fieldname, textString)

		End If

	Case Else : Set item = New NotesItem(doc, fieldname, textString)

	End Select

	fieldcount=fieldcount+1

End Sub
 

Sub WriteErrorDoc

	REM  This routine creates documents from strings that cannot be processed in normal processing.

	REM  Add a temporary form to your destination database named "ErrorDoc" with 1 field named "Body"

	REM  Add a temporary view to your destination database named "LoadErrors" with a selection formula for Form="ErrorDoc"

	REM  If the view contains any documents, then resolve them manually 

	REM  When done, delete all documents of form name "ErrorDoc", the temporary form, and the temporary view

	Dim success As Variant

	Set errordoc = db.CreateDocument

	errordoc.Form = "ErrorDoc"

	Set rtitem = New NotesRichTextItem (errordoc, "Body" )

	Call rtitem.AppendText("INPUT FAILURE:  ")

	Call rtitem.Addnewline(1)

	Call rtitem.AppendText(fulltext)

	Call rtitem.Addnewline(2)

	If Not(doc Is Nothing) Then

		success = doc.RenderToRTItem( rtitem )

	End If 

	Call errordoc.Save( True, False )

End Sub
 

Sub DefineFields

	REM Field names must be listed in the order they appear in the input file. All fields must be accounted for.

	REM If a field is to be formated, the format code must be inserted as the value of the listtag

	REM Do not use format codes unless data content is known and consistent.  Leave null to create a generic text field

	REM syntax fieldlist("fieldname")={format code}

	REM example using defined constant   fieldlist("Comment_Field")="RICHTEXT"   will create a richtext field

	REM example using defined constant   fieldlist("Date_Field")="DATETIME"   will create a datetime field

	REM example using defined constant   fieldlist("Number_Field")="INTEGER"   will create a INTEGER numeric field

	REM example using defined constant   fieldlist("SSN")=ssnmask  will create a text item formatted as "000-00-0000"

	fieldnum = 0

	fieldlist("RecordType")=""

	fieldlist("SeqNum")=""

	fieldlist("DtCreated")="DATE"

	fieldlist("Name")=""

	fieldlist("Address")=""

	fieldlist("City")=""

	fieldlist("State")=""

	fieldlist("ZipCode")=""

	fieldlist("HomePhone")="PhoneFormat"

	fieldlist("WorkPhone")="PhoneFormat"

	fieldlist("Fax")="PhoneFormat"

	fieldlist("DtLetterSent")="DATETIME"

	fieldlist("DtClosed")="DATETIME"

	fieldlist("ORIGIN_CODE")=""

	fieldlist("Body")="RICHTEXT"

	Forall f In fieldlist

		fieldnum = fieldnum + 1

	End Forall

End Sub
 

Sub AppendToNotesDoc

	Dim tempItem As NotesItem

	Dim processField As String    'used to position field table for processing

	positionStart = 1

	Forall f In fieldlist

		If fieldname = Listtag (f) Then processField = "Append"

		If processField <> "" Then

			If positionStart > docLength Then Goto ProcessDocument    'no more fields to process, probably hit CR/LF

			positionEnd = Instr(positionStart, fulltext, delimeter, 5)

			If positionEnd = 0 Then positionEnd = docLength + 1      'CR/LF

			Select Case processField

			Case "Add"

				fieldname = Listtag (f)

				Call doc.Removeitem(fieldname)

				Call BuildField

			Case "Append"

				textString = Trim(Mid$(fulltext,positionStart,(positionEnd-positionStart)))

				Select Case editmask

				Case "RICHTEXT"

					Set rtitem = doc.GetFirstItem(fieldname)

					Call rtitem.AddNewLine( 1 )

					If textString<>"" Then  Call rtitem.AppendText(textString)

				Case "DATETIME" : Call WriteErrorDoc

				Case "INTEGER" : Call WriteErrorDoc

				Case Else

					Set tempItem = doc.GetFirstItem(fieldname)

					Set item = doc.ReplaceItemValue(fieldname, tempItem.Text  & Chr(10) & textString)

				End Select

				processField = "Add"

			End Select

			positionStart = positionEnd + 1

		End If     'else loop for next field until last field written is found

	End Forall

ProcessDocument:

	Call doc.ComputeWithForm( False, False )

	Call doc.Save( True, False )

	counter = counter + 1

End Sub

Open in new window

0
 
LVL 4

Expert Comment

by:chuckalicious
ID: 24801690
Never seen the "ComputeWithForm" method before. Would make sense to use that instead of my suggestion.
0
 
LVL 22

Expert Comment

by:mbonaci
ID: 24801765
Although ComputeWithForm executes the default value, translation, and validation formulas, you'll have to write your own validation function to check whether you already have that document in your db.
First you have to decide which field is the primary key (unique).
Then create a view that displays the docs and in the first columns shows that field (column must be Sorted).

Then use this function:
Function IsUnique( strDocMbr As String ) As Boolean

	'Mb¤, 15.12.2004

	'Function returns True if strDocMbr is unique (doesn't exist in db as primary key)

	Dim s As New NotesSession

	Dim db As NotesDatabase

	Dim v As NotesView

	Dim doc As NotesDocument

	

	Set db = s.CurrentDatabase

	Set v = db.GetView( "NameOfYourView" )

	If v Is Nothing Then Exit Function

	

	Set doc = v.GetDocumentByKey( strDocMbr, True )

	

	If doc Is Nothing Then IsUnique = True
 

End Function

Open in new window

0
 

Author Comment

by:bxe9532
ID: 24808863
Thank you both for your responses.  I am using Notes Import from the File menu at this time.  There are two unique keys, but no primary keys at this point because either can be null.  By this I mean I'm creating a database for our network support group that pulls records for network incidents from our CA Unicenter Service Desk (unique key Service_Desk_No) and projects from our Clearquest database (unique key Change_Request_No).

If, for instance, a firewall port needs to be opened then a change request is created as well as a service desk change order.  If a cable is damaged on the firewall then a service desk incident is opened.  If the firewall blows up then an incident is created and an ad hoc change request is created after the blown up firewall is replaced.  What I'm driving at is the record will have one or the other or both.

I've never used domino designer before, where would I put the validation function described?  Could I run two?  I do have two separate views with those fields sorted for my manual entry validation to work(DupCR and DupSD).      
0
What Security Threats Are You Missing?

Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

 
LVL 22

Expert Comment

by:mbonaci
ID: 24812639
I would create two separate forms, one for Service desk, other for Change request.
For the purpose of checking whether the doc is unique, create two views (one for each form) with these selection formulas:

SELECT Form = "<ServiceDeskFormName>"
SELECT Form = "<ChangeRequestFormName>"

And place the Service_Desk_No and Change_Request_No in first column (sorted).

You should incorporate the function IsUnique into new import routine (I found more simple one for you).
So, open your destination db in Designer, create new Agent, paste the code (from this post and IsUnique function) into Options section.
I suggest you to duplicate the function by creating IsServiceDeskUnique and IsChangeRequestUnique (just change the function name and replace "NameOfYourView" with the actual view names.

Set agent properties:
run: On event - Action menu selection
target: None

You'll have to modify a few things in the code:
 - Constants (xls filename, max rows, ...)
 - criteria to define which type of doc it is (serviceD or changeR) - see the comment in the code
 - doc.Form = here put the name of your form
 - assign lotus document field names in lines following comment: 'Set Field values
 - assign view names to IsSDUnique and IsCRUnique


Start slowly and we'll help you if you get stuck...
Option Public

Option Declare
 

'Important: set these values to your environment...

Const xlFilename= "<full path to Excel file>" 	'IMPORT FILENAME

Const MAX_RECORDS = 10		'Set this to the maximum number of records you want to loop

Const MAX_SKIPROWS = 10		'Set this to the number of blank rows you want to skip before you assume

				'the rest of the worksheet is blank
 
 

Sub Initialize

	On Error Goto Handle_Error

	Dim s As New NotesSession

	Dim db As NotesDatabase

	Dim doc As NotesDocument

	Dim row As Integer, written As Integer, skiprow As Integer

	Dim chk As String

	

	Dim Excel As Variant

	Dim xlWorkbook As Variant

	Dim xlSheet As Variant

	

	Set db = s.currentdatabase

	

	'First Connect to Excel and see if there are any records to import

	Print "Connecting to Excel..."

	Set Excel = CreateObject( "Excel.Application" )

	If Excel Is Nothing Then Print "Unable to open the excel object..." : Exit Sub

	

	Excel.Visible = False 			'// Don't display the Excel window

	Excel.Workbooks.Open xlFilename 	'// Open the Excel file

	Print "Opening " & xlFilename & "..."

	Excel.Workbooks.add

	Excel.Workbooks(1).Worksheets(1).Activate

	Set xlWorkbook = Excel.Workbooks(1)

	Set xlSheet = Excel.Workbooks(1).Worksheets(1)

	

	row = 1

	written = 0

	Print "Starting import from Excel file..."

	chk="xxxx"

	

	Do While( row <= MAX_RECORDS )

		With xlSheet

			'check whether the row is empty

			chk = Trim( Format( .cells( row, 1 ).value ) + Format( .cells( row, 2 ).value ) + Format( .cells( row, 3 ).value ) + Format( .cells( row, 4 ).value ) )

			If Len( chk ) < 0 Then

				skiprow = skiprow +1

				Goto NextRow

			End If

			

			If row <> 1 Then			'Skip header row

				If Len(chk) > 0 Then

					Set doc = New NotesDocument(db)

					If .Cells( row, 1 ).Value = "ServiceDesk" Then 'criteria to define which type of doc it is

						doc.Form = "<ServiceDeskFormName>" 'Form name

					

						'Set Field values

						doc.employee_number = .Cells( row, 1 ).Value

						doc.employee_lastname = .Cells( row, 2 ).Value

						doc.employee_firstname = .Cells( row, 3 ).Value

						doc.employee_middlename = .Cells( row, 4 ).Value

					Else
 

						doc.Form = "<ChangeRequestFormName>" 'Form name

					

						'Set Field values

						doc.employee_number = .Cells( row, 1 ).Value

						doc.employee_lastname = .Cells( row, 2 ).Value

						doc.employee_firstname = .Cells( row, 3 ).Value

						doc.employee_middlename = .Cells( row, 4 ).Value

					End If

					

					Call doc.Save( True, False, True )

					Print " Creating Record..." + Cstr( row )

					written = written + 1

					Set doc = Nothing

					skiprow = 0					'If we find a record, then set the skiprow back to 0
 

				End If

				

			End If

		End With

NextRow:

		row= row+1

		If skiprow >= MAX_SKIPROWS Then

			Print Cstr( MAX_SKIPROWS ) + " blank rows reached... assuming the rest of the worksheet is blank..."

			Exit Do

		End If

	Loop

	

	Print "Disconnecting from Excel..."

	Excel.activeworkbook.close

	Excel.Quit '// Close Excel

	Set Excel = Nothing '// Free the memory that we'd used

	Exit Sub

	

Handle_Error:

	If Err = 213 Then

		Print "Import File not found..."

		Err = 0

		Exit Sub

	End If

	On Error Goto 0

	If Not xlworkbook Is Nothing Then

		Excel.activeworkbook.close  '// Close the Excel file without saving (we made no changes)

		Excel.Quit '// Close Excel

		Set Excel = Nothing '// Free the memory that we'd used

	End If

	Exit Sub

End Sub

Open in new window

0
 

Author Comment

by:bxe9532
ID: 24819457
That's great, it imports the records fine but I'm still trying to check for duplicates.  When I entered the IsUnique Function in the agent it breaks into a separate section under Terminate.  Is there a way to call the IsUnique Function from the loop when it is reading records?  I'm still not sure how the IsUnique function works.  
0
 
LVL 22

Expert Comment

by:mbonaci
ID: 24823819
I thought you're going to abandon the whole thing, you got pretty far for someone who has never used Domino Designer.

Exactly, you have to call the function from the loop, like this:
If .Cells( row, 1 ).Value = "ServiceDesk" Then 'criteria to define which type of doc it is

	If IsServiceDeskUnique( .Cells( row, 1 ).Value )

		doc.Form = "<ServiceDeskFormName>" 'Form name

	

		'Set Field values

		doc.employee_number = .Cells( row, 1 ).Value

		doc.employee_lastname = .Cells( row, 2 ).Value

		doc.employee_firstname = .Cells( row, 3 ).Value

		doc.employee_middlename = .Cells( row, 4 ).Value

	End if
 

Else
 

	If IsChangeRequestUnique( .Cells( row, 1 ).Value )

		doc.Form = "<ChangeRequestFormName>" 'Form name

					

		'Set Field values

		doc.employee_number = .Cells( row, 1 ).Value

		doc.employee_lastname = .Cells( row, 2 ).Value

		doc.employee_firstname = .Cells( row, 3 ).Value

		doc.employee_middlename = .Cells( row, 4 ).Value

	End if

End If

Open in new window

0
 
LVL 22

Accepted Solution

by:
mbonaci earned 500 total points
ID: 24823921
One more thing,
with this modification you'll have to modify this piece of code:

Call doc.Save( True, False, True )
Print " Creating Record..." + Cstr( row )
written = written + 1
Set doc = Nothing
skiprow = 0                              'If we find a record, then set the skiprow back to 0


First three lines copy just above End if of each isUnique function and leave two remaining rows intact, like this:
If .Cells( row, 1 ).Value = "ServiceDesk" Then 'criteria to define which type of doc it is

	If IsServiceDeskUnique( .Cells( row, 1 ).Value )

		doc.Form = "<ServiceDeskFormName>" 'Form name

	

		'Set Field values

		doc.employee_number = .Cells( row, 1 ).Value

		doc.employee_lastname = .Cells( row, 2 ).Value

		doc.employee_firstname = .Cells( row, 3 ).Value

		doc.employee_middlename = .Cells( row, 4 ).Value
 

		Call doc.Save( True, False, True )

		Print " Creating Record..." + Cstr( row )

		written = written + 1

	End if

 

Else 

	If IsChangeRequestUnique( .Cells( row, 1 ).Value )

		doc.Form = "<ChangeRequestFormName>" 'Form name

					

		'Set Field values

		doc.employee_number = .Cells( row, 1 ).Value

		doc.employee_lastname = .Cells( row, 2 ).Value

		doc.employee_firstname = .Cells( row, 3 ).Value

		doc.employee_middlename = .Cells( row, 4 ).Value
 

		Call doc.Save( True, False, True )

		Print " Creating Record..." + Cstr( row )

		written = written + 1

	End if

End If
 

Set doc = Nothing

skiprow = 0					'If we find a record, then set the skiprow back to 0

Open in new window

0
 

Author Closing Comment

by:bxe9532
ID: 31600947
Fantastic!  You thought I was going to abandon it and I thought you might too for asking you to write my code for me.  Thanks for all the help and the followup.
0

Featured Post

6 Surprising Benefits of Threat Intelligence

All sorts of threat intelligence is available on the web. Intelligence you can learn from, and use to anticipate and prepare for future attacks.

Join & Write a Comment

  In today’s Arena we can’t imagine our lives without Internet as we are highly used to of it. If we consider our life style just for only 2 min we found that face to face communication is swapped by e-communication.  Every Where from Works place to…
Notes Document Link used by IBM Notes is a link file which aids in the sharing of links to documents in email and webpages. The posts describe the importance and steps to create a Lotus Notes NDL file in brief.
Excel styles will make formatting consistent and let you apply and change formatting faster. In this tutorial, you'll learn how to use Excel's built-in styles, how to modify styles, and how to create your own. You'll also learn how to use your custo…
This video shows how to remove a single email address from the Outlook 2010 Auto Suggestion memory. NOTE: For Outlook 2016 and 2013 perform the exact same steps. Open a new email: Click the New email button in Outlook. Start typing the address: …

746 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

13 Experts available now in Live!

Get 1:1 Help Now