Best way to update a table via ODBC?

[Rochelle_505]

Hello all,

I am about to update two FM7 tables via an ODBC connection, and need some advice. The tables consist of customers and products. I only need to update the existing records and then add any new records.

Problem: After I choose the Update existing records in found set, and check the Add remaining data as new records FM appends to my database. I started with 513 records and ended up with 1056. There were only 30 new customers that should've been added. Did I miss a step?

The ODBC source is coming from as AS/400 file. Any advice will be appreciated. Thanks!!!

[B-Man]

If you choose Update existing records it overwrites your data record by record, it does not do data comparison. So record 1 of your imported data overwrites record 1 of your FM data.

If you want it to do data comparison you need to choose Update Matching records and then click on the mapping arrow and change it to a = to tell what field you want to use as a comparison. If you want to add new records make sure you check Add remaing data as new records.

Hope this helps

[Rochelle_505]

:exactly:

I imported with the Match option and checked the Add remaining data as new records. VOILA FM updated the existing records and added the remaining as new records correctly.

Before I was deleting ALL RECORDS and importing to an empty table, which was not good because when I went back to my invoices they were all gone!!!!Thankfully I had the foresight to test this first with a copy of the database. THANK YOU THANK YOU!!!