General Development Question - Importing data to off-line database
I'm not a database guy so I suspect I may be trying to use the wrong general approach to solve a problem and could use some advice.
My company runs via a large and very old FileMaker database that was put together many years ago. I've just taken the entire old FileMaker4 version of the database and updated it to FileMaker12 and that works fine. I've also configured and tested the web interface to it and that's fine.
Now I want to add a series of new features. I've taken a copy of the current database and planned to work on it off-line so as not to interfere with the company's day-to-day operation. My plan was as follows:
* Add and test features in my copy of the database
* When they've all been tested and OK'ed, import the entire contents of the on-line database into my copy
* Replace the on-line version of the database with my copy.
I wrote a series of scripts that delete all the records in my copy and then import from the on-line database by importing from it's fmp12 files. At first glance it worked fine. At 2nd glance there was a whole series of problem. All the problems come down to the same issue. There are a number of fields throughout the database that are either calculated or pulled from a different table. When the new data gets imported, all those fields get recalculated.
The simplest example would be as follows:
* An order entry clerk enters an order by selecting a part number from a list. That part number is used to pull a description and a price from another table. That description and price are stored with the order.
* Over the years, the tables containing the prices and descriptions change so that today's prices and descriptions are different than they were two years ago.
* If I look at the on-line data, old orders show the old prices, new orders show the new prices.
* When I import the entire database, those fields from the old orders do a lookup from the parts table and overwrite the old prices/descriptions with the new ones.
I emphasize that this is a specific case but there are many of them throughout the design so the solution can't be changing those specific field definitions.
My two questions are:
* Is there any way to bulk import data and turnoff all look-ups and calculations.
* Is there an entirely different process that I should be using instead to solve the general problem of doing lots of new development off-line, updating to the current data set and then going live with the new system?
Thanks for any tips you can offer