Jump to content

This topic is 2356 days old. Please don't post here. Open a new topic instead.

Recommended Posts

Hello,

I have two tables: one is company and the other is contacts. One to many relationship in between. I want to import contacts from different FM database into this one, but the problem is :

I have one table in that db for company and contacts(like contact1...contact12) and separate tables in which I am importing to. I fear I have to start fresh entering 1500 records manually and their contacts.

Please help!

 

 

Link to comment
Share on other sites

7 minutes ago, dav1089 said:

I fear I have to start fresh entering 1500 records manually and their contacts.

Certainly not. You just need to import the data 13 times: first, import the CompanyID and all the fields describing the company into the Companies table. Then do 12 imports into the Contacts table, each time importing the CompanyID and the field (or fields) describing one of the 12 contacts. To avoid ending up with empty contact records, do a find in the source table before importing each set - so that only companies that have a contact in that position are sourced.

There is a way to reduce the 12 imports to one, by creating a repeating calculation field - but since you only have to do this once ...

Link to comment
Share on other sites

Hi,

Sorry I didn't mention, actually, I already have some of the source companies (records) with missing contacts in the target table as well as other companies. 

So how can I map companyID to the contacts in that case?

(found set?) but then it will be duplicate records in target db.

 

Edited by dav1089
Link to comment
Share on other sites

11 minutes ago, dav1089 said:

I already have some of the source companies (records) with missing contacts in the target table as well as other companies. 

Do both tables have a CompanyID field? If yes, are the values of the duplicates the same in both tables? Also, do the "other companies" already have any related contacts?

Link to comment
Share on other sites

This is a fine mess... Here's one way you could go about it:

1. In the target Companies table, set the CompanyName field to validate as Unique, Validate always.

2. Import all companies from the source Companies table into the target Companies table; set auto-enters on during the import, so that every company is assigned a unique CompanyID.

3. Add a CompanyName field to the target Contacts table (this is just temporary).

4. Import the contacts as explained above (12 times) into the target Contacts table, but import the company name instead of the (non-existing) CompanyID.

5. Temporarily change the relationship between the two target tables to match on CompanyName.

6. In the target Contacts table, show all records, click into the Contacts::CompanyID field, and replace the field contents with calculated result = Companies::CompanyID.

7. Change the relationship back to match on CompanyID, and delete the CompanyName field from the target Contacts table. If you like, remove the unique validation from the CompanyName field in the target Companies table.

Make you sure you have a backup before you start.

Edited by comment
  • Like 1
Link to comment
Share on other sites

Comment explained it very well but here is a file showing how to serialize parent and child tables (In this case Companies and Contacts) switching from basing the relationship on names to being based upon internal, meaningless IDs.

http://fmforums.com/files/file/60-migration/

It might be useful as well.  :-)

  • Like 1
Link to comment
Share on other sites

@comment When I change relationship back to CompanyID pk->fk , the portal data doesnt stay.. but if I change relationship back to companyName, it shows up in portal back again.

I followed the same steps as you have described.

 

Edited by dav1089
Link to comment
Share on other sites

Check the data in the parent and child table used for that portal.

 

You will probably find that CompanyName matches on both sides, but the CompanyID_pk and/or the CompanyID_fk do not match/are blank on one side...

Link to comment
Share on other sites

Hello @webko , 

Yes, you are right.

But that's why I calculated fk based on company_ID pk

Do I have to use companyName as pk and fk from now on?

I think thats not bad idea because company name will be always unique

Edited by dav1089
added more description
Link to comment
Share on other sites

You cannot *guarantee* that a company name will be unique.

You can that a serial number is...

I don't know exactly what data you have at the moment, but you can probably use the Company Name as a match field as a one-off process to make the proper keys match on both sides. 

Assuming your Company table has the correct PrimaryKey and the Contacts table is missing matching ForeignKeys, you can import from the Company table to the Contacts table, Update Matching on CompanyName and importing the PrimaryKey to the Contacts ForeignKey

Link to comment
Share on other sites

14 hours ago, dav1089 said:

But that's why I calculated fk based on company_ID pk

That doesn't sound right. The foreign key needs to be populated once (while the relationship is still based on the company name). If you make it a calculation field, it will lose its value after you switch the relationship to use company ID. Not to mention that an unstored calculation field cannot be used as a foreign key at all. Make the field a Number field (or Text field) and populate it using the Replace Field Contents command, as explained in point #6 above.

 

13 hours ago, webko said:

You cannot *guarantee* that a company name will be unique.

Actually, you can (and perhaps should), by validating it as unique. But that doesn't make it suitable to serve as the matchfield: a company's name can change - sometimes by a trivial event such as "Oh, that's not how you spell Gobbledygook, Ltd.".

  • Like 1
Link to comment
Share on other sites

This topic is 2356 days old. Please don't post here. Open a new topic instead.

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now
 Share

×
×
  • Create New...

Important Information

By using this site, you agree to our Terms of Use.