Tech Tuesday: SalesForce.com Street Address
Tuesday, March 1, 2016When 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.
There is no comma embedded in the street field that I see, but there is a CRLF, but I can\’t get this code to work with that and find the CRLF. Any ideas as to why?
Don,
Try addressing the CRLF with the following syntax.
Microsoft.VisualBasic.vbLf,Microsoft.VisualBasic.vbCRLf
The full script should look something like:
dim findposition as Integer
dim Street2 as String
findposition = _BILLINGSTREET.IndexOf(Microsoft.VisualBasic.vbLf,Microsoft.VisualBasic.vbCRLf)
if findposition < = 0 Street2 = “” else Street2 = _BILLINGSTREET.Substring( findposition , _BILLINGSTREET.Length – findposition -1 ) end if return Street2
Thank you so much Ethan, you\’re awesome. I am still learning VB. I had to use the FN.Length function, it kept giving me errors with your code.
VBCRLF did not work but VBLF did.
Street1 Calc field
dim findposition as Integer
dim Street1 as String
findposition =_BILLING_CONTACT__R__MAILINGSTREET.IndexOf(Microsoft.VisualBasic.vbLf)
if findposition > 0
Street1 = Microsoft.VisualBasic.Left(_BILLING_CONTACT__R__MAILINGSTREET, findposition )
else
Street1 = _BILLING_CONTACT__R__MAILINGSTREET
end if
return Street1
For Street2 Calc Field
dim findposition as Integer
dim findpos2 as Integer
dim Street2 as String
findposition = _BILLING_CONTACT__R__MAILINGSTREET.IndexOf(Microsoft.VisualBasic.vbLf)
findpos2 = fn.LENGTH(_BILLING_CONTACT__R__MAILINGSTREET) – findposition -1
if findposition < = 0 then
Street2 = \"\"
else
Street2 = _BILLING_CONTACT__R__MAILINGSTREET.Substring( findposition , findpos2 )
end if
return Street2
Yes – in the authors case it must have been comma separated every time when they entered (or imported) it.
But if you just type into the field and press “Return” on each line, SF appears to use just the LF (char 10) value and not the CRLF as you described.
So the solution was to do what you note – just switch the character you are looking for.
Kinda funny but someone else had asked exactly this in a support case on Friday and the above was the solution.
Or, better, rewrite it a bit to make it a bit easier to follow.
dim addresses() as string
dim addr1 as string, addr2 as string, addr3 as string
addresses = _SHIPPINGSTREET.Split(Microsoft.VisualBasic.Chr(10))
if addresses.Length >= 1 then
addr1 = addresses(0)
end if
if addresses.Length >= 2 then
addr1 = addresses(1)
end if
if addresses.Length >= 3 then
addr1 = addresses(2)
end if
MessageBox.Show(addr1)
MessageBox.Show(addr2)
MessageBox.Show(addr3)
In the above script, it looks for Microsoft.VisualBasic.Chr(10) which is the VbLf – i just like using the numeric value.
So it uses the .Split() function to split the string up on the vbLf character.
So if i have multiple lines, it splits them for me w/o having to look for the the lines manually.
So if i had one line or four lines, my array length tells me how many lines it found.
So i’d make the 2 or 3 calc fields that look like the above up to the split.
and then you check the length. so my CALC_LINE_2 field would be:
dim addresses() as string
addresses = _SHIPPINGSTREET.Split(Microsoft.VisualBasic.Chr(10))
if addresses.Length >= 2 then
return addresses(1)
else
return “”
end if
etc