Importing from single table to many related tables

[spankalee]

Hey there,

I need to import data from an older FM solution which was designed with very large tables into a new solution that uses many more tables and relationships. The Import function doesn't seem to let you import into more than one table, so I'm trying to figure out how to do it.

As an example, I have a customer table. Customers have addresses and phone numbers. In the old DB the customer table had all the address fields (street, city, etc) duplicated 3 times over for up to 3 different addresses, and there were 5 phone number fields.

The new DB has a many-to-many relationship set up for phone numbers, and 3 1-to-1 relationships for the addresses.

So I need to create the customer record, import the relevant fields, then create a phone record (and customer-to-phone link?) for each non-empty phone field, and create an address record for each address.

A script would have a whole mess of SetFields, and if I'm not mistaken would require a script in the old file to loop though it's records and then a script in the new file to create the new records. I'm not sure how I get the SetFields to work then with two separate files and no relationship between the two.

Is there maybe a way to do a few passes with import to get the customer record setup, and then the addresses records and so on?

Thanks guys,

-J

[-Queue-]

Import the file multiple times, first into one layout, then the next, etc. Then loop to create new join table records, if necessary.

[spankalee]

How do I make sure to get the addresses linked to the correct customer records? The customer IDs will be created on import.

[-Queue-]

Use globals to pass the data between tables. No relationship is necessary to access them in version 7. Or use a trick like I've described in this thread to do it without going to a different table.

[spankalee]

So I can't just use:

SetField(NewCustomer::Name; OldCustomer::Name)

Than would be too bad, because is I have to use globals then my script instantly becomes twice or fours times as long.

Either I create a global for each field I need to transfer, or I create a global for each type. Then the one SetField becomes four commands:

GotoLayout(OldCustomers)

SetField(NewCustomer::TextGlobal; OldCustomer::Name)

GotoLayout(NewCustomers);

SetField(NewCustomer::Name; NewCustomer::TextGlobal)

Then I have to do that for 30 or so fields.

ouch.

[-Queue-]

It depends whether there is a relationship between NewCustomer and OldCustomer to use. If you have an id field that is the same between them, then that would work fine.

[spankalee]

Ok, sweet. The old DB had a text code used as a key. So I was able to use that to establish a relationship and copy most of the fields. It works great for the one-to-one address relationships because I don't need to do any NewRecords, just SetFields.

But on the many-to-many relationships I'm not sure how to continue. The phone numbers are in a portal. The only portal related funtions are GotoPortalRow and DeletePortal row. But they don't specify which portal to goto.

So I think I have to switch to the PhoneNumbers table, create a new record, then switch to CustomerPhoneLink table and create a new record with the customer ID and phone record ID.

Is there a simpler way?

Thanks for the help Q

[-Queue-]

You specify which portal to go to by first going to a field that is in the desired portal. Go to Field [rel::field], then Go to Portal Row. I haven't worked with the new table structure enough to know whether this will prevent switching layouts to create the join though.