Large search and replace problem

[Saucepan]

hi

ive got 2 quite large tables; one with some 65 000 records, the other with nearly 5 000.

now i need to check the 65 000 one to see if any of those records already exists in the 5 000 pile. im using a $id = $id2 type of script with 2 diffrent loops, one looping first through the small script, and then exiting back to the other loop, which lopps throught the big one. both commiting after the last record.

this takes forever, anybody know how to speed this up?

[Fitch]

Try a matching records import.

[comment]

In the smaller table, Show All Records, then Go to Related Record [Match all records in the current found set ].

[Saucepan]

thanks but i need to script it, i need to save the unmatched ones and check them again to another list befor i just delete them. is there anyway to scrip whatever you just said?

[comment]

Show All Records is a script step, and so is Go to Related Record. Actually, I don't know of another way to go to related record/s, so you must script this.

[Saucepan]

k, i c...

thing is that im on a swedish copy of fm8 so i have to translate everything into swedish, and the translations for fm is not good in the first place so its basicly guessing. ive got the "show all records" as a normal search mode, thats why...

anyhow, thanks a bunch!

Edited July 26, 2006 by Guest

[Saucepan]

ok.. doingh the show all, and goto related but all i get is that i end up in the smaller table, with it showing all records. do i need a "goto table" option?

[comment]

You need to go to the bigger table, using a relationship that defines a duplicate.

[Saucepan]

im a programmer by basis, please talk code...

kinda like:

goto layout(smaller)

show all()

goto related records(shoe search results only)

goto layout(bigger)

?

[comment]

Go to Layout [ some layout of the smaller table ]

Show All Records

Go to Related Record [ From table: “bigger”; Using layout: some layout of the bigger table ] [ Show only related records; Match found set ]

[Genx]

...

Go To Layout[smaller]

Show All Records[]

Go to Related Record [ From table: BiggerTable; Using layout: Layout tacked to Bigger table ] [ Show only related records; Match found set ]

Loop

Set Field[bigTable::Exists ; 1]

Go To Record[Next ; Exit After Last]

End Loop

The above or close to it, will do comments suggestion and show you those records that exist within the big table that are in the small table, or if you want to do it the other way around, run from big table. The field Exists, is just a marker field so if you need you can find those records again and then check it with your other list.