is there a unique record identifier in each record that can be equal in both tables? or are you trying to compare field/field record by record? It might be very difficult to know what to compare if you don't have something that is equal (unique) AND unchangeable in both tables.
Hi inov9pro (would have used your full name if I knew it),
- how many records do the tables usually have?
- are there creation and modification timestamps stored with each record? These might already help to narrow down the number of changes to investigate. When designing a db, it's good FM practice to put those timestamp fields into every table.
As Beverly said, the easiest way would be to have a unique identifier that can be used to relate the records between the two tables. But using other values as matching criteria could work as well. I used that sometimes to match very different tables, e.g. to compare headwords of Wikipedia and another encyclopedia. A match can be found using a calculation (field) that evaluates the relationship between the two TOs: not IsEmpty(relatedTO::matchfield).
Another option would be to export the record data line-wise and do the comparison outside FM. You may have a look at the article on Unix diff in Wikipedia. Or export as XML and use tools like XML comparison in Oxygen or xmldiff.
This was about exact matching. If it comes to fuzzy matching, see the discussion here.
Yes there is a unique identifier in each table.
- There is a unique identifier for each table.
- There are about 83 fields in each table.
- There are nearly 1 million records in each table.
I am going to work using caculations to compare each field.
As Beverly said, if you have a unique identifier that relates the records in both tables, you have a chance.
Otherwise, forget it. You would have to calculate 1E6*(1E6-1)/2 = 4.999995E11 (half a trillion) record-record combinations.
Yikes. Luckily there are uniquie identifiers.
Another question is, since I do have a unique identifier is there an efficient way to compare these tables?