Checking for duplicate keys

[steve3739]

Am starting to rewrite an old FoxPlus (DOS) program from scratch using VFP50. One of the (free) tables will be a vendor table, with the user assigning a unique vendor lookup key (they could be adding a new vendor, or changing the lookup key of an existing vendor).

I have come up with 2 ways for checking for duplicate keys as the user enters a value into the vendor id field of the vendor add/edit form:

1) Set lookup key as a candidate key (with record buffering on). The problem is, when checking for a duplicate key, the record pointer is moved resulting in the table being updated with an incomplete record.

2) With record buffering off, use scatter memvar to populate the input form. After checking for a duplicate key, append a record to the table, and update it with the field values from the form.

# 2 is my preferred way. Any better way of doing this?

Thanks for any suggestions.

 

[DSummZZZ]

I'm not sure if VFP 5 supports INDEXSEEK() or not, but if it does, you can use that to do a lookup without moving the record pointer:

IF INDEXSEEK('something', [b].F.[/b], 'SomeTable', 'sometag')
   *...not unique
ELSE
   *... add record
ENDIF

Where .F. says don't move record pointer.

Another option would be to open the table in another work area and use SEEK():

USE sometable AGAIN ALIAS lookup
IF SEEK('something', 'lookup', 'sometag')
   *...not unique
ELSE
   *... add record
ENDIF

-Dave Summers-

Even more Fox stuff at:

http://www.davesummers.net/foxprolinks.htm

 

[steve3739]

Dear Dave,
I don't recall seeing the indexseek() function, but I'll check when I get home tonight. Your second suggestion looks like a good solution -- quick and minimal coding. Thanks very much for your help!

Steve

 

[alvechurchdata]

You could use SQL - that doesn't move the pointer:

Select VendorID ;
  from Vendor ;
  where VendorID = PossibleDuplicate ;
  into array laDummy

If _Tally>0
  *-- PossibleDuplicate exists already
else
  *-- PossibleDuplicate doesn't exist
endif

Geoff Franklin

http://www.alvechurchdata.co.uk

 

[steve3739]

Dear Geoff,
Thank you for the suggestion. I'll give it a try.
Steve

 

[TamarGranor]

A couple of comments. First, rather than doing this in VFP 5, you'd be well-served to get VFP 9 and do it there. There have been tons of changes between those versions, including quite a few that will make it easier for you to come up to speed.

Second, don't let users specify primary keys. If the user really needs to set a vendor code, make that a candidate key, but always put a unique field that you control into each table. It'll save you a lot of code.

Tamar

 

[steve3739]

Thanks Tamar, I appreciate your input.
Regards, Steve

 

[steve3739]

Followup: Here is the final code I used in the field's valid event. It works for both adding a new record, and changing an existing lookup code (Note-VFP5 does not support the indexseek() function):

dimension lookup(1,1)
store 0 to lookup(1,1)
select recno() from vendor into array lookup where vendid=this.value
if lookup(1,1)>0 .and. lookup(1,1) <> recno()
=messagebox('Vendor ID already in use.')
this.value=space(len(this.value))
return .f.
endif

Also, each table will have a unique key that the user does not have access to.

Thanks to all for your help.
Steve