I love this routine which someone on Profox posted a long time ago.
PROCEDURE nodupes
LPARAMETERS lcfile
LOCAL lcalias, lcfilepath, lnarea
IF PCOUNT() = 0
lcfile = GETFILE('DBF')
ENDIF
lnarea = SELECT(0)
lcalias = JUSTSTEM(lcfile)
lcfilepath = ADDBS(JUSTPATH(lcfile))
* this will index unique and recall will only recall one and no duplicates
SET DELETED OFF
IF USED(lcalias)
SELECT (lcalias)
ELSE
SELECT 0
USE (lcfile)
ENDIF
SET FILTER TO DELETED()
REPLACE ALL status WITH 'D' FOR DELETED()
SET FILTER TO
DELETE ALL
* index as you wish to test for duplicates - since it goes to an idx,
there is no need to open exclusive
INDEX ON wkdate + wktripno + wkevent + wkstdate + wksttime + wkenddate +
wkendtime + wkmins + wkdriver + status UNIQUE TO lcfilepath + '_Temp.idx'
RECALL ALL
SET ORDER TO
SET DELETED ON
DELETE ALL FOR status = 'D'
SELECT (lnarea)
Jeff
---------------
Jeff Johnson
[email protected]
(623) 582-0323
www.san-dc.com
On 12/01/2010 05:12 PM, Kevin Cully wrote:
> You can create an index on that field, and make the type "Candidate" and
> that should prevent duplicates.
>
> This candidate key includes blanks so if you add a record with a blank
> value, that is allowed. The next record being added with a blank value
> would *not* be allowed.
>
> I've seen this cause trouble in older VFP systems that use an APPEND
> BLANK followed by a REPLACE statement. If the APPEND BLANK succeeds,
> but the REPLACE statement fails, you can be left with an "orphaned"
> blank record in the system. All subsequent "Add" operations will fail.
> If you use just an INSERT statement, you should be okay.
>
>
> On 12/01/2010 06:39 PM, Sytze de Boer wrote:
>
>> I have a client who has asked me to add a field to a table
>> The app is very old, but works a charm
>> (I think they are using sql to access the free standing table)
>>
>> They have asked for a Character field, 32 chars
>> THE FIELD MUST NOT ALLOW DUPLICATES
>>
>> How do go about this ?
>>
>>
>
[excessive quoting removed by server]
_______________________________________________
Post Messages to: [email protected]
Subscription Maintenance: http://leafe.com/mailman/listinfo/profox
OT-free version of this list: http://leafe.com/mailman/listinfo/profoxtech
Searchable Archive: http://leafe.com/archives/search/profox
This message: http://leafe.com/archives/byMID/profox/[email protected]
** All postings, unless explicitly stated otherwise, are the opinions of the
author, and do not constitute legal or medical advice. This statement is added
to the messages for those lawyers who are too stupid to see the obvious.
