When using SmartConnect to pull the account (customer) information from Salesforce.com to keep data in sync to another system, people have run into issues trying to get Street address 1 and Street Address 2 as SalesForce stores these in one field called Billing Street. It is stored in a comma separated format. Here is a quick way to get street address 1 and street address 2.
To create a calculated field for Street 1, you would go into the SmartConnect map, additional columns, columns and select calculated. Give it a name something like STREET1_CALC, and copy the code below. This code finds the position of the 1st comma and get the left most portion of the Billing Street source column from the beginning to the just before the comma. If there is no comma, then return the Billing Street which only contains street address 1.
dim findposition as Integer
dim Street1 as String
findposition = _BILLINGSTREET.IndexOf(“,”)
if findposition > 0
Street1 = Microsoft.VisualBasic.Left(_BILLINGSTREET, findposition )
else
Street1 = _BILLINGSTREET
end if
return Street1
To create a calculated field for Street 2, you would go into the SmartConnect map, additional columns, columns and select calculated. Give it a name something like STREET2_CALC, and copy the code below. This code finds the position of the 1st comma and adds 1 to the position to start after the comma. We then get the substring of the Billing Street from the position after the comma to the end of the string. If there is no comma, then return blank as the Billing Street only contains street address 1.
dim findposition as Integer
dim Street2 as String
findposition = _BILLINGSTREET.IndexOf(“,”) + 1
if findposition < = 0
Street2 = “”
else
Street2 = _BILLINGSTREET.Substring( findposition , _BILLINGSTREET.Length – findposition -1 )
end if
return Street2
Hope this helps solve the curious street information from SalesForce.