Importing All Records in a Database

[Ron Cates]

Hi All,

I am wanting to take my database off site and continue to develope. When I return to the office I would like to replace the old file with the updated one I will be working on. I imagine that it is a matter of importing all existing records from the old file to the new one, But I'm a little unsure about how to go about it. Thanks for any suggestions..

Ron

[IdealData]

If this is a one off event then do the following BEFORE you start:

1. Create a "root" layout for each TABLE in the current file.

Take your copy and when you return

2. Delete all records in the development file

3. Go to each "root" layout in turn in the current file

3.1. Show all records

3.2. Go to the equivalent "root" layout in the development file

3.1. Import records from the current file into the development file (by matching names)

3.2. Update serial numbers in the development file

Finished.

[Ron Cates]

I would like to be able to do this on a somewhat regular basis. Would it be possible to script this? I have 29 tables. Seems like it would be pretty time consuming otherwise.

Also, when creating a "root" layout I am assuming that this layout would have every field in the table on it. Is this correct?

As for Updating serial numbers. Is that something I would select during the import process?

And as always, thanks for all your help Ideal.

Edited January 29, 2010 by Guest

[alwynus de winslow]

I have a solution that's in regular development AND in production use by the client. When installing the new file, all of the data is moved from the old one by a script which runs subscripts. Each sub script handles one table import roughly as described above; then it sorts by ID, goes to the last record, and updates the serial number via the script step "Set Next Serial Value".

Difference in my import is that I don't use the Standard layout as a destination or do a find-all in the source; all the records and all the fields are imported into the clone file regardless. At least they seem to be; YMMV.

[Fenton]

There are basically 2 methods for updating serial IDs. Both use the Set Next Serial Value script step.

Method 1. I think most people use the above, Go To Record [ Last ], and just increment that last serial ID. This is usually good enough. But there is a relational situation where it may not be.

If you do not have the [x] Delete related records option on the main relationship to the child table, then the following situation is possible. The last record in the parent table had related children. Then the parent record was deleted, leaving orphaned children; not a big problem, yet.

You then Import, and reset the next serial id of the parent table. The next record will then have the same serial id of the record which was deleted. Which is OK, as far as that goes, but it will link to the orphaned children from the record which was previously deleted.

So, the upshot is, if you use this method you should have [x] Delete related records turned on. Or never delete the last record.

P.S. There is a SerialIncrement ( text; incrementBy ) function, which can be used if you have a text prefix on your serial ids.

Method 2. In FileMaker 6 I believe, the GetNextSerialValue ( "file name"; "field name" ) was added. If this is setup properly it will update the serial id directly from the next value from the source file you're importing. ("field name" is actually "table occurrence::field name")

Both "file name" and "field name" are TEXT values. You cannot just point to the field (because that would reference the field's value, not its name). And you must know the name of the file you're importing from; which is not usually a problem.

In FileMaker 10, the GetFieldName ( field ) function was added. This can be used as the 2nd parameter, instead of the text reference, making it more robust.

This method bypasses the possible "last record was deleted, leaving orphans to get mismatched to new parent" problem. You can also write a "check next serial IDs" routine for after the import, using the GetNextSerialValue to target each file, and compare the values; they should be the same.

This method requires that the "source" file be open. Which means that you also need the Show All Records routine. Whereas method 1 works best if the source file is explicitly closed; never opened. The Import step does not "open" a file.

So, the methods are very different in that important regard. I've used method 2 in a very large file. It was a lot of work to build the routines. But they work well, and I don't have to worry about orphans getting mismatched. But proper setup of delete related in relationships solves that problem anyway (which I've also done).

Edited January 29, 2010 by Guest

[Ron Cates]

Wow, thanks for the advice guys. It seems a lot more complicated than I originally thought. But I think it is a must for me because I would also at times like to do my development on a development version and update the original afterwards to prevent any accidents while developing. I guess I have some work to do and a lot of testing with backups before I try it on the live version.

Thanks again guys

[RodM]

Consider a memory stick the whole application.

I have an import routine that goes through each table..

In testing, my Data_old file is exact copy of my Data file

Select layout

Delete All

Import from my Data_old.fpt file

Select next layout

All my clients for updates just rename their data and imports to new version work great for my app

[Ron Cates]

Ok guys. I am still very confused about the update serial number part. Are you saying the serial numbers don't import and thus you have to reassign them via update serial numbers? I still feel like I am missing something very important here. Can someone break it down a little simpler for me? Or maybe if anyone knows of a tutorial or set of detailed instructions, maybe with examples?

Sorry to sound stupid. But I need to get this.

Thanks

Ron