dav1089 Posted January 7, 2016 Share Posted January 7, 2016 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 More sharing options...
comment Posted January 7, 2016 Share Posted January 7, 2016 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 More sharing options...
dav1089 Posted January 7, 2016 Author Share Posted January 7, 2016 (edited) 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, 2016 by dav1089 Link to comment Share on other sites More sharing options...
comment Posted January 7, 2016 Share Posted January 7, 2016 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 More sharing options...
dav1089 Posted January 7, 2016 Author Share Posted January 7, 2016 Source company table does not have companyID but target has, both tables have some similar company names(duplicates). Contacts table is totally empty. Link to comment Share on other sites More sharing options...
comment Posted January 7, 2016 Share Posted January 7, 2016 (edited) 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, 2016 by comment 1 Link to comment Share on other sites More sharing options...
LaRetta Posted January 8, 2016 Share Posted January 8, 2016 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. :-) 1 Link to comment Share on other sites More sharing options...
dav1089 Posted January 8, 2016 Author Share Posted January 8, 2016 That was really Helpful THank you guys Link to comment Share on other sites More sharing options...
dav1089 Posted January 8, 2016 Author Share Posted January 8, 2016 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? Link to comment Share on other sites More sharing options...
comment Posted January 8, 2016 Share Posted January 8, 2016 You want to use a portal. Slide controls are not designed to show related records. Link to comment Share on other sites More sharing options...
LaRetta Posted January 9, 2016 Share Posted January 9, 2016 You can, however, put a portal on a slide control or a popover. :-) Link to comment Share on other sites More sharing options...
dav1089 Posted January 11, 2016 Author Share Posted January 11, 2016 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 Link to comment Share on other sites More sharing options...
dav1089 Posted January 11, 2016 Author Share Posted January 11, 2016 (edited) @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, 2016 by dav1089 Link to comment Share on other sites More sharing options...
webko Posted January 11, 2016 Share Posted January 11, 2016 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 More sharing options...
dav1089 Posted January 11, 2016 Author Share Posted January 11, 2016 (edited) 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, 2016 by dav1089 added more description Link to comment Share on other sites More sharing options...
webko Posted January 11, 2016 Share Posted January 11, 2016 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 More sharing options...
comment Posted January 12, 2016 Share Posted January 12, 2016 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.". 1 Link to comment Share on other sites More sharing options...
dav1089 Posted January 12, 2016 Author Share Posted January 12, 2016 @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.. Link to comment Share on other sites More sharing options...
Recommended Posts
This topic is 3019 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 accountSign in
Already have an account? Sign in here.
Sign In Now