Compare identical tables in FileMaker 19

0
votes

I'm writing a simple app that needs to update some data from an ODBC source.

I have the same table in a MySQL database and the FileMaker app. I want to write a script that will run through all records in one table and compare them to the records in the other table. If there is a difference between them, show a dialog and let the user choose to copy the new information into the FileMaker record.

I've got the ODBC and external data source part working and I have the tables visible in FileMaker (ESS). Looping over all records is easy, but I can't figure out how to find the identical record in the ODBC source via its primary key.

The tables are absolutely identical except for where they are stored. Same data fields, etc. and in most cases 99% of the data will be identical.

1
odbcfilemaker
Are you able to add the MySQL table as an external data source in Filemaker? – michael.hor257k
+1 to Michael's suggestion, but if you can't use ESS you need to run "SELCT" SQL query on your ODBC source using "Execute SQL" script step. You can use primary key in WHERE clause of the query. You will need "Execute SQL" and not "ExecuteSQL" – Nicolai Kant
@michael.hor257k yes, I have the tables visible in FileMaker already. (also updated the question text to include that) – Tom
@NicolaiKant that might be the right idea there. I forgot I can simply execute SQL from within FileMaker. I can hack my way out of a wet paper bag in SQL, so that sounds like it would probably solve my problem. – Tom
Then you should be able to define a relationship between the two tables matching on the primary key field. Once you have done that, you can access the corresponding record's data directly through the relationship. – michael.hor257k

1 Answers

1
votes

If you are able to add the MySQL table as an external data source in Filemaker, you should be able to define a relationship between the two tables matching on the primary key field.

Once you have done that, you can access the corresponding record's data directly through the relationship.