Hi Nancy
I had to do the same thing. One of our salesmen loaded accounts and contacts
in on 22/8/2008 so I used my rudimentary SQL skills to associate the right
contacts with the right accounts, but only for the records loaded on the
22/8/2008.
This was how I did it using 3 Views to compensate for my lack of SQL skills
- you can adjust to suit. You will need admin access to the SQLserver
database.
1) Set your BCM to work Offline, and make sure no-one else is using BCM.
2) Open SQL Management Studio and connect to the BCM database server -
usually this is in the SQLServer instance called MSSMLBIZ then backup the
MSSmallBusiness database for safekeeping.
3) Expand the MSSmallBusiness database and right click on the Views and
Select New View. Close the Add Tables dialogue that may pop up and find the
SQL panel (it usually contains the words SELECT FROM)
4) Paste in the following statement :
SELECT
acc.ModifiedOn, acc.ModifiedBy, acc.EntryGUID AS AccGUID,
cnt.FullName AS AccName, acc.Type, acc.PrimaryContactGUID
FROM
dbo.ContactMainTable AS acc INNER JOIN
dbo.ContactNamesTable AS cnt ON acc.ContactServiceID = cnt.ContactServiceID
WHERE
(acc.ModifiedOn > '22 August 2008') AND (acc.ModifiedOn < '23 August 2008')
AND (acc.Type = 2)
5) Change dates to suit, click on the red ! to run it, just to check it
works, then save the view, give it the name NHAccounts
6) Add another new view like in step 3, but paste in the following statement:
SELECT Type, EntryGUID AS ContactGUID, CompanyName, ParentType,
ParentEntryID, ParentContactServiceID
FROM dbo.ContactMainTable AS con
WHERE (Type = 1) AND (CompanyName IS NOT NULL) AND (ModifiedOn > '22
August 2008') AND (ModifiedOn < '23 August 2008') AND (ParentContactServiceID
IS NULL)
7) Change dates to suit, then run it like the other to test then save this
view with the name NHContacts
8) Add another new view like in step 3, but paste in the following statement:
SELECT
dbo.NHAccounts.ModifiedOn, dbo.NHAccounts.ModifiedBy,
dbo.NHAccounts.AccGUID, dbo.NHAccounts.AccName, dbo.NHContacts.Type AS
ContactType, dbo.NHContacts.ContactGUID, dbo.NHContacts.CompanyName,
dbo.NHContacts.ParentType, dbo.NHContacts.ParentEntryID,
dbo.NHAccounts.AccID, dbo.NHAccounts.Type, dbo.NHAccounts.PrimaryContactGUID,
dbo.NHContacts.ParentContactServiceID
FROM
dbo.NHAccounts INNER JOIN
dbo.NHContacts ON dbo.NHAccounts.AccName = dbo.NHContacts.CompanyName
9) Run it like the other to test then save this view with the name NHUpdate
You can close the design panels after saving the views. Now you are ready to
run the update command.
10) Right click on MSSmallBusiness and select New Query
11) To check how many records will be changed before actually doing it paste
in the following statement and execute.
Select * from NHUpdate;
12) If you are happy with the selection then it's time to run the
command..Paste in the following update statement and Execute the query
update NHAccUpdate set Parenttype=2, parententryid=AccGUID,
ParentContactServiceID=AccId where parenttype is null;
12) You should get a message telling you how many rows were affected.
That's it
Set your BCM online, let it update its database and check your accounts and
contacts. They should be linked up now.
There are fancier ways to do this with one monster UPDATE statement but this
way you can use the views to check the accounts and contacts before you run
things and tweak the dates to constrain the update to specific records. You
can also the views for checking in future.
I used a variation to assign primarycontacts to each account, but actually
the account manager should decide that sort of thing!!
