Skip to content
+1-888-319-3663

EONE BLOG

Tech Tuesday: SalesForce.com Street Address


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.

4 Comments

  1. Don Comontofski on April 26, 2021 at 3:09 am

    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?

    • Ethan Sorenson on April 27, 2021 at 1:42 am

      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

  2. Don Comontofski on April 27, 2021 at 4:29 pm

    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

  3. Patrick Roth on April 27, 2021 at 5:18 pm

    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

Leave a Comment





RECENT POSTS


2021 Q4 Promotions
Connect with us at Directions EMEA 2021 in Milan!
eOne at Summit North America 2021
Employee Spotlight: Megan Tripp, Business Development Representative
eOne is now compatible with Microsoft Dynamics GP October 2021 Release

POPULAR POSTS


2016 SmartConnect Integration Bootcamps
Tech Tues: Automatically refreshing Pivot Tables in Excel Refreshable Reports
Happy Thanksgiving from the eOne team!
2017 SmartConnect Integration Bootcamps
New Releases of Extender and SmartList Builder

CATEGORIES

TAGS

Business Central CRM D365 Business Central Dynamics 365 dynamics crm Dynamics GP Dynamics NAV Econnect Employee Spotlight eone eOne News error Error Message Events Excel Excel Report Builder Extender Flexicoder GP integration Map Meet the Team Microsoft dynamics crm Microsoft Dynamics GP Navigation List Builder Office Relationships partners Popdock release Reporting SalesForce SalesForce.com SmartConnect SmartConnect.com SmartConnect Bootcamp SmartConnect Maps SmartConnect Office Hours SmartList SmartList Builder SmartPost SmartView SQL Tech Tuesday Templates training

Integrate & Automate without Any Code.

SmartList Data has Never Been Faster.

The Easiest Way to Report on GP Data.