Check for Duplicate Table Entries

by Bill Parker

Bill Parker, the author of the A5Doc documenter program, never does anything by halves. He is the consummate writer of library functions for Alpha Five. If you can think of a place for a general purpose function, the chances are that Bill has already written it. Here is his function to test for duplicate entries in a table. The function can compare up to 20 fields in a record to decide if a new entry is a duplicate of an old entry.

Bill provides the following comments for anyone using this script:

IF DupCheck("Last_Name, First_Name","", "",0)
  cancel()
END IF

FUNCTION DupCheck as L(vDupFields as C,vField as C,vDisplayFields as C,vMaxRecs as N)
'Duplicate check for multiple fields, with option to accept duplicate. 
'(if 1 field, use standard rule)
'RETURN VALUE, .t.- reject current duplicate, .f.- no duplicate, or accept it.
'in the calling script, say - IF dupcheck(...) THEN cancel() END IF
'Note: currently, duplicates can only be checked on character fields.  limit of 20 fields.
'if check is on first name + last name, call function from CanWriteField rule for 2nd in tab order.
'weakness - if user later changes 1st in tab order to create a duplicate, it is not caught.
'partial solution - also call function in CanSave record event.
'Note that if a duplicate is allowed, the warning will display both when leaving the field, and when saving record.
'function can be called from field rule or form, but not from a global script since it needs a table context.
'** sample - called from form's CanDepart event
'IF DupCheck("First_Name, Last_Name","","",0)
'  cancel()
'END IF
'** sample - called from CanWriteField event on Last_Name rule
'this is the only event to use the second parameter.  otherwise, it should be ""
'IF DupCheck("First_Name, Last_Name","Last_Name","",0)
' cancel()
'END IF
'** sample - called from CanSaveRecord field rule event.  includes optional display list, and # recs to show.
'IF a_deleting_record
'  end
'END IF
'IF DupCheck("First_Name, Last_Name","", "Last_Name, First_Name, Company", 12)
'  cancel()
'END IF
'W Parker 8/26/99
'	parameters passed to function
'DIM	vDupFields as C   ' list of field names separated by commas, combination must be unique
'DIM	vField as C    	' if script is on CanWriteField event, put name of field here, otherwise, "".
'DIM	vDisplayFields as C	' display list - field names separated by commas.  default=vDupFields
'DIM	vMaxRecs as N     ' # of records to show in duplicate list, default=5, max=20
DIM	vString as C   	' modified version of vDupFields.  this is parsed to create query filter
DIM	vFilter as C   	' filter constructed from vDupFields
DIM	vRecs as N     	' number of duplicate records found
DIM	vName as C     	' the curremtly parsed field name
DIM	vNameArray[20] as C	' fields to display. used when building the display string for duplicates.
DIM	n as N         	' counter
DIM	m as N         	' counter
DIM	t2 as P        	' 2nd instance of current table, used to query. don't use t (in data entry mode.)
CONSTANT CRLF = chr(13) +chr(10)
t = table.current()
'	***** Check context *****
'	-------------------------
'	CansaveRecord is called when record is deleted.  
'	don't want to DupCheck when deleting record.
'	in v4.03, checking for context of CanSaveRecord event 
'	can't be generalized inside the function.
'	so if DupCheck is called from CanSave event, add 
'	following 4 lines before calling the function:
'when record is deleted, CanSave event is called.  don't dupcheck.
'IF A_DELETING_RECORD	
'	END
'END IF
'	in case function is called from form event.
IF t.mode_get()=0		
' not in data entry mode - needed for use on form object.
	DupCheck = .f.		
	' so as not to trigger additional cancel() in calling routine
	END
END IF
'	***** Setup *****
'	-----------------
't2= table.open(t.name_get())	' error if there is blank in table name, because it is replaced with _
t2= table.open(t.filename_get())	' fully qualified path.  this retains any blank
IF vDisplayFields=""
	vDisplayFields = vDupFields
END IF
IF vMaxRecs = 0
	vMaxRecs = 5
END IF
IF vMaxRecs > 20
	vMaxRecs = 20
END IF
DupCheck = .f.		
' default assumes there is no duplicate, or it is accepted.
'	***** Generate filter *****
'	---------------------------
'	produce something like "First_Name='" +t.First_Name +"' .AND. Last_Name='" +a_field_value +"'"
'	which translates to "First_Name='abc'.AND.Last_Name='def'"
'	make sure delimeter is at beginning and end of string
vString = ALLTRIM(vDupFields)
IF SUBSTR(vString,1,1)<>","
	vString = "," +vString
END IF
IF SUBSTR(vString,LEN(vString),1)<>","
	vString = vString +","
END IF
'	loop through string of field names to create filter
vFilter = ""
FOR n=1 to 20
	vName = ALLTRIM(piece(vString, n, ","))
	IF vName=""
		n = 20
	ELSE
		IF n>1
			vFilter = vFilter +".AND."
		END IF
		IF vName=vField
			vFilter = vFilter +vName +"='" +a_field_value +"'"
		ELSE
			vFilter = vFilter +vName +"='" +EVAL("t." +vName) +"'"
		END IF
	END IF
NEXT n
'	***** Query for duplicates *****
'	--------------------------------
query.filter = vFilter
i = t2.query_create()
vRecs = i.records_get()
'	***** Display duplicates *****
'	------------------------------------
'	use ui_msg_box, because displaying form in Can event causes problems.
IF vRecs>0
'		***** make sure delimeter is at beginning and end of string *****
	vString = ALLTRIM(vDisplayFields)
	IF SUBSTR(vString,1,1)<>","; vString = "," +vString; END IF
	IF SUBSTR(vString,LEN(vString),1)<>","; vString = vString +","; END IF
'		***** loop through string of field names to create array *****
	FOR n=1 to 99
		vName = ALLTRIM(piece(vString, n, ","))
		IF vName=""
			vMaxFields = n -1
			n = 99
		ELSE
			vNameArray[n] = vName
		END IF
	NEXT n
'		***** create display string for msg_box by reusing vString *****
'		with the enhanced msg_box coming in v5, fields can be aligned
'		first get field names
	vString = ""
	FOR n=1 to vMaxFields
		vString = vString +vNameArray[n] +",  "
	NEXT n
	vString = SUBSTR(vString, 1, LEN(vString)-3) +CRLF	' remove last dash, and new line
'		now get field values
	vMaxRecs = MIN(vRecs, vMaxRecs)
	t2.fetch_first()
	FOR n=1 to vMaxRecs
		FOR m=1 to vMaxFields
			vString = vString +TRIM(EVAL("t2."+vNameArray[m])) +",  "
		NEXT m
		vString = SUBSTR(vString, 1, LEN(vString)-3) +CRLF	' remove last dash, and new line
		t2.fetch_next()
	NEXT n
'		***** ask if user wants to keep duplicate	 *****
	vReply = ui_msg_box("Warning - " +LTRIM(STR(vRecs)) +" duplicate record(s).  " +LTRIM(STR(vMaxRecs)) +" displayed.", vString +CRLF +"Accept duplicate?", 307)	' 48+3+256
	IF vReply<>UI_YES_SELECTED		' do this in case form is not closed w/ button.
		DupCheck = .t.		'cancel()	' don't move out of last name field.
	END IF
END IF
'	***** Wrapup *****
'	------------------
i.drop()		' remove query
t2.close()
END FUNCTION
'	*****************************************************************************************
FUNCTION piece as C(vString as C,n as N,vDelim as C)
'	Return a value from vString, based on the Nth occurance of vDelim
'	if string is " a=~1~ b=~2~ c=~3~ ", specify n=5 and vDelim="~" to return "3"
'	if string is " a=~1~ b=~2~ c=~3~ ", specify n=3 and vDelim=" " to return "c=~3~"
'	don't use this with records such as field rules. that format requires different parsing.
'	based on a script by Peter Wayne
DIM	vBeg as N		' beginning position
DIM	vEnd as N		' ending position
vBeg = ATC(vDelim, vString, n)
vEnd = ATC(vDelim, vString, n+1)
piece = SUBSTR(vString, vBeg+1, vEnd-vBeg-1)
END FUNCTION

Bill has thoughtfully laced his script liberally with comments. The downside of his thoughtfulness os that it's hard to read this script on the Web! You can download this script as a self-extracting zip file and study it off-line.

9/4/99

Don't forget, we need your feedback to make this site better!

Return to home