none
BCM Import Accounts related to contacts

    Question

  • I have a database of Customers with one-to-many relationship to Contacts.  Using Import feature of BCM 2007, I'd like to import customers into Accounts and contacts into business contacts while maintaining the relationship.  I don't see an account id field when importing business contacts.  How you I do this?

    TIA,

    Josh

    Friday, December 17, 2010 7:17 PM

All replies

  • Hi Josh

    The only way this can be done in Business Contact Manager 2007 is to use a script (BCM 2010 allows linking via the GUI).   In order for the script to work, the Company Name associated with a Business Contact Must match the Account Name.  For example, if you have the following Business Contact:
    First Name: John
    Last Name: Doe
    Company: ACME

    Then you must have an Account Named 'ACME'

    In order for the Macro to succeed, the Company Name AND the Account Name must match. This includes both Spaces, punctuation marks and Case.
    In addition, when Importing Data into the Accounts Folder in Business Contact Manager, you will need to Map the Company Name to the Account Name Field.
    What you will need to do is import and map the data into BCM, then run the Macro. I would also recommend importing the data into a test/empty BCM Database.

    If you have some programming specific questions, you might want to post them in the Outlook Dev Forum
    http://social.msdn.microsoft.com/Forums/en-US/outlookdev

    For some general information on Outlook Macros, please take a look at the following link
    http://msdn.microsoft.com/en-us/library/ee814736.aspx

    Hope this helps!

    Larry - MSFT

     

    ++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
    Microsoft provides programming examples for illustration only, without warranty
    either expressed or implied, including, but not limited to, the implied warranties
    of merchantability and/or fitness for a particular purpose.
    This article assumes that you are familiar with the programming language being
    demonstrated and the tools used to create and debug procedures. Microsoft support
    professionals can help explain the functionality of a particular procedure, but
    they will not modify these examples to provide added functionality or construct
    procedures to meet your specific needs.
    ++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++


    NOTE: Depending on the number of records to be linked, the linking process might
    take a substantial period of time.

    Sub Link_BusinessContacts()

    Dim olApp As Outlook.Application
    Dim objNS As Outlook.NameSpace
    Dim olFolders As Outlook.Folders
    Dim bcmRootFolder As Outlook.Folder
    Dim bcmAccountsFldr As Outlook.Folder
    Dim bcmContactsFldr As Outlook.Folder
    Dim newAcct As Outlook.ContactItem
    Dim newContact1 As Outlook.ContactItem
    Dim userProp As Outlook.UserProperty

    Set olApp = CreateObject("Outlook.Application")
    Set objNS = olApp.GetNamespace("MAPI")
    Set olFolders = objNS.Session.Folders
    Set bcmRootFolder = olFolders("Business Contact Manager")
    Set bcmAccountsFldr = bcmRootFolder.Folders("Accounts")
    Set newAcct = bcmAccountsFldr.Items.Add("IPM.Contact.BCM.Account")
    Set bcmContactsFldr = bcmRootFolder.Folders("Business Contacts")


    'set the code to continue even if there are errors
    On Error Resume Next

    'Count the number of Contacts in the Business Contacts Folder
    num_contacts = bcmContactsFldr.Items.Count

    'Loop through all of the Business Contacts
    For i = 1 To num_contacts

    'Set the Name of the Company Field (if there is one) to a variable
    ContactCompany = bcmContactsFldr.Items.Item(i).CompanyName

    'Check to see if there is a Company for the Contact by checking its length
    If Len(ContactCompany) > 0 Then

    'If there is a Company Name Search to see if there is an Account with that
    Name.
    'The additional Aprostrophes are required to search for company names
    containing punctuation marks and Numbers
    Set newAcct = bcmAccountsFldr.Items.Find("[FileAs] = " & "'" &
    ContactCompany & "'" & "")

    'Link the Business Contact to the Account
    Set newContact1 = bcmContactsFldr.Items.Item(i)
    If Len(newContact1.UserProperties("Parent Entity EntryID")) = 0
    Then
    Set userProp = newContact1.UserProperties.Add("Parent Entity
    EntryID", olText, False, False)
    userProp.Value = newAcct.EntryID
    newContact1.Save
    End If
    Else
    End If

    Next i

    'Let the user now the data has been linked
    msgbox "Data Linked Successfully"

    End Sub



    ADDITIONAL INFO
    ==================
    Business Contact Manager for Outlook Developer Guide
    http://msdn.microsoft.com/en-us/library/aa431857.aspx

    Wednesday, December 22, 2010 5:25 PM
  • Larry, Thanks for your reply.

    I understand the process.  If I import a new field such as CustomerID, which is present in both Accounts and BusContacts and is also unique then I believe I can eliminate the need for unique Company/AccountName by altering the script to use the new CustomerID field.

    Like this:

    <snip>

    'Loop through all of the Business Contacts
    For i = 1 To num_contacts

    'Set the CustomerID to a variable
    ContactCustomerID = bcmContactsFldr.Items.Item(i).CustomerID

    'Check to see if there is a CustomerID for the Contact by checking its length
    If Len(ContactCustomerID) > 0 Then

    'If there is a CustomerID Search to see if there is an Account with that CustomerID.
    'The additional Aprostrophes are required to search for company names
    containing punctuation marks and Numbers

    'apostrophies may not be required for searching a numeric id

    Set newAcct = bcmAccountsFldr.Items.Find("[CustomerID] = " & "'" &
    ContactCustomerID & "'" & "")

    </snip>

    Does that sound feasible?

    Thanks,

    Josh

    Wednesday, December 22, 2010 5:43 PM
  • Hi Josh

    I have not tried it - but one thing to remember is that - from a design and Interface perspective -  BCM and Accounts will only link via the Company Name. Were you trying to link them based on something besides an Account/Company Name?

    Larry - MSFT

    Wednesday, December 22, 2010 11:28 PM
  • Thanks Larry.  I was not aware that the Account Name/Company was used for the relationship in the database & UI.  The code looks like there is a required field in BusContacts called 'Parent Entity EntryID' which is being set to the EntryID field of Accounts.

    Wouldn't this be the relationship that drives UI?

    <snip>

    Set userProp = newContact1.UserProperties.Add("Parent Entity
    EntryID", olText, False, False)
    userProp.Value = newAcct.EntryID

    </snip>

    I tested and am able to have two accounts with same name and relate BusContacts separately to each on the Account Edit Form.  This means the account name is not unique and perhaps not a concern for linking(?)  Are there other places that the design is to use name fields?

    Josh

    Wednesday, December 22, 2010 11:59 PM
  • Hi Josh

    You are correct - the underlying database does use numerous GUID's and Entity links in addition to Account Name/Company to manage links (not only between Accounts and Contacts but also between other BCM Components/features).
    Are you attempting to link BCM Items in a different manner than Account Name to Company?
    Did the sample resolve the initial linking problem? 

    Thanks!

    Larry -MSFT

    Tuesday, December 28, 2010 12:47 AM
  • Hi.

    I'm very new to VBA programming.  The code doesn't seem to work for bcm 2010.  It stops at: bcmRootFolder.Folders("Accounts")
    Have the names of the folders changed?  Do you have by any change a updated macro for bcm 2010?

    Many thanks,

    Oddur.

    Tuesday, January 18, 2011 12:13 PM
  • I have the same issue with trying to import data from Goldmine.  Did anyone have a solution to importing Account and Business Contacts separately, yet maintain the links(as established by Goldmine) and recreate them to the BCM Account???

    I have approx 19,000 records to import and need help.  I'm assuming a simple import and export will not do.  

    Thank you,

    Greg


    RemaxGreg

    Thursday, June 21, 2012 1:32 AM
  • Hi Greg,

    You can import your contacts and accounts from goldmine and if the company names are unique, you can link them in BCM using the code in this thread. 

    If company names are not unique then you can import a unique accountid from goldmine into bcm for both accounts and contacts.  Then you can use code similar to that in this thread to link them.  The altered code would search for BCM account using the ID from goldmine like so:

    Set newAcct = bcmAccountsFldr.Items.Find("[GoldmineID] = " & "'" &
    ContactCustomerID & "'" & "")

    HTH,

    Josh

    Thursday, June 21, 2012 2:45 AM
  • Thanks Josh.  I have a couple more questions to make sure I do the right thing.  Company names are not unique.

    First, being a novice.  I assume I'm importing all my contacts and account before I run your code.  How do I insert and run ( and then remove?) the code in BCM?  Specific details would be most helpful.  Being cheap, is this anything that free MS Support would endeavor for me with a remote session??

    Second, here are a few other questions:

    1.  Import Accounts. 

    a.  Does it matter whether I import Accounts or Business contacts first??

    b.  Shouldn't the field "Account Number" be used to hold the "unique accountid", or do I create a custom field?  And how can my future entries into BCM be assigned unique numbers automatically?  Does BCM  mysteriously hide some "unique accountid" somewhere, just as Goldmine did.  I assume that every account and contact is assigned unique id's of some sort automatically by BCM, at a minimum to enable syncing.  Goldmine used AcctNo and RecID to tie all its accounts and contacts together.   Where can I find more info on this, as it is critical not only to my import but all future data entry??

    c.  I assume I should leave the "Primary Contact" field blank on the import.  If I'm relying on the code you've developed to establish Contact to Acct links, will it also create the Primary Contact link, or just a plain link (meaning I'd have to manually establish the Primary Contact)?  What would happen if I put in a name for the Primary Contact??

    2.  Import Business Contacts

    a.  I imagine the Goldmine "RecID" again is the unique identifier that I'm looking for a similar match in BCM.  What field is to be used to hold the "unique contactid", or do I create a custom field for each contact?  BCM says it uses names, but how can names be relied on to keep records unique.  I know a few John Smiths.  Again, how can my future entries into BCM be assigned unique numbers automatically?  Does BCM  mysteriously hide some "unique contactid" somewhere, just as Goldmine did. 

    b.  I assume I should leave the  "Parent Account Name" field blank on the import.  Or should I since I could easily import the Goldmine AcctNo field?  If I'm relying on the code you've developed to establish Contact to Acct links, will it duplicate the Parent Acct?

    Again, will I have to manually establish the Primary Contact, or is there a better way?  What would happen if I put in an entry into the Parent Account Name??

    I feel so lost, and can't believe this issue is not fully addressed somewhere in a step by step fashion by BCM.

    Thanks for your help!!

     


    RemaxGreg

    Thursday, June 21, 2012 2:12 PM
  • Hi Greg,

    These are good questions.  I'll try to answer the best I can, but start by saying unless you have a lot of time and some coding skills, be prepared for a less than perfect outcome.  I know I won't be able to give you all the detailed answers you may need.  I'd say a realistic expectation would be to have your accounts and contacts imported and linked together.  That's it.  Importing history or documents is a feat.  If you've been using Outlook already for emailing your goldmine contacts, then you may get some good results linking email history to your contacts.  You will likely do a few passes at the import process, deleting imported records from BCM in between, to fine tune the results...

    Here goes:

    Import Accounts first so you can try your luck at using the auto-linking of contacts to accounts.  If you have BCM 2010 you can elect to have the import automatically link C to A using Company & AccountName Fields.  This might be good approach to get you 90% done if for example there are very few instances of duplicate company names.  Or you could rename compaies to a unique name in gold mine first.  It's truly a shame tha MS did not add the ability to choose a field to do the linking.

     BCM has hidden fields that maintain the relationships between entities.  You will only need your goldmine id if you need to use code to do the linking.  The code finds the related account using the goldmine id then sets up the link using BCM relationships.  After the entities are linked, the goldmine ids are not used by BCM they are only for your reference.  No need to worry about furture data entry.  After golmine is imported BCM will handle linking for new entries.

    Primary contact is a relationship in BCM.  For new entries in BCM you select from a list of  contacts related to the account and BCM database saves the unique id of the primary contact in the relationship field.  This means you cannot simply put the name in there and have the same linking.  You'd have to use a similar approach as this code to look for the contact using an unique field and then set the related entity id.  It makes this a lot more complicated for novice but it can be done.  Same thing for salesperson if you were to setup salespeople as contacts in BCM and then have a 'relationship' user defined field to save the salesperson on the account.  These are outside the scope of this discussion.  The best thing for novice would be to set a text field to the primary contact name.  At least then your users can read the primary name on the account.

    The BCM import wizard helps you map fields between your goldmaine export file and BCM.  You can also 'Map to New Field' which adds a userproperty to BCM and puts your data in that field.  Notice the option to 'Link Business Contact to Account if Company Field Matches AccountName.'

     

    The import will allow you to put a value in Parent Account Name field of contact record, but that would not establish a relationship between account and contact.  It would show the account name on the contact form, but not the contact record in the list of related contacts of account form.  So the info would be helpful to users, but not fully powerful to BCM system.

    The short answer is:

     Do your best to make goldmine company names unique.

    Include those names in both your account and contact export files. 

    Also include priomary contact name in account export. 

    Import account file first making sure to put company name in account name and primary contact name in that field while mapping. 

    Save the map cuz you'll do this import several times to get it right. 

    Then import contacts making sure to put company name in company field and try the 'link business contact to account...'

    If that result isn't usable but you want to try again then delete the imported records from outlook.  (you may want to start with a subset of your 19k records to save time until your ready for the final import).  If the results are no good then explore this code further or hire someone who knows vba to do the import for you.  MS will not help on this.

    ...not such a short answer..I know...sorry.

    Josh


    • Edited by joshbooker Thursday, June 21, 2012 3:47 PM
    Thursday, June 21, 2012 3:37 PM
  • THANK YOU VERY MUCH for your help again. 

    Ouch from Microsoft!  I was wondering how they kept track of each record and account uniquely, and could never find out.  My bad for not learning about this sooner.  The fact these fields are hidden (ie never exportable in case I ever want to switch to a different program? or are they exportable using some wizardry??) is very problematic!!  I appreciate Goldmine much more knowing they built in the capability to export their hidden Acct Num and Record ID fields.  I have to decide if I really want to go through the BCM import difficulties or use a different program for my CRM.   I can't believe I am the only business user who has ever had these questions or needs.  And that a simple solution hasn't been developed by Microsoft sooner.   I should never have to lose any of the time consuming connections between my account and contacts.   If you can circulate this to any of the MS engineers, I'd appreciate it.


    RemaxGreg

    Monday, June 25, 2012 11:10 PM
  • Greg,

    You're welcome.  BCM is a nice crm-like addition to Outlook for free.  The IDs can perhaps be exported and certainly can be exported using code or direct SQL Server access to the tables.  For what it's worth, BCM data is upgradable to MS CRM.  MS CRM Online is a nice, rather affordable offering.

    Josh

    Monday, June 25, 2012 11:38 PM