1 Reply Latest reply on Nov 13, 2013 1:04 PM by philmodjunk

    General Development Question - Importing data to off-line database

    jerreilly

      Title

      General Development Question - Importing data to off-line database

      Post

           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

        • 1. Re: General Development Question - Importing data to off-line database
          philmodjunk

               The related data should not get relooked up when you import the data into it. Make sure that you DO NOT select the option for enabling auto-enter options during import and the data should be imported from the original tables and nothing should be relooked up and auto-enter calculations should not re-enter data. That's one of the main reasons you use auto-enter field options for looked prices in an invoice in the first place.

               Fields of type calculation, on the other hand, will recalculate when the data they reference is imported. So if you find that data is in a field of type calcuation that should not recalculate, then it should be converted into a data field with an auto-enter calculation before you start importing data.

               And instead of deleting data from your tables via script, just use save a copy with the clone option to get a copy with empty tables for importing.

               Another "gotcha" to look out for is that your script should also update next serial value settings on all auto-entered serial numbers so that you don't get records with duplicate serial numbers after importing the current data into your new version.

               You may also be interested in transitioning to a Data Separation Model as a way to reduce the number of times that you'll need to take the active copy of the database down and import data from it into the new version.