Stephen Murphy Posted October 15, 2017 Posted October 15, 2017 Hello All I have an excel spreadsheet with contact names, company names , email, phone etc. I imported the company details email & phone to the Accounts module and I imported the contact names & associated account name to the Contacts module How do I "relookup" the account names so that the contact is assigned to the Account in the accounts module? Regards Stephen Murphy Ireland
Steve Martino Posted October 15, 2017 Posted October 15, 2017 You appear to be importing into 2 different tables and want to connect them through a relationship. If you don't have keys set up in the spreadsheet to connect Accounts to Contacts, they wont be related. You can either do it manually if it's not alot of records, or create a simple database, and drop the excel spreadsheet onto FM and import it as a flat file. Then break out either the accounts, or contacts to a related table, with proper foreign and primary keys. Then you can import that into your database. You'll want to not 'Perform auto enter' operations. You may also have to go into your original database and make sure you can import the new primary keys. Also make sure each value is unique, or the whole thing goes down the tubes. I would also add a field in your temp DB for creation date, so if things don't work out, after you import, and modify the found set (imported records), you can find those newer records and delete them to start over. I'm probably making it sound more difficult than it actually is.
Stephen Murphy Posted October 15, 2017 Author Posted October 15, 2017 Hi Steve Thanks for this. Just to clarify, I'm using FM Starting Point so the Contacts module and Accounts Module are already linked? There are over 1,700 entries so manual linking each contact to the respective Account would be an ordeal So ..I have manually entered (imported) the Company name (Account) into the contacts module and the Accounts module .....instead of clicking the magnifying glass and manually assigning each contact to company name (Account) entry I'm hoping there is a way to assign each contact to the account using a script or relookup ????
Steve Martino Posted October 15, 2017 Posted October 15, 2017 3 hours ago, Stephen Murphy said: Just to clarify, I'm using FM Starting Point so the Contacts module and Accounts Module are already linked? They are probably linked with a Primary Key---<Foreign Key setup. How are they 'linked' on the spreadsheet? Is it just by account name? If it's linked by Account Name on the spreadsheet then what I think I would do is to make a database with 2 tables and import the contacts into a Contacts Table, and the Account info into an Account table and link the two tables by account name. Then they are linked. I would next add, depending on which table holds the child record, a foreign key and populate that with a calculation that inserts the primary key from the parent table. Now you can switch the relationship to Primary-Foreign and make sure everything still matches. At that point, you can import those records, Parent table, then Child Table like I mentioned earlier. If you import the primary key and not let it auto-enter, from the parent table, making sure it's unique, and the foreign key from the child table, those records will now be connected via the FMSP relationship. Can you show a mockup of the spreadsheet with some data, and where you want it to land in the database? I do have the latest copy of FM Starting Point, so if someone from there doesn't chime it tomorrow, I'll have a look based on any follow up info you can provide. If you can also let me (us) know what 2 tables or table occurrences you are working with in FMSP, it will make it easier for me to get to it.
Jonathan Hogle Posted October 17, 2017 Posted October 17, 2017 Stephen, Unfortunately, Steve is correct. Without Account ID's, the Contacts you imported will have to be manually attached. If you have the account ID's in the spreadsheet already, it's just a matter of setting them into the "ID_Account" field in Contacts during your data import. Thanks, Jonathan Hogle
Stephen Murphy Posted October 21, 2017 Author Posted October 21, 2017 Thanks Steve & Jonathan ....i understand now.
Recommended Posts
This topic is 2849 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