Check for duplicate record?

[Keith Hubert]

Hi All,

I have done a search here and found only 4 threads which unfortunately have not answered the following question.

Using a Dialog to enter their name and address, how do I prevent and also advise the user that they have already registered?

I assume this would be in BeforeValidate?

[Steve Workings]

Re: Check for duplicate record?

I'd point you towards the Validate event Keith.

Something like this:

Currentform.has_error = .f.
Currentform.Error_Message = ""

' validation checking code here - whatever you need

if some_condition_here then
   Currentform.has_error = .t.
   Currentform.Error_Message = "Our records show you have already registered." 
end if

[Keith Hubert]

Re: Check for duplicate record?

Hi Steve,

Thanks for getting back to me on this.

Could you please give me an example of "validation checking code".

[Steve Workings]

Re: Check for duplicate record?

Well, it depends on exactly what approach you want to take.

Here's one example (I'm typing directly into the msg board, not in the Xbasic editor, so not perfect stuff here:)

Let's suppose your controls where they entered their name are vFirst and vLast, and your Customer.dbf fields are First and Last:

query.filter = "First = '" + alltrim(currentform.controls.vFirst.value) + "' .and. Last = '" + alltrim(currentform.controls.vLast.value) + "'"
query.order = ""
t = table.open("[pathalias.adb_path]\customer.dbf")
qdx = t.query_create()
if qdx.records_get() > 0
   CurrentForm.Has_Error = .t. 
   CurrentForm.Error_Message = "You've already registered." 
end if
t.close()

Note: This approach isn't sufficient: it'll reject Robert Smith after the first one, for instance.

[Keith Hubert]

Re: Check for duplicate record?

Thanks Steve, I will have a play in the morning.

I'm a bit rusty with the syntax but I'm sure to pick it up again as needs must.

[Keith Hubert]

Re: Check for duplicate record?

Steve that was just perfect.

It put things into context for me, many thanks.

What is wrong with Robert Smith? ;) Who is this guy?

I changed filter to LastName and Post Code.

[den1s]

Re: Check for duplicate record?

Guys,

I am now in need to do almost the same thing.

I have a calculated field in my table called 'Baseline'.

When a user enters details of an asset within my dialog component e.g. in fieldA and fieldB, then presses Submit. I need a check put in place to check for any duplicates exist within the table.

My main difference is that the calculated field 'Baseline' in my table is based on;

alltrim(fieldA)+"."+alltrim(fieldB)

I think i need to put some duplicate avoidance code in the table?

What do you think?

Denis

[Pat Bremkamp]

Re: Check for duplicate record?

Denis,

I think the easiest way is to create an index for your table that matches the calculation, then use the exist() function or keyexist() function to check if it is already entered.

Pat

[den1s]

Re: Check for duplicate record?

Thanks Pat, I will give that a go.

Denis

[ChristJC]

Re: Check for duplicate record?

Thanks Steve, I will have a play in the morning.

I'm a bit rusty with the syntax but I'm sure to pick it up again as needs must.

Hi Keith

I am trying to utilise the code that Steve has referred to in this thread, but I keep getting an error which seems to be related to the query.filter = "Surname = '" +

Any ideas where I could be going wrong?

function CanInsertRecord as v (DataSubmitted as P, Args as p, PageVariables as p, Result as p)
with PageVariables
dim t as P
dim qdx as P
t = table.open("persons")
query.filter = "Surname = '" +
alltrim(DataSubmitted.Surname) + "' .and. FirstName = '" +
alltrim(DataSubmitted.FirstName) + "'"
query.order = ""
qdx = t.query_create()
if qdx.records_get() > 0
CurrentForm.Has_Error = .t.
CurrentForm.Error_Message = "You've already registered."
end if
t.close()
Result.Cancel = .f.
Result.ErrorHTML = ""
end with
end function

In a nutshell, I am wanting to avoid the same person being entered twice into the dbase. To acheive this, the validation is based upon the surname and first name fields

Many thanks for any feedback you can provide

Jason

[MikeC]

Re: Check for duplicate record?

Jason,
If indeed your code is what you have in your post, then obvious what the problem is (script lines wrapped when they should not be). Use the "Code Tags" that are available in the Advanced reply to a thread (highlight your code that has your formatting --indents and such, then click on the # icon). Even if not the problem you will have to do this as the way you have it, it simply cannot be read by most to find errors.

the following would then be a one liner...

query.filter = "Surname = '" + alltrim(DataSubmitted.Surname) + "' .and. FirstName = '" + alltrim(DataSubmitted.FirstName) + "'"

[Steve Wood]

Re: Check for duplicate record?

I second Pat's suggestion and always use exist() to find duplicates. But (Keith) you probably know by now that using the name will never work. Too many Joe's and the same Joe will enter it Joe Smith and then J Smith the next time. That's why everyone uses email address or telephone, or SSN in the old day.

[ChristJC]

Re: Check for duplicate record?

Hi Mike

Many thanks for your reply, greatly appreciated. Your suggestion certainly fixed that issue, unfortunately the duplicate values are still being allowed. Should I insert something into the code to signify that a new record is being added? Here is the updated code with formatting as you sensibly suggested.

FUNCTION CanInsertRecord as v (DataSubmitted as P, Args as p, PageVariables as p, Result as p)
	WITH PageVariables
		dim t as P
		dim qdx as P
		t = table.open("persons")
		query.filter = "Surname = '" + alltrim Args.DataSubmitted.Surname) + "' .and. FirstName = '" + alltrim(Args.DataSubmitted.FirstName) + "'"
		query.order = ""
		qdx = t.query_create()
		IF qdx.records_get() > 0
			CurrentForm.Has_Error = .t.
			CurrentForm.Error_Message = "You've already registered."
		END IF
		t.close()
	END WITH
END FUNCTION

[Steve Wood]

Re: Check for duplicate record?

To test your query value, add this line below where it is set:

save_to_file(query.filter,"c:\test.txt")

and then go look at test.txt

If you want a simple but unattractive way out, use table Field Rules and make the field required to be unique. It will send a not-so-elegent message to the user that they cannot save the record if it is a dup.

[ChristJC]

Re: Check for duplicate record?

Hi Steve

It definitely works. I entered the actor Hugh Laurie into the grid and the .txt file result was

Surname = 'Laurie' .and. FirstName = 'Hugh'

Why would the qdx.records_get() > 0 not activate the error message and not allow the record to be added? Does CurrentForm refer to webgrids, or just desktop forms?

Many thanks for replying. Must be late in your part of the world

Cheers

Jason