Validating for a unique value

[csr]

I am not using databases ... free tables only.

I wish to validate a value which is part of a new record being added to a file to find out if that value already exists.

I am using Row buffering.

The value I am validating is just one of many fields on the form.

I am recycling records (BLANK/RECALL) so I cannot be sure of the physical position of the record I am adding.

Various thoughts come to mind ....

Count for the field having that value

IndexSeek


Just wondering if there is some classic means of doing this



Don

 

[mkrausnick]

Indexseek() with .F. for the position parameter would probably be the fastest, assuming the field is indexed.

Mike Krausnick
Dublin, California

 

[csr]

Well, that was the answer I expected.

That was the easy part ...

Now here is the kicker ...

Let's suppose I go back into an existing record and tab the cursor through these fields again and when I exit the field I wish to validate ...

It gives me a message ... ALREADY EXISTS !

Because it will !!!!

Why does it, you ask ?

Because INDEXSEEK() has found it in the index, which is to be expected ... because it is there.

Now we somehow have make the exception for data that already exists and we did not change ... or something like that.

That is where my thoughts on counting came from.


So anyway ... now what ? Any classic means for dealing with this ?





Don

 

[mkrausnick]

So you wrote the record out and now you're basically in MODIFY mode? I would use RECNO() - know the RECNO() for the record currently being displayed, and if the INDEXSEEK() lands on it, then REPLACE instead of APPEND BLANK. Of course, then you'd be using INDEXSEEK with .T. for position rather than .F.

The other solution is to know whether the user is adding or modifying, and react accordingly - if adding, do not allow a duplicate, or if editing, allow changes to the existing record.




Mike Krausnick
Dublin, California

 

[csr]

Not so fast ...

There is more to this ...

Let's suppose you use the RECNO() to decide if it is a duplicate you found.

I guess that means if the recno() of the record you find is the same as the one you were on ... then it is not a duplicate. Right ? Is that the plan ?


Well, if that is the case ... Let suppose you are editing a record and you change that value to some other value that already exists ...

However, when you do the SEEK ... it finds the record you are on first because it happens to come first physically within the file. Then it will show that the RECNO() of the record you found is the same as the one you are editing and you will decide it is not a duplicate, when in fact the very next record also has that same value.

See the dilemma ?

This is why I brought this to this forum.

I can grind out a convuluted method for this ...

Something like SKIPping to the next record to see if that has the same value, and if it is ... I have found a duplicate ... OR ... in small files ... COUNT for values equal to the test value ...etc ..... but I am just wondering what how other programmers deal with it. I was hoping for something a little more elegant these code intensive methods.



Don

 

[mkrausnick]

Well, I see the dilemma, but consider the root cause - the user is entering what amounts to a primary key for the record. Would it be possible to take that away from the user and have the system generate it? Then you'd have the control to eliminate the problem.

A second question is: Once the user has entered a value, is it necessary for him/her to be able to modify it?

Another possiblity - since I always try to use SQL for things like this -

SELECT COUNT(*) FROM <table> WHERE <key> = <value> ...

which is the same as COUNT FOR but will scale up to client/server when your app makes the big time.

Food for thought.





Mike Krausnick
Dublin, California

 

[JanRog]

What about using Locate...
* --- assume current record is the one to be checked
Local nRecNo
m.nRecNo = recno()
* --- open table again so you don't have to get back to original record after locate
use <table> again alias DupCheck in 0
Select DupCheck
m.nRecNo = recno()
Locate for <key> = <value> .and. Recno()# m.nRecno
if found()
* --- duplicate key
else
* --- unique key
endif

 

[csr]

Agreed ... Some good points for thought.

The LOCATE FOR (From Jan) is a nice 1-step approach to finding a duplicate. I like that. However, I believe it moves the record pointer. That requires saving the original recno() and going back. Not monumental but extra machinery. I could probably build a my_locate() function to hide that and be perfectly happy.

Of course, this only works with relatively small files ... large files might take too long. Another possibility along this same line would be to use SEEK() and then SKIP to see if the very next record also has that value.


The statement (from Mike) that this is basically having the user enter a primary key has my attention also.

It is true and I also have a (system generated) primary key attached to all of my records. That makes having the user enter this code rather redundant. It has only survived in my thinking from the days when we commonly had codes for things which acted like primary keys. However, it probably is completely unnecessary now. I think that is the approach I favor.

Having said that ... I do not believe this eliminates the dilemma entirely. There are times when it is not desirable to enter a value into a file that is identical to some other previous value. Then we are right back to the same problem.

Then, I believe I will need to investigate these and perhaps other possibilities. Thanks for all who participated.




Don