importing multi-table db

[hpw]

hello!

we put together our contact db existing out of several excel files, then we transferred it one-to-one to fmp8 to clean it up. Now we have to transfer it to a relational multi-table db based on the clienttracker template.

we have around 2000 organisations (should enter the client table) and within them some 4000 contacts (should enter the contact table). The contact table is related to the client table by the field zk_F_clientID.t an auto-entered value.

the organisation field in the contact table uses a valuelist generated of the organisation names entered in the client table.

so: how can i import the contact data into the contact table so the contacts are automatically related to the data of the organisation in the client table?

thanks for your help!

hans

[Søren Dyhr]

Well, I toyed a little with it some years ago! Take a look:

http://www.databasepros.com/FMPro?-DB=resources.fp5&-lay=cgi&-format=list.html&-FIND=+&resource_id=DBPros000686

--sd

[hpw]

it's quite tricky everything actually. the fmp one-table db resulting out of the various excel files has some 4000 records (contacts) in 2000 firms. that's the first difficulty as i wrote above.

the next difficulty is that there are some comment fields (4 of them for each contact) and they need to be imported in the contact log table.

It seems that it is not possible to import from one table into several tables at the same moment (or is it?)

if not possible, I thought in my naivity that I could import first everything into the client table, then export the allocated auto-number (zk_P_clientID.t) to the source file, than import the contacts into the contact table matching the auto-number field (zk_P_clientID.t) with the related field (zk_F_clientID.t) in the contact table.

Afterwards I would have gone further doing the same thing with the related serial fields zk_P_contactID.t (contact table) and the zk_F_contactID.t (contactLog table)

i know this sounds completely confusing but i just can't explain it better as it is confusing for me.

hans

[Søren Dyhr]

It seems that it is not possible to import from one table into several tables at the same moment (or is it?)

It's an excellent challenge though, but I can only make it happen with Applescript, and you are eventhough stumbling over some buffersize problems you have to work around, somehow!

But perhaps others can see the light??

--sd

[Fenton]

It is a little confusing, the way this is being explained. I cannot tell if you are using IDs to related the files or not. I can't really tell if you are still trying to tie multiple files together, or have already brought the data into your final 1 file, multiple tables file.

It sounds like you have either a mixture of IDs and names to use for relationships, or only names. I can't really tell. But if it was all IDs I don't think you'd be having a problem.

What I would do would be to create relationships between the multiple separate files, using whatever I had to tie them, check and test the records for connection, populate IDs if missing, then import.

It is possible to do "temporary" relationships using names, if that's all you got. But you must test afterwards; there will usually be a few that missed, because of spelling errors, etc.. Once you've got 100% working name relationships, you use them to bring in real IDs, starting at the main table (Organizations; but does everyone belong to an organization?)*

*Do you want to put Organizations AND Contacts in the same table, using a join table to tie them; some people use this method. It depends on your mix of Orgs and Contacts, and what you use them for.

If you could carefully explain the structure that you have, without too many superfluous details, we could likely help better. Try not to ramble, as I do :-]

[hpw]

thanks! it's a hell of a job really but i found a way round using names and IDs like you wrote Fenton.

i surely will have to come back to you guys in a later stage of my job.