This Should be easy...... I thought

[draxx2755]

So, Madtowng..... I tried the script you wrote and it Put a .T. on all the records. Too funny, before it was all .F. Here's the script:

dim tbl2 as P
dim otherValue as C


tbl2 = table.open("dup_check")


'PleaseWait(.T.)

tbl2.fetch_first()
while .not. tbl2.fetch_eof()

tbl2.change_begin()
otherValue = LOOKUPC("F",dup_check->ref,"ref_","ameregl3","ref_")
dup_check->dup = if(otherValue = dup_check->ref,.T.,.F.)
tbl2.change_end(.t.)
tbl2.fetch_next()

end while
'PleaseWait()


END

I'll take a look at the other examples provided and see what I can see. Thanks again to all who have contributed, it is much appreciated.

Scott

[madtowng]

Scott,

It often helps to have some sanitized data for testing purposes.
So , for now, I created a couple dbf tables, nothing exciting, but
enough to confirm it works as expected. (You will need to make a few changes for your needs).

dim tbl2 as P
dim otherValue as C
debug(1)

tbl2 = table.open("masterdbf")


'PleaseWait(.T.)

tbl2.fetch_first()
while .not. tbl2.eof()
if tbl2.eof() then
debug(1)
end if
tbl2.change_begin()
otherValue = LOOKUPC("F",tbl2.ref,"ref","olddbf","ref")
tbl2.dup = if(otherValue = tbl2.ref,"T","F")
tbl2.change_end(.t.)
tbl2.fetch_next()

end while
if tbl2.eof() then
tbl2.change_begin()
otherValue = LOOKUPC("F",tbl2.ref,"ref","olddbf","ref")
tbl2.dup = if(otherValue = tbl2.ref,"T","F")
tbl2.change_end(.t.)
end if
tbl2.close()
debug(1)​

[draxx2755]

Thx Madtowng for the effort. I always have a "testing" version of my database to work with. I never use my live db. When I get back to the office on Monday I'll change what I need to and give it a go. Thanks again!

[madtowng]

well… there’s test data (a subset of your live data not meant for the world to see) and there’s sanitized data (data safe for the world to see).

For the purpose of this issue, i created 2 dbf tables with 10 rows of 5 letter ref ids.

[Ted Giles]

Just to try it, I am seeing about putting the dupe test in a Calculated Field on the main table.
Dinner got in the way.

[madtowng]

With this code, you'll still need to create the tables, but this will populate both tables
before doing the dup check. (Keep in mind, I didn't get to my current weight being overly ambitious)

dim tbl as P
dim tbl2 as P
dim otherValue as C
dim masterList as C
dim oldList as C

masterList = <<%txt%
aekvn
aizev
anmcp
fpory
hsxaf
irbxc
ntqyx
utwlf
vtohe
zfvxx
%txt%

oldList = <<%txt%
ewwbg
svasp
gfudn
lkode
ivdwb
zjwga
dxcdk
xwitc
ublgu
whwdg
ntqyx
hsxaf
%txt%
debug(1)

tbl = table.open("masterDBF",FILE_RW_SHARED)
'tbl.enter_begin()
for each id in masterList
    tbl.enter_begin()
        tbl.ref = id.value    
    tbl.enter_end(.t.)    
next 'id
tbl.close()    
tbl.open("olddbf",FILE_RW_SHARED)
for each id in oldList
    tbl.enter_begin()
        tbl.ref = id.value
    tbl.enter_end(.t.)
next 'id
tbl.close()        
tbl2 = table.open("masterdbf")


'PleaseWait(.T.)

tbl2.fetch_first()
while .not. tbl2.eof()
if tbl2.eof() then
    debug(1)
end if
tbl2.change_begin()
otherValue = LOOKUPC("F",tbl2.ref,"ref","olddbf","ref")
tbl2.dup = if(otherValue = tbl2.ref,"T","F")
tbl2.change_end(.t.)
tbl2.fetch_next()

end while
if tbl2.eof() then
tbl2.change_begin()
otherValue = LOOKUPC("F",tbl2.ref,"ref","olddbf","ref")
tbl2.dup = if(otherValue = tbl2.ref,"T","F")
tbl2.change_end(.t.)    
end if
tbl2.close()
debug(1)​

[Ted Giles]

Ho Hum!
Well I was proven right. Dinner gone and a field in the primary table has been set up to identify duplicates in the secondary table.
The checking is done in a SET and that can be a bit daunting, so please see the attached and pull it apart to see how it works.
There is also a dupe check which runs the Intersect operation as an example.

It seems a bit easier to upload what I meant rather than all this coding stuff!

Pick a Form and view if you so wish.

Dupes Validation Example.zip

[draxx2755]

You know I think Ted wins the prize with the Intersect. I just need to change my order of when I actually do the Intersect for it to work. Thanks to all for all your work on this and I want you all to know I sincerely appreciate the effort and the push in the right direction for me.

Take care all and Happy Thanksgiving!

Scott

[Ted Giles]

Glad you have a solution that fits your situation.
Remember you can also use the SUBTRACT operation to create a table of records from the Master table which Do Not have a corresponding duplicate data field in the subsidiary table.

[Mike Wilson]

Scott,
Glad you got this underway.
You wrote: "And just so you guys know there are a lot of records in the one table, probably around 35,000 or so.......".
Just for the record, which table has the 35K records?
Which table has the 35K records....The source table (tbl2=dup_check) or the target table ( tbl =ameregl3 )? Is the other table smaller and how many records are in the table that doesn't have 35K records?
To have it be said, there is no reason that the loop that is checking for a record field value match between two tables needs to happen with the big table being the source table checking against a table with less records. With the goal of finding matching ref values in the two tables, the execution is most effectively executed using a source table loop and target table filter method by having the source table being the smaller of the two tables.

@madtowng:
You wrote: "What's missing is that garage_2 has a list of items in order of the ref number. Once the ref number is found, the door is closed, and move to the next item."
I'm not sure from where the info that the ameregl3 table records exist in reg value order. However that makes no difference because the lookupc() and the tbl.order() function filter the target table (ameregl3) to the source table (dup-check) ref value. There is no loop through the ameregl3 table, so your statement that "each item is checked" I believe is invalid. And the whole point of my example is that ...yes... the garage door of the target table (amereregl3 = Garage_2) is opened and closed with each iteration through the source table (dup_check - Garage 1) by using the lookupc() function within each loop iteration of the source table, and that is the burden difference in the methods I was trying to reveal.

And if you can use operation as Ted is recommending... that's your ticket to paradise!

[Ted Giles]

I have automatic garage doors, so I'm OK with that.
The zip has an example of the Intersect operation, as well as loony Set stuff with a calculated field to auto set for dupes, Mike.

As Arthur Dailey said " the world's your lobster!"

[Al Buchholz]

Al, I can see that the sets you describe can show linkages, but I'm not immediately coming up with how the logical field value for dup is updated in the parent table with a conditional value based upon a link existing or not.

An operation - probably an update for the set.

Then you can see the results in a browse for the set.

[Ted Giles]

I forgot to mention that the Parent can be updated initially by running Reevaluate Field Rules in the little .zip I posted.
After that, if you add a record to the parent, which has a corresponding recording the child, the flag will be set to True.

[CharlesParker]

might be a good idea to create an index on the field your searching too