2 Replies Latest reply on Dec 10, 2014 6:27 PM by wfgclapp

    Compare & Update records between two tables

    wfgclapp

      Filemaker 13 server and Pro, and Go. I have two tables. One is a 'scratch' table and the other is for live data. Most fields in both tables are identical.

       

      I have records that are imported from my ERP into the scratch table and some of the data in these records will change throughout the day. The record key will stay the same, but some of the field data will change.

       

      I want to be able to show the user whether a record has been changed since the last update.

       

      So...my thinking is to import data from the ERP into the scratch table periodically throughout the day. Upon each import, I'm envisioning a script that will compare certain fields in both tables and if a field is changed, the a field named 'Status' in the live table will be changed to 'changed' and the changed field in the live record will be updated with the new data from the scratch record.

       

      I also want to check for records that are in the live table but are not in the newly imported scratch table, i.e. deleted records. In that case, I want to make the 'Status' field in the live record change to 'deleted'.

       

      This was a user can see if a record has been changed or deleted.

       

      There may be an easier way than what I'm thinking about, i.e. using these two tables.

       

      But if these two tables sounds like a good way to go, can someone help me with how I might script this?

       

      I've made my pertinent relationships between my live and scratch tables.

       

      Thanks for any help.