Copying record data from one table to another

[RSpisketts]

Hi,

Does anyone know the best way to copy fields from records in one table to fields in records in another table. I've tried using a portal but I need to copy fields from more than 1000 records (the limit for a portal).

Any ideas?

Thanks.

[SBG]

Export from the one file and import into the other file with the Import Action set to either Update Existing Records or Update Matching Records?

[RSpisketts]

Hi,

I'm not doing a file to file copy, but a table to table copy.

I want to copy several field values from records in one table, to records in another. I defined a relationship to restrict the records in the TO that the layout is based on, and have a portal with the other records. I loop over the portal rows and copy the fields when there is an appropriate match. It only works if you have 1000 records or less in the portal.

I really need to know if there is a better way of doing this.

Cheers,

Roger.

[-Queue-]

It's the same idea. Isolate the records you want to copy and go to a layout based on the table in which you want to import. Then perform your import (File -> Import Records), selecting the current file and the table (via the Source list) from which to import and then matching the fields and clicking Import.

[Brian C]

There is another way to transfer data from one table to another that does not involve using an import.

I discovered this idea when I was creating a payroll system and discovered it was a bit too easy to break my imports while I was still developing/testing the product. FMP 6 may break your stored import order if you delete fields due to their order of creation. I have not tested this problem in FMP 7 yet but I suspect it works if all referenced tables are located within the same file.

Though this process is slower and involves more steps, it is not bad if you want to always make sure you know where your data is going without having to print your script just to view the stored import information.

In any case here goes:

Setup global fields in your destination (child) table.

You should have one global field for each of the fields that you want to transfer.

Setup a relationship between the parent and child tables. Any relationship will do.

Use the setfield() script step with this relationship to send the data from a specified field in the parent table, to a global field in the child table.

Use a scripted loop to step through the parent table records to send data to the child table, and then execute an external script in the child table to put the data into the fields you want there.

Works for me!

[-Queue-]

There's an even easier method that does not involve globals, found here. I haven't tested it for v7 lately. (It didn't work using the first revision.) But it works quite well for v6.

I think importing is a better option in this case than either method though, considering that there are over 1000 records involved. Importing would probably be much faster.

[Brian C]

My hat is off to you good sir. A good idea, I dont think I would have found out that technique unless it was by accident!

Something for me to keep in mind when I'm using non-calculated keys in a relationship.

[RSpisketts]

Thanks alot for your suggestions !