> Hi Nancy.
>
> Yes, I am a plonker.
>
> The NHAccounts view should contain the following - notice the AS AccID bit
> which I missed out previously. To change, right-click on the NHAccounts view
> and select Design, then paste the stuff below into the SQL pane, replacing
> the previous statement, then save it.
>
> SELECT
> acc.ModifiedOn, acc.ModifiedBy, acc.EntryGUID AS AccGUID,
> cnt.FullName AS AccName, acc.ContactServiceID AS AccID,
> 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)
>
>
> As for the custom contact field, these are tricky to identify because there
> are different types and they are stored in various tables, basically the name
> of the user field is stored in the UserFieldDefinitions table, this
> cross-references somehow to the ContactAdditionalPropertyBag, and this
> cross-references somehow to the Contact via the ContactID, but I have not
> been able to figure out how it works.
>
> You can try a fuzzy matching to associate the comapny name with account name
> by using the LIKE comparison operator, so instead of the following bit in
> NHUpdate view
>
> FROM
> dbo.NHAccounts INNER JOIN
> dbo.NHContacts ON dbo.NHAccounts.AccName = dbo.NHContacts.CompanyName
>
> you could try
>
> FROM
> dbo.NHAccounts INNER JOIN
> dbo.NHContacts ON dbo.NHAccounts.AccName LIKE '%%' +
> dbo.NHContacts.CompanyName + '%%'
>
> which means match any account which contains the account name somwhere in
> the copmany name. - I may give you some more matches, but watch out for wrong
> matches!
>
> --
> regs Mark
>
>