Duplicate records

[L. Lautenbach]

First Let me say A Giant thank you to A5 and this forum.

Does anyone have a script for checking that we are not entering duplicate information to a data dase?



the data base we are working with is a one table

Would like to check for Address, phone, serial no.

Any help would sureley be appricated.

Thanks
Larry

[John Dodson]

RE: Duplicate records

Here is some code I use to check duplicates on entry (using parts of lastname, firstname, and birthdate). I believe I got this off the learn alpha website, so you might check there for more details. It is a CanSaveRecord script.

''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 = "newacct='"+ table.current().newacct + "'"
ix=t.query_create()
t.fetch_first()
recs=ix.records_get()
x=""
if recs > 0 then
show_recs = iif(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

[L. Lautenbach]

RE: Duplicate records

Thank you

That works great!!

[davej]

RE: Duplicate records

Larry,

You can also go into field rules for the table in question, select the field you are concerned about and click on the 'Validations' tab and then on the Uniqueness test, define the test that, for you, defines uniqueness.

Dave

[L. Lautenbach]

RE: Duplicate records

Thanks Dave
That was as simple as it gets!!!
Is there also a dialog popup that could be created on duplcates, that would inform user, that record exist??

Larry

[davej]

RE: Duplicate records

Larry,

You can include a message that appears if a value fails the validation text. First of all, maximize the field rules windows - that should ALWAYS be done when you are using fleld rules. Towards the bottom of the screen, you will see 'Custom validation', which is where you can define validation rule more complex than what you get with the uniqueness test. You can also define a message that appears as needed.

The reason I say that you should always maximize the field rules window is because there are many more options that are not seen if the windows is less that full screen.

Dave

[L. Lautenbach]

RE: Duplicate records

Ya Lost me !

Under Custom validation, what type of validation should I enter? The field name is Cust_num. I got a little lost at this point.

Sorry about that.
Larry

[davej]

RE: Duplicate records

Larry,

If the validation only has to do with one field, use the uniqueness test. The custom validation is for more complex circumstances and could be something like 'student is enrolled in the class and prerequisite grade is less than C'. The message could be 'Do not allow enrollment'.

Dave