2 Replies Latest reply on Jun 1, 2016 6:18 AM by ianmanning

    Find records that change after a relookup fields contents

    ianmanning

      I have a FM10 database that pulls data from an SQL database.

      One field in the FM database is OrderStatus which has 3 states, Open, Partially Shipped and Closed.

      I need to refresh the Order status field every hour and then process any records that change status.

      I can find all records that are not closed and relookup the fields contents. (once the Order is closed, it stays closed)

      I don't think that I can use a modification date as this will change every time the relookup field contents is run.

      My Idea is to have a script that will step through each found record and copy the current status to an "Old Status" field then run the Relookup field contents and have a "Changed" calculation field that compares the current status to the Old status.

       

      Is there a simpler way to copy all the Current Status field data to the Old Status fields without stepping through each record and using SetField[Order::OldStatus; Order::OrderStatus]

      There are normally over 1000 open or Partially shipped orders at any time so a small saving of time could make a big difference.

       

      Is there another way to see changes that I don't know about?

       

      Thanks

      Ian