locked
CRM 2011 accounts and contacts not linking together RRS feed

  • Question

  • Hey guys, i have uploaded my accounts and contacts files as .zip, .xml separately, .csv seperately and zipped and nothing works. some of the files linked together on the zipped xml files but even with that I cannot get the information to link together perfectly. about 30% of the names are linking to the accounts and even though its saying that its been successful they are not linked together when I go in to check it.

    Anyone else experiencing problems like this?

    My data is from a full export from Salesforce.com. because of problems with the data I am only uploading the accounts and contact information nothing more.


    thebuggermom

    Monday, January 28, 2013 9:43 AM

Answers

  • I don't know the specifics of the structure of exported SF data, but in general terms you are going to want two csv files that look like this (plus lots of other 'real' fields, of course):

    Contacts

    Contact ID    First Name   Last Name   Account ID   Street1 etc

    C0001          John             Doe              A0001          1 High Street, some city (works at head office)

    C0002          Jane             Doe              A0001          2 Another Lane, some other city (works for same company as John, but in another office)

    C0003          Alice             Smith            A0002          7 Some Avenue, any city

    Accounts

    Account ID    Company Name   Primary Contact ID  Street1 etc

    A0001           Acme Plc              C0001                     1 High Street...

    A0002           Widget Inc          C0003                      7 Some Avenue...

    You would need to add a custom field on Contact to hold the ID (which you may want to put in the footer of the form to be able to see it for data troubleshooting). You can also add a custom field for Account ID, which I would recommend so you can keep this intact for later checking, or use an existing field such as Account Number if not being used already. Notice the company name is not mentioned in the Contact file, nor the primary contact name in the Account file.

    Zip the two files together (use csv files ideally, that way you will tend to get more records under the filesize limit than with xml). Import as one job. When you set up the field mapping for Contacts, you map the "Account ID" as the "Parent Customer" then use the tools in the GUI to tell it you are mapping this to an Account based on the field "SF_AccountID" (or whatever field you use). Likewise when you import Accounts, map "Primary Contact ID"  to "Primary Contact" and tell it to look this up based on "SF_ContactID".

    Lookups based purely on names are usually prone to errors because they are non-unique or not accurate enough (Acme Plc != Acme Plc. != Acme).

    Importing one file after the other will not link them both ways. You could import all accounts, then import all contacts with the link to parent account, and include as extra field "IsPrimaryContact". Use advanced find and an on demand workflow to find these contacts and update their parent account record to link back to the contact as primary.


    Hope this helps.
    Adam Vero, Microsoft Certified Trainer | Microsoft Community Contributor 2011
    Blog: Getting IT Right

    • Marked as answer by thebuggermom Monday, January 28, 2013 4:07 PM
    • Unmarked as answer by thebuggermom Monday, January 28, 2013 4:07 PM
    • Marked as answer by thebuggermom Monday, January 28, 2013 5:01 PM
    Monday, January 28, 2013 1:14 PM
  • Yes, you can do this from the "Imports" area to remove all records created during a particular import job, without having to go and delete records of different types using bulk edit by hand.

    Hope this helps.
    Adam Vero, Microsoft Certified Trainer | Microsoft Community Contributor 2011
    Blog: Getting IT Right

    • Marked as answer by thebuggermom Monday, January 28, 2013 5:01 PM
    Monday, January 28, 2013 4:10 PM

All replies

  • Are you using a unique field to link Contacts to the parent (eg an exported Account ID field), or are you relying on Company Name being a) unique and b) correctly spelled and formatted in every case?

    Hope this helps.
    Adam Vero, Microsoft Certified Trainer | Microsoft Community Contributor 2011
    Blog: Getting IT Right

    Monday, January 28, 2013 12:12 PM
  • Hey adam,

    long story.....

    first i tried to upload the Salesforce file as a zipped file all together. This came back with almost all failing. these would have been most likely linked by the SFDC_ID code in both. At the time I had not realised that they were under different headings in accounts and contacts ie the same code was under SFDC_ID in Accounts and AccountID in contacts. 

    Then I tried to edit the information so that I would have the contact names in accounts and accounts in the contact fields. This kind of worked (about 50%) but it was very time consuming as each had to be done individually. 

    Next i tried uploading the accounts and contacts as seperate .csv files but because of the amount of accounts and contacts in each I had to upload them in batches between 500 and 1000. Still no luck. With this method everything went up successfully (except for any accounts that I had not deleted off the system) but they were not linked.

    I am at my wits end with this at the moment. Been at it for months literally. tried ringing the MS helpline but they dont really answer my question and just send me in circles where i find some information but it is not relevant until I get the information up 100% in the first place.


    thebuggermom

    Monday, January 28, 2013 12:57 PM
  • I don't know the specifics of the structure of exported SF data, but in general terms you are going to want two csv files that look like this (plus lots of other 'real' fields, of course):

    Contacts

    Contact ID    First Name   Last Name   Account ID   Street1 etc

    C0001          John             Doe              A0001          1 High Street, some city (works at head office)

    C0002          Jane             Doe              A0001          2 Another Lane, some other city (works for same company as John, but in another office)

    C0003          Alice             Smith            A0002          7 Some Avenue, any city

    Accounts

    Account ID    Company Name   Primary Contact ID  Street1 etc

    A0001           Acme Plc              C0001                     1 High Street...

    A0002           Widget Inc          C0003                      7 Some Avenue...

    You would need to add a custom field on Contact to hold the ID (which you may want to put in the footer of the form to be able to see it for data troubleshooting). You can also add a custom field for Account ID, which I would recommend so you can keep this intact for later checking, or use an existing field such as Account Number if not being used already. Notice the company name is not mentioned in the Contact file, nor the primary contact name in the Account file.

    Zip the two files together (use csv files ideally, that way you will tend to get more records under the filesize limit than with xml). Import as one job. When you set up the field mapping for Contacts, you map the "Account ID" as the "Parent Customer" then use the tools in the GUI to tell it you are mapping this to an Account based on the field "SF_AccountID" (or whatever field you use). Likewise when you import Accounts, map "Primary Contact ID"  to "Primary Contact" and tell it to look this up based on "SF_ContactID".

    Lookups based purely on names are usually prone to errors because they are non-unique or not accurate enough (Acme Plc != Acme Plc. != Acme).

    Importing one file after the other will not link them both ways. You could import all accounts, then import all contacts with the link to parent account, and include as extra field "IsPrimaryContact". Use advanced find and an on demand workflow to find these contacts and update their parent account record to link back to the contact as primary.


    Hope this helps.
    Adam Vero, Microsoft Certified Trainer | Microsoft Community Contributor 2011
    Blog: Getting IT Right

    • Marked as answer by thebuggermom Monday, January 28, 2013 4:07 PM
    • Unmarked as answer by thebuggermom Monday, January 28, 2013 4:07 PM
    • Marked as answer by thebuggermom Monday, January 28, 2013 5:01 PM
    Monday, January 28, 2013 1:14 PM
  • Hello,

    As for me the best way is to use SSIS Integration toolkit to import data to CRM from any source (it is free for tasks that you have at the moment). Check following url -

    http://www.kingswaysoft.com/products/ssis-integration-toolkit-for-microsoft-dynamics-crm


    Freelance Developer for Dynamics CRM 4.0/2011

    Monday, January 28, 2013 1:42 PM
    Moderator
  • thats a great help adam i will definitely try this and i will get back to you. I will have to wipe the system of the files that I have already uploaded as well wont I?


    thebuggermom

    Monday, January 28, 2013 4:08 PM
  • Yes, you can do this from the "Imports" area to remove all records created during a particular import job, without having to go and delete records of different types using bulk edit by hand.

    Hope this helps.
    Adam Vero, Microsoft Certified Trainer | Microsoft Community Contributor 2011
    Blog: Getting IT Right

    • Marked as answer by thebuggermom Monday, January 28, 2013 5:01 PM
    Monday, January 28, 2013 4:10 PM
  • not too bad so. Just one more quick question.

    I went back onto the original Salesforce files and, i know you said that you were unfamiliar with them but, there are three different ID columns in each.

    Accounts: ID, SFDC_ID, and ParentID

    Contacts: ID, SFDC_ID, and Account ID

    I have noticed that the ID and SFDC_ID are the same but the ParentID and AcccountID are different and many of the have 0000000000000AAA as that value. 

    This is where it gets confusing for me.

    I have tried uploading the files as zipped .csv files as well and the file comes up as having corrupt fields in it. I have used the original file importing from Salesforce and this has not come up before, and I have not tampered with the original files except for copying the information to another excel sheet.


    thebuggermom

    Monday, January 28, 2013 4:19 PM
  • ok i think i figured out the problem but now i dont know what to do to resolve it.

    I was looking in the csv files that i have as you said to edit the columns for Contact ID and Account ID. No problem there in terms of Contacts I have an ID code to link them to the accounts they belong to.

    However when I go into the Accounts file there is no column there for the contact ID codes. all that is there is the Parent ID code and that only links it to another account on the same excel sheet. 

    In other words i have a lookup field for the contacts to find their accounts but nothing that works the other way. Could this be why the information is sucessfully being imported but not linked together. And if so, is there an easy way for me to link the codes together on the accounts excel sheet without having to do them one by one?


    thebuggermom

    Monday, January 28, 2013 4:48 PM
  • The only essential link between Contacts and Accounts would be the "Parent Customer" link to the Account ID.

    You only really need to care about a link from Account to Contact ID if you want to identify which of several Contacts is the "primary" contact for an Account. Do you have a means of figuring out which Contact is the "primary" one? Is there perhaps a column for "is primary contact" or somesuch? If not, then you won't be able to link them in this direction, but that should not stop you linking Contacts to their parent Account, and Accounts to their parent (owning) Account too.

    These relationships are independent - you could theoretically link a Contact as the primary contact for an Account who does not even work for the company - for example if communications should go first to their advocate, or external consultant who acts on their behalf. This Contact could be the primary for several Accounts they represent, yet their own parent Account would be the company that actually cuts their pay cheque.


    Hope this helps.
    Adam Vero, Microsoft Certified Trainer | Microsoft Community Contributor 2011
    Blog: Getting IT Right

    Monday, January 28, 2013 4:53 PM
  • ok i understand what you are saying there but in terms of the example that you gave earlier I would need at least a primary contact field in accounts in order to link at least one of them to it? 

    then the problem lies that if I import them with no contact id reference to field all contacts related to an account, how would I link them after that?

    Just want to say you are being really helpful and I am exceptionally grateful as you are answering many of my questions! Also I am sorry if this is pestering you but as you have probably noticed I am a complete newbie to this and I really haven't a clue about CRM programmes at all

    Thanks again Adam!


    thebuggermom

    Monday, January 28, 2013 5:06 PM
  • You can link 1, 10 or 1000 Contacts to an Account, simply by each Contact having their "Parent Customer" set to that Account. In the GUI you would do this by filling in the company name, for data import purposes it is more useful to set this by some unique ID.

    For that 1 Account, you can also identify which of the 1000 Contacts is the primary, by setting "Primary Contact" to a single Contact record. Again, in the GUI you would typically type the person's name, when importing an ID is less likely to fail (because of two John Doe's, say). You do not have to set the primary contact at all if you don't want to, or it does not map to your business processes - perhaps the "primary" looks like it should be the most senior, but on a day to day basis the person you speak to most is someone else entirely. Which to choose?

    If it not useful, don't use it. If you can't accurately identify who the primary contact is, don't use it. All 1000 contacts will still be linked to the Account, because you filled in the "Parent Customer" field. (which you might choose to rename "Parent Account" if that is more meaningful for your scenario).

    Some tips to help get your head round this - a lookup field does just that - it looks UP from one child record (of possibly many) to the parent record.

    Another way to remember which way round your links are in a lookup field, especially why this is important to get right when you import data is that a lookup field answers the question - "who's your daddy?" (I bet you won't forget it now!).

    Taking the business context away for a moment - for any child, it is a reasonable question to ask "who is your mother?" since all children have a mother. Several children may have the same mother. Ask the mother "which is our favourite child?" and the answer is only obvious for someone with only one child - for everyone else it is a moral dilemma*. So it is with contacts in a B2B company - every contact should be related to the company they work for, but it may not be possible to identify which is the primary contact in every case.

    * the moral dilemma is not how to choose which child is your favourite of course, but whether to answer the question honestly!


    Hope this helps.
    Adam Vero, Microsoft Certified Trainer | Microsoft Community Contributor 2011
    Blog: Getting IT Right

    Monday, January 28, 2013 5:20 PM
  • when I am uploading the files then, should I delete the Contact ID code in the Contacts excel file and just leave the Account ID column, and with the Accounts file simply keep the Account ID and the Parent ID because if there is no column for contact ID in the accounts file is it not irrelevant and taking up memory in the file?  

    from what I understand anyway, (I could be wrong), the contacts should still link to the Accounts as just contacts and I can select who to be a primary contact later on? 

    ps very good way of putting it I will defintely not forget that explanation any time soon :D )


    thebuggermom

    Monday, January 28, 2013 5:32 PM
  • Exactly. You could either remove the columns if file size is a real issue, or just tell the field mapping to ignore them.

    That's what us trainers do - find different ways to get people to remember things, even if it needs a bit of humour to do so!


    Hope this helps.
    Adam Vero, Microsoft Certified Trainer | Microsoft Community Contributor 2011
    Blog: Getting IT Right

    Monday, January 28, 2013 5:39 PM
  • i just deleted the columns that i do not need the ones that really only meant anything in the Salesforce programme. However yet another new obstacle has arisen. I am getting this error which never came up before with the original zip files from salesforce - "Error in unzip. The .zip or .cab file could not be uploaded because the file is corrupted or does not contain valid importable files."

    I found some help on this that said to use =clean(N2) on the mailing column (very basic explanation there is was a lot longer) tried that but it still comes up with the sames error. 

    Any ideas why this might be happening now when it never happened before with the exact same file?

    Also, you said that you were a trainer. I am probably going out on the limb here but by any chance do you know of any trainers in Ireland that I would be able to get either classroom help or on site help with this? I have been looking for courses on CRM but all the ones that I find require some level of competency in the field! 

    Humour is the best way to learn i think though!! its only truly funny things that really stick in your head anyway!! Brilliantly put though!! :D 


    thebuggermom

    Tuesday, January 29, 2013 9:50 AM
  • my best guess is that the problem lies in the accounts file as I just tried to upload them seperately and the Contacts go through the import wizard no problem but the accounts are stopped when I try to upload the file at the beginning! 


    thebuggermom

    Tuesday, January 29, 2013 9:53 AM
  • You could try reaching out to http://www.pedroinnecco.com/ who is a CRM MCT based in Ireland

    If CRM does not like your files it is most likely to be non-printable characters, which the CLEAN function in Excel may help with. You may also want to use TRIM which removes leading and trailing spaces and turns double spaces into single. I have heard of issues with imnports with trailing spaces (whereas the GUI normally strips these).

    After using CLEAN and TRIM, make sure you copy and paste special just values and formats back over the data (or into a new sheet). You need the end result values of the formulae, not the formulae themselevs.


    Hope this helps.
    Adam Vero, Microsoft Certified Trainer | Microsoft Community Contributor 2011
    Blog: Getting IT Right

    Tuesday, January 29, 2013 4:48 PM
  • ok I am going to sound really bad here but how can I implement the clean and trim functions to do all columns together or is that possible?

    I have never had any cause to use these functions in the past as I have never had a problem uploading an excel file to anything before. :/ 

    I have also contacted Pedro he is being really helpful too thanks for giving me his contact details. Turns out he does courses in a college not to far from me so I am waiting to hear details of the next arranged class times!! :D 


    thebuggermom

    Wednesday, January 30, 2013 9:44 AM