Display Duplicae records with originals - HELP

[Geoffrey Hollander]

Hi,

I�m trying to display duplicate records after I have marked them in my table. For this example, let�s say that I�m checking for duplicate last names. While I can query and display all marked records, I would also like to display the original, unmarked records to which these dupes belong. Like this:

[unmarked()] Jim Johnson
[marked()] Bill Johnson
[marked()] Sam Johnson
[unmarked()] Tom Smith
[marked()] Bob Smith
[unmarked()] Bill Brown
[marked()] Tim Brown
[marked()] Jane Brown
etc.

Obviously, I want to omit any records that are unique (with unique last names, in this case) and only select those that have dupes along with those dupes as well. Once I find them, I would like to put a dupe-group number in a �DUPE_GRP� field (Numeric, 8, 0), so the result would look like this:

1 [unmarked()] Jim Johnson
1 [marked()] Bill Johnson
1 [marked()] Sam Johnson
2 [unmarked()] Tom Smith
2 [marked()] Bob Smith
3 [unmarked()] Bill Brown
3 [marked()] Tim Brown
3 [marked()] Jane Brown
etc.

I have tried the following script, but I think that hunting accident did more damage than the doctor indicated, because I think it looks as though it should work�
''XBasic

DIM counter as N

counter = 0

tbl = table.open(WorkFile)

query.description = "Find Marked Dupes"
query.order = "LAST + IIF(.NOT. MARKED(), 'A', 'B')"
query.filter = ""
query.options = "I"

tbl.query_create()

IF tbl.mode_get() > 0 THEN

ui_msg_box("Error:", "Stuck here, Boss! ")

END IF

tbl.fetch_first()

WHILE .not. tbl.fetch_eof()

IF marked() THEN

tbl.fetch_prev()
counter = counter + 1

commit_flag = .T.
tbl.change_begin()

ON ERROR GOTO Error_handler
tbl.dupe_grp = counter

tbl.change_end()

tbl.fetch_next()

WHILE marked()

commit_flag = .T.
tbl.change_begin()

ON ERROR GOTO Error_handler
tbl.dupe_grp = counter

tbl.change_end(commit_flag)

tbl.fetch_next()

END WHILE

tbl.fetch_next()

ELSE

tbl.fetch_next()

END IF

END WHILE

Error_handler:

commit_flag = .F.

END

Any insights would be appreciated - I'm trying to write a dupe-checker that will let me edit and report on duplicates in mailing lists.

Thanks (in advance) and regards,

Geoff Hollander

[Geoffrey Hollander]

RE: Display Duplicae records with originals - HELP

Ooops, this thing lost all my indents -- hope that the code is readable... Sorry.

Geoff Hollander

[Tom Cone Jr]

RE: Display Duplicae records with originals - HELP

Geoffrey, I would look in a different direction, instead of running multiple queries and a few operations, I'd just sort the table and then step through it top to bottom.

In pseudo code:

1. Set index primary to arrange records in desired sort order.
2. Run update operation against all records in the table to zero out the dupe_group number field.
3. Return to top of file; set dupe_group variable counter to one.
4. Store key value in variable
5. Fetch_next, if it's a match, fetch_prev, then while match exists write dupe_group number to each record, fetch_next and set dupe_group value until no match occurs; end while; increment dupe_group number.
6. Go to step 4, repeat step 5
7. Continue until end of file

Using this approach there's no need to mark records. Only those with duplicate values have a dupe_group counter. Filter your report for records with non blank values for the dupe_group field.

[Geoffrey Hollander]

RE: Display Duplicae records with originals - HELP

Tom,

Thanks for the suggestion. I like your approach as well. I'll try it and let you know how it works.

Thanks and best regards,

Geoff