Importing files which lookup each other

[brianofivanhoe]

Hi,

I am working with an old system which was built as a number of separate files which reference each other, rather than one file with multiple tables. I need some guidance here on good practice when changing layouts. When I have made a layout change, I backup the live version of the file, replace it with my new version, delete all records from my new version and then import all the data from the backup version. This works for most of the files but ... two of the files have lookup fields to each other. So the question is: which one should I import first? Or is there something I can run after importing to make sure ALL lookups get redone?

Looking forward to your help,

Brian

[omegagoh]

Dear Brian,

I think you are working on a system built prior to FMP7. The main concern is more on the lookup options set in both files. If the options set were simple and the data are expected to remain unchanged since entered, then start from the file logically having data first. After the importing of records, just do a re-lookup for the first file (the second file would have "re-lookup" from the first file during the import). However, the lookup options and import options may have overriding effect which you need to check prior to importing.

In a long run, I suggest that you adopt the data separation approach where user interface (UI) is separated from data store. The advantage is that you can replace the UI file without affecting the data (i.e. no need to delete records from the new version and import from the old version).

Currently, if your changes only involve the layout then what you need to do is to make all parts of the old layout to be the same sizes to the new layout. Next, delete all objects in the old layout; copy and paste all objects from the new layout to the old layout. Just beware that things are slightly more complicated if it involve changes to the related scripts (which you can copy and paste the script steps from the new version to the old version too!). However, it would be quite complicate if it also involve changes to the field definition.

[IdealData]

When importing data the final option is to "update serial numbers, lookups etc."

If you elect NOT to update the serials/lookups then you will get the data from the live system exactly as it was at import time. you will probably need to manually update the serials to ensure the next serial number is after the highest serial number in the records.

It is not necessarily desirable to update lookups during import as you may inadvertently change the data that was in the live file.

The whole process can be scripted - in fact I have a system under development now with over 50 tables to update by importing.

[brianofivanhoe]

Thanks, omegagoh, for your response - I will change the order of the import of two troublesome tables and see if that solves the problem. I intend to split the file into BE and FE too which will remove this problem but that's quite a major project due to the current structure. Watch this space ...

Edited June 30, 2010 by Guest

[brianofivanhoe]

Thanks, IdealData, for your reply, I will give the import a try according to your suggestion. I have tried a scripted import on another project and that worked fine but this one has some 'interesting' lookups and calculations.

[brianofivanhoe]

This one solved by doing the imports without any auto-enter options. Thank you for your assistance.