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!

 

 

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 ...

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 January 7, 201610 yr by dav1089

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?

Source company table does not have companyID but target has, both tables have some similar company names(duplicates).

Contacts table is totally empty.

 

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 January 7, 201610 yr by comment

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.  :-)

That was really Helpful

THank you guys 

Another question is : I have made these changes, so can I show related contacts using slider on company table based layout? or I have to use portal?

You want to use a portal. Slide controls are not designed to show related records.

You can, however, put a portal on a slide control or a popover.  :-)

Alright.. So that's where I was making mistake... I had slide control and on each slide I was putting one contact information related to that company

@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 January 11, 20169 yr by dav1089

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...

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 January 11, 20169 yr by dav1089
added more description

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

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.".

@comment Ohh! I see what you are saying.. It worked perfectly and have imported contacts into Portal Finally with pk-fk relationship..

Thank you all for the help again..