by Dr. Peter Wayne
| Sometimes you want to allow your user to make duplicate entries, but you have to warn the user of all possible duplicates. |
Managing duplicate entries is every database administrator's nightmare. Gather round for a true story from my real profession as a gastroenterologist. I have a patient - call him George K. - whom I have been treating for hepatitis C. As part of my management I have been checking hepatitis C viral RNA levels in his blood. The laboratory his insurance company uses - a nationwide laboratory with generally fine credentials - sent me a graphical report of his hepatitis C viral titers in a prettily designed report called the "Serial Monitoring Report." Looking closely at the report, I noticed that there were 2 trend lines. One line listed his hepatitis C viral measurements (which, I am happy to report, have been at undetectable levels for the past 3 months while being treated.) The second trend line showed an increasing level of HIV viral RNA.
| Read on for the resolution of this true to life cliff-hanger! |
Now this was a major problem. First, I did not think George had HIV. As far as I knew, he was HIV negative. In addition, I had counseled him and his wife on hepatitis C but had not discussed HIV with them. If he were HIV positive, did I have an obligation to let his wife know? Even though she is not my patient, in a legal sense I had entered into a doctor-patient relationship with her by discussing her husband's hepatitis C with her and its implications for her. Therefore I was legally and in some cases ethically bound to let her know that George was HIV positive. But I had another moral dilemma - here I was privy to information that George had not wanted to reveal to me. Should I call him up? Ask him to come to the office? I was also very upset with the laboratory. They had no right to just casually send me the results of George's HIV testing, without his explicit permission.
| Relax - this story has a happy ending! |
Could it be an error? George's last name was distinctly unusual - in fact I've never met anyone else with the same last name. I decided to call the laboratory, and eventually found my way to the woman in charge of the programming for the Serial Monitoring Report. She quickly determined that there were 2 patients with the same name, one with HIV and one with hepatitis C, being treated by 2 different doctors. Which meant that my George had the fastest cure of HIV on record.
| The best stories have morals that you can apply to Alpha Five. |
The moral of the story: Sometimes you have to allow duplicates in your database. The error in the company's graphics program was the assumption that it was unlikely that 2 people with the same first and last name would have serial monitoring performed at the same lab. That kind of assumption falls apart as the database grows.
Alpha Five makes it relatively easy to prevent duplicate entries: you can either use the Uniqueness test in the Validations tab in a table's Field Rules, or you can use the key_exist() function to write your own uniqueness rule. But what if you want to warn the user of duplicate entries but still allow the user to override your warning? After all, what the laboratory manager needs is a way to allow 2 patients with the same name to be entered into the serial reporting database.
As is often the case with Xbasic programming, the first step is deciding the event for which to write a script. Remember the basic rule of scripts: There are Can events which are permission-type events, and On events which are fait accompli events. I always thought that the Can events should be named Mother,MayI events, but these distinctions were lost on Alpha Five's programming team...
Returning from our digression, we want to interrupt the user before the duplicate record has been saved. That's a job for a Can event! The 2 useful events here are:
The choice of which event to use is a matter of preference. The CanSaveRecord event has the disadvantage that the entire record is completed before the user is informed of a possible duplicate. The CanWriteField event has the disadvantage that, if there is a complex multifield duplicate rule that needs enforcing, then you will have to write code for each field that is involved in possible duplication, in case the user makes a series of entries or changes that is out of the usual field order.
In any event (no pun intended), let's just choose one of these handy events, the CanWriteField event for the lastname field of our table. For simplicity, we'll just warn the user when the last name matches a pre-existing last name.
The logic of our CanWriteField event handler will be as follows:
I owe this script to Selwyn Rabins, president of Alpha Software, so naturally this script uses the sample Invoice.adb application provided with Alpha Five. We'll write a duplicate detection script for the CanWriteField event of the lastname field of customer.dbf. From the Control Panel, right-click on customer, choose Edit field rules, then choose the Events tab for the lastname field:
Figure 1. The script editor for the CanWriteField event of lastname.
Here is the script that checks the lastname field:
''XBasic
dim t as p
CONST crlf = chr(13) + chr(10) ' carriage return/linefeed
dim ix as p
dim recs as n
dim x as c
dim allow as l
dim ans as n
dim cnt as n
t=table.open(table.current().filename_get())
query.filter = "lastname = var->a_field_value"
query.order=""
query.options=""
ix=t.query_create()
t.fetch_first()
recs=ix.records_get()
x=""
if recs > 0 then
show_recs = iif(recs < 11,recs,10) 'only show 10 duplicates
x = ""
crlf = chr(13)+chr(10)
for cnt = 1 to show_recs
x = x+alltrim(str(cnt))+". "+\
"Record "+alltrim(str(t.recno()))+": "+alltrim(t.lastname)+", "+\
alltrim(t.firstname)+" - "+alltrim(t.bill_address_1)+", "+\
alltrim(t.bill_city)+" "+alltrim(t.bill_state_region)+" "+\
alltrim(t.bill_postal_code)+crlf
t.fetch_next()
next cnt
end if
ix=t.index_primary_put("")
ix.drop()
t.close()
if recs > 0 then
ans= ui_msg_box("Warning! "+alltrim(str(recs))+" duplicates found",\
x + crlf + "Allow record ?",\
UI_ATTENTION_SYMBOL + UI_YES_NO + UI_SECOND_BUTTON_DEFAULT)
if ans = UI_NO_SELECTED then
cancel()
end if
end if
end
Script 1. The CanWriteField script for lastname.
There's a few points about Selwyn's script that bear comment:
Here's what we see if we try to enter a series of customers whose last name is "Graham":
Figure 2. Warning message about duplicate records.
At this point, choosing Yes will allow the lastname of the potential duplicate customer to be saved, while choosing No will keep us from leaving the field.
In real life we are probably more concerned about duplicates who match on several fields, such as first name, last name, and address or birthdate. The simplest place to put code to check on multiple fields in a record is in the CanSaveRecord event. Let's modify Selwyn's code to check both the first and last names in the CanSaveRecord event:
''XBasic
if a_deleting_record then ' it's a deletion, don't check duplicates
end
end if
dim t as p
CONST crlf = chr(13) + chr(10) ' carriage return/linefeed
dim ix as p
dim recs as n
dim x as c
dim allow as l
dim ans as n
dim cnt as n
t=table.open(table.current().filename_get())
idx=t.index_primary_get()
query.filter = "lastname='"+ table.current().lastname+\
"' .and. firstname = '"+ table.current().firstname + "'"
ix=t.query_create()
t.fetch_first()
recs=ix.records_get()
x=""
if recs > 0 then
show_recs = iif(recs < 11,recs,10) 'only show 10 duplicates
x = ""
crlf = chr(13)+chr(10)
for cnt = 1 to show_recs
x = x+alltrim(str(cnt))+". "+\
"Record "+alltrim(str(t.recno()))+": "+alltrim(t.lastname)+", "+\
alltrim(t.firstname)+" - "+alltrim(t.bill_address_1)+", "+\
alltrim(t.bill_city)+" "+alltrim(t.bill_state_region)+" "+\
alltrim(t.bill_postal_code)+crlf
t.fetch_next()
next cnt
end if
t.index_primary_put(idx)
ix.drop()
t.close()
if recs > 0 then
ans= ui_msg_box("Warning! "+alltrim(str(recs))+" duplicates found",\
x + crlf + "Allow record ?",\
UI_ATTENTION_SYMBOL + UI_YES_NO + UI_SECOND_BUTTON_DEFAULT)
if ans = UI_NO_SELECTED then
cancel()
end if
end if
end
Script 2. The CanSaveRecord script to check 2 fields.
The main differences between Script 2 and Script 1 are:
Remember, you could extend this principle to check any number of fields in your table. However, as you add records the query will run more and more slowly unless you set up an index that the query can use for lightning query optimization. In this case, there already is an index on lastname+firstname, so our queries should be optimizable!
8/29/99
Don't forget, we need your feedback to make this site better!