Mark said:
Hi
I amin the process of importing 1000 Accounts and 3000 Contacts into
Business Contact Manager and I have noticed that the contacts are not linked
to the accounts during the import process.
I understand that BCM does not provide for this and I will have to link them
manually. I was however hoping that perhaps someone knew of a workaround or a
SQL query that could automate this process.
I don't believe there is any way built-in. I've just done this very job
(I hope!) using MS Access to run a few queries on the BCM database. I'm
still trying to track down a schema for BCM, as there's a certain amount
of risk in playing with a database you don't fully understand, and this
is a complex one. I don't really have time to piece it together myself.
Everything that follows is completely without any kind of warranty, has
no connection whatever with Microsoft, and is used at your own risk...
As far as I can see, there are two links required. The ParentEntryID of
the contact must be set to the EntryGUID of the Account, and the
ParentContactServiceID must be set to the ContactServiceID respectively.
The ServiceID appears to be what the Account entry of the contact uses,
while the list of contacts for an account seems to be drawn from the
EntryGUID. I'd experimented a bit, and having found that the ServiceID
linked the tables, I didn't go any further. Then I found that the
companies appeared to have no members. Potential inconsistencies of this
kind are the reason for the Prime Directive of data storage, which is to
store information in one place only.
The biggest problem is the import itself, where you need to import a
primary key to Accounts and the foreign key to Contacts, while the
import process doesn't seem to offer general-purpose fields which can
later be easily found in the base tables. Those base tables seem to be
quite fragmented, with one-to-one relationships. I used Email3 for a key
which was alphanumeric.
I also populated the Company field of the Accounts, by joining the
original source tables for contacts and companies, and making a new
table for the contacts which also contained the full text name of their
company. It seemed to be easier to do that with the Access tables than
after they had been imported into the more complex BCM tables.
Here are the queries I used to link the ContactServiceID and
ParentContactServiceID, using the keys in Email3 for both contact and
account:
Query ServiceIDfromEmail3
SELECT dbo_ContactFullView.ContactServiceID,
dbo_ContactFullView.FullName, dbo_ContactFullView.Email3Address
FROM dbo_ContactFullView
WHERE (((dbo_ContactFullView.Type)=2) AND
((dbo_ContactFullView.IsDeletedLocally)=False));
Query MakeNewIDs (maketable query)
SELECT dbo_ContactFullView.ContactServiceID,
ServiceIDfromEmail3.ContactServiceID AS NewID INTO NewIDs
FROM dbo_ContactFullView INNER JOIN ServiceIDfromEmail3 ON
dbo_ContactFullView.Email3Address = ServiceIDfromEmail3.Email3Address
WHERE (((dbo_ContactFullView.Type)=1) AND
((dbo_ContactFullView.IsDeletedLocally)=False));
Query MainUpdate (select for safety, then update query)
SELECT dbo_ContactMainTable.ContactServiceID, NewIDs.NewID,
dbo_ContactMainTable.ParentContactServiceID
FROM dbo_ContactMainTable INNER JOIN NewIDs ON
dbo_ContactMainTable.ContactServiceID = NewIDs.ContactServiceID
WHERE (((dbo_ContactMainTable.Type)=1) AND
((dbo_ContactMainTable.IsDeletedLocally)=False) AND
((dbo_ContactMainTable.ParentContactServiceID) Is Null));
Note that accounts are in fact Contacts of Type=1 and the contacts have
Type=2. Note also that when the MainUpdate query is run and appears to
contain the right data, it should be converted to an update query where
ParentContactServiceID is set to NewID.
The same job is basically done again to update the GUIDs, still using
the ContactServiceID as the primary key to the ContactMainTable. These
queries could easily be combined, knowing what I know now.
Best of luck, and if you find any more gotchas, let us know.
