Clean out trailing spaces in contact fields in Dynamics CRM

  • Article

If you import Data over SDK Webservices you are able to import trailing spaces. These trailing spaces are normally trimmed from the UI and never got to the database, but over the SDK there is no trimming.

If you synchronize contacts with outlook, Outlook parses the fields and trims the spaces. So this contact is marked as dirty and will be synchronized back to CRM. CRM does not update the data and within the next synchronization we get the trailing spaces re-synced again. The sync could last very long and impact the load on the server. The contacts that you modified manually are synchronized fine and the data is stored in the database with no trailing space and this contact will only be re-synced if something changes.

A script that first adds a trailing string that we will remove later and with the trailing string a real change, the data is synced as expected. The below is sample script to add a trailing string to addressfields street postalcode and city

The BusinessAddress File is a little bit special because BusinessAddressstreet and the other fields are virtual and parsed from outlook on the fly that’s why I selected the address field as example but you can do this any other field to.

Const olFolderContacts = 10
Dim olkApp, olkSes, olkContact
Set olkApp = CreateObject("Outlook.Application")
Set olkSes = olkApp.GetNamespace("MAPI")
olkSes.Logon "Outlook"
Set olkContacts = olkSes.GetDefaultFolder(olFolderContacts)
i=0
j=olkContacts.Items.count
For each olContact in olkContacts.Items
i=i+1
wscript.stdout.writeline ("record " & i & " out of " & j)
Set objProperty = olcontact.UserProperties.Find("crmxml")
If Not TypeName(objProperty) = "Nothing" Then
str=olcontact.BusinessAddressstreet
city=olcontact.BusinessAddresscity
plz=olcontact.BusinessAddressPostalcode
olcontact.BusinessAddress=str+"(remove)"+vbcrlf+ plz + " " + city+"(remove)"
olcontact.save
end if
Next
msgbox "Ready"

When you now have a look on your contacts you will see all the trailing “(remove)” strings.
Sync multiple times with CRM until no contacts are synced.
Now it’s time to remove the “(remove)” string and would do the below.

Const olFolderContacts = 10
Dim olkApp, olkSes, olkContacts
Set olkApp = CreateObject("Outlook.Application")
Set olkSes = olkApp.GetNamespace("MAPI")
olkSes.Logon "Outlook"
Set olkContacts = olkSes.GetDefaultFolder(olFolderContacts)
i=0
j=olkContacts.Items.count
For each olContact in olkContacts.Items
i=i+1
wscript.stdout.writeline ("bearbeite Datensatz " & i & " von " & j)
Set objProperty = olcontact.UserProperties.Find("crmxml")
If Not TypeName(objProperty) = "Nothing" Then
str=olcontact.BusinessAddressstreet
city=olcontact.BusinessAddresscity
plz=olcontact.BusinessAddressPostalcode
olcontact.BusinessAddress=replace(str,"(remove)","")+vbcrlf+ plz + " " + replace(city,"(remove)","")
olcontact.save
end if
Next
msgbox "READY"

Check your contacts and see that the trailing “(remove)” is removed.
Sync again multiply times until no contacts are synced anymore and now you have completed it.

If you have a lot of users may be you what to automat this script as add_remove.vbs and remove_remove.vbs. and write a Batch such as:

CRMOutlookSync.exe
CRMOutlookSync.exe
CRMOutlookSync.exe
cscript "add_remove.vbs"
CRMOutlookSync.exe
CRMOutlookSync.exe
CRMOutlookSync.exe
cscript "remove_remove.vbs"
CRMOutlookSync.exe
CRMOutlookSync.exe
CRMOutlookSync.exe

Download the crmoutlooksync.exe from the codeplex project here:
https://crmoutlooksync.codeplex.com/

Best Regards
Dynamics CRM Team