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

[draxx2755]

HI all. Been gone from A5 for a few years and dragged back into it now....lol. I wrote a script to compare a value in one field in a table to a field in another table and if there was a duplicate, it should mark a logical field in the first table as .T., if not then .F. . It is marking all records as .F. when I know for a fact that there are duplicates in the comparison table. The fields in the first table (Dup_check) are Ref, a character field and the second field is Dup which is the logical field. In the other table (ameregl3) it is looking in the Ref field to see if there are duplicates that match to the Dup_check table.

Here is my code:

dim tbl as P
dim tbl2 as P

tbl = table.open("ameregl3")

tbl2 = table.open("dup_check")


PleaseWait(.T.)

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

tbl2.change_begin()
dup_check->dup = IF( dup_check->"ref" = ameregl3->"ref", dup_check->"dup" = .T., dup_check->"dup" = .F.)
tbl2.change_end(.t.)
tbl2.fetch_next()

end while
PleaseWait()


END

This should be easy but obviously I'm missing something....lol.

Any ideas?

Thanks guys and gals......

Scott

[draxx2755]

I just realized I think I need to tell it to loop through the "ameregle3" table just not sure how to do that anymore. Also please disregard the attachment. Not sure how I attached that in the first place......oops.

[madtowng]

Hi Scott,
If it were me, I would use lookupc (I think I got the right one) to do the compare.
With that said, it looks like you’re comparing each record in tbl2 to the same record in tbl(maybe).

[Ted Giles]

May I enquire as to what needs to be achieved?
If you flag a record in table 1 which has a corresponding field record in table 2, what next?

Suggest you look at a Summarise operation which will quickly and easily identify matching records and create a 3rd table.
Or I may have misunderstood summat.

[draxx2755]

Thx Ted. That is peridot the simplest solution fur me to try. I'm trying to identify duplicate records by comparing a field in both tables. If a duplicate exists then i would be able to investigate further. Thx for the suggestion.

[Mike Wilson]

Hi,
Please clarify:
"I wrote a script to compare a value in one field in a table to a field in another table and if there was a duplicate, it should mark a logical field in the first table as .T., if not then .F. "

Is what you are really meaning this:
"I want to see if the value from a specific field in table 1 is present as a value in a specific field in table 2. If there exists a field value in table 2 that matches the field value from table 1, this is a duplicate and the record in table 1 is changed by making the value in a logical field=.T. otherwise logical field changed to .F."

If this is the situation, you will have to capture the field value from table 1 and filter the field in table 2 to the value from the field in table 1. If the filter returns record count >0 then you have a match and the changing the logical field to .T. needs to happens, or if not the reverse.

dim tbl as P
dim tbl2 as P
dim vfield_val as C
dim vfilter as C
dim vnr as N

dim cycle as N=0
dim vnrecs as N

DIM GLOBAL pDlg1 as {waitdialog}
pDlg1.Set_Title("Please Wait")
pDlg1.Create(1,"Percent")

tbl = table.open("ameregl3")
tbl2 = table.open("dup_check")
vnrecs=tbl2.records_get()

'LOOP THROUGH SOURCE TABLE
tbl2.fetch_first()
while .not. tbl2.fetch_eof()

'SHOW WAIT INCREMENT
cycle=cycle+1
pDlg1.Set_Message("")
pDlg1.Set_Bottom_Message(str(cycle)+" of "+(""+vnrecs))
pDlg1.Set_Percent(Var->cycle,Var->vnrecs)

'ACQUIRE THE FIELD VALUE TO CHECK FROM SOURCE TABLE
vfield_val=alltrim(tbl2.ref)
vfilter="ref="+quote(vfield_val)

'FILTER THE OTHER TABLE TO DETECT THE VALUE PRESENT OR NOT
xt=tbl.order("",vfilter)
vnr=xt.records_get()

'WRITE THE RESULTS BASED ON THE RECORD QUANTITY RETURNED
tbl2.change_begin()
if vnr=0
tbl2.dup=.F.
else
tbl2.dup=.T.
end if
tbl2.change_end(.t.)

'TEST NEXT RECORD
tbl2.fetch_next()
end while

tbl.close()
tbl2.close()
pDlg1.close()​

[johnkoh]

Scott,

dim tbl as P
dim tbl2 as P

'tbl = table.open("ameregl3")        'make inactive

tbl2 = table.open("dup_check")


PleaseWait(.T.)

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

'changed  build the condition before "change_begin"
'dup_check->dup = IF( dup_check->"ref" = ameregl3->"ref", dup_check->"dup" = .T., dup_check->"dup" = .F.)
'
filter_at_here = build_your_filter_here        'I do not know the table fields. example: "ameregl3" = +quote( tbl2.Your_field_name)
vKeyRef   = lookup("ameregl3",filter_at_here,"ref")
vUpdated = If(tbl2.ref = vKeyRef , .t. , .f.)
'

tbl2.change_begin()
    tbl2.dup = vUpdated        'changed
tbl2.change_end(.t.)
tbl2.fetch_next()

end while
PleaseWait()​

[madtowng]

Here is a mockup of what I suggested.
Documentation for the LOOKUPC function is here.
You will need to supply the actual index name.

' dim tbl as P not needed with this example

dim tbl2 as P
dim otherValue as C

' tbl = table.open("ameregl3") not needed with this example

tbl2 = table.open("dup_check")


PleaseWait(.T.)

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

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

end while
PleaseWait()


END​​

[Al Buchholz]

Consider defining a set that uses an expression to link the 2 tables together.

If you are only checking one way - ie table 1 to table 2 - that's all you need to link.

If you are checking both ways then make a 3rd table with append operations using the first 2 tables as input.

Collect enough information in the 3rd to link to each of the 1st tables and to indicate which table it came from.

A set with the 3rd table as the parent and the other 2 as children can be viewed to verify the process is correct.

[Ted Giles]

That's sort of why I asked what was going to happen next, AL (as opposed to Al which looks like Artificial Inteligence)
A bit like dogs chasin' cars. Whaddya do when you catch 'em?

[Mike Wilson]

Imagine you have two garages that face each other, each with a large cubical shelving array, and you want to know if the items in the cubicles in garage_1 are the same as the items in the cubicals of garage_2. So you open the garage door to garage_1 and you look at the item in cubicle_1, then you go to look at the item in cubicle_1 in garage_2 to see if it is the same. To do this you must first open the garage door to garage_2 to get to the cubicles of garage_2, and after you have looked in the cubicle of garage_2, as you go back to garage_1 you close the garage door of garage_2. Next look, garage_1 cubicle_2, open the door of garage_2, look at garage_2 cubicle_2, close the garage door of garage_2, etc. All that opening and closing of the garage_2 door, it's exhausting! Wouldn't it be easier to open and close both garage doors one time, execute the searches and close the doors at the end of you looking?

My script and the scripts from johnkoh and madtowng will produce the same results. However, the use of lookup() and lookupc() has the script open, filter, and close the second table with every iteration of the loop - i.e. open and close the garage door with every look. My script opens and closes the second table only one time and only filtering of the the second table happens with each iteration of the loop. A subtle but important difference.

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.

[madtowng]

Mike Wilson 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.
Your way, each item is checked. Not so bad if there's only 100 items in garage_2 (I would use an array in this case),
but a lot different if there's 20000 items.

[Ted Giles]

I have had a lot of fun with this. All straight out of the box as Alpha was designed to be used.

Firstly, the Summarize I suggested was wrong. It should be an Intersect. (A neck oil moment!)

So I simply built an intersect based on the values of the field being interrogated. The result shows in a new table and has all the duplicates in it.

Then, for giggles, I built a Form with two browses, side by side, based on a set, linked by the field being interrogated and set to UNIQUE LINK. ( A little used feature?)
The Form brings up the two tables, side by side and only identifies matching field values.

The user has 2 ways now of correcting/locating/viewing duplicates. Through a new table, or by displaying a browse.

This is the intersect code.

target_table_name = filename_decode("[PathAlias.ADB_Path]\duplicates.dbf")

a_tbl = table.open("data1")

intersect.t_db = "data2"
intersect.o_file = target_table_name
intersect.m_key = "DATA"
intersect.t_key = "DATA"
intersect.m_filter = ""
intersect.t_filter = ""
intersect.m_count = 4
intersect.delete_o_dd = .T.
intersect.m_field1 = "Record_Id"
intersect.m_field2 = "Data"
intersect.m_field3 = "@Data2->Record_Id"
intersect.m_field4 = "@Data2->Data"

a_tbl.intersect()

This is the Browse form. They are different tables and recnos.
alpha01.jpg

[draxx2755]

Mike, that is exactly what I'm tying to achieve and between you and Ted, I'm feeling dumber and dumber....lol. Listen everyone thanks for all the great input and suggestions. I now have a couple different ways to go. And just so you guys know there are a lot of records in the one table, probably around 35,000 or so.......

[Ted Giles]

Nah, you'll get there.
I used this approach - Intersecting in gvmnt work on millions of records.
Took a while to run though. 35k ain't so bad.
Try the simple intersect option - in the Operations tab.
Back it all up first!
Just select the primary and secondary tables, important identifiers and the potential duplicate field, and run it.
When you have the new table, you can do what you wish with it.
At least you will get a feel for the volume.