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?