Solved

Perform field validation when importing excel document into a form

Posted on 2009-07-07
10
678 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

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

On Demand Webinar: Networking for the Cloud Era

Did you know SD-WANs can improve network connectivity? Check out this webinar to learn how an SD-WAN simplified, one-click tool can help you migrate and manage data in the cloud.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This is an old article, please see an updated version of this article, located here: http://www.experts-exchange.com/articles/23619/Notes-8-5x-Windows-7-Notes-info-and-tips.html
Problem "Can you help me recover my changes?  I double-clicked the attachment, made changes, and then hit Save before closing it.  But when I try to re-open it, my changes are missing!"    Solution This solution opens the Outlook Secure Temp Fold…
In this video, viewers will be given step by step instructions on adjusting mouse, pointer and cursor visibility in Microsoft Windows 10. The video seeks to educate those who are struggling with the new Windows 10 Graphical User Interface. Change Cu…
If you’ve ever visited a web page and noticed a cool font that you really liked the look of, but couldn’t figure out which font it was so that you could use it for your own work, then this video is for you! In this Micro Tutorial, you'll learn yo…

717 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