I am trying to create a custom button url formula that will populate a start address and destination address in google maps via the driving travel mode. We have a business need to calculate the miles (by car) between two points for eligibility purposes.
Each time I try, Salesforce throws me an error saying that I need to enter a valid and/or well-formed url.
The start address = {!Practicum__c.Residential_Street__c} + {!Practicum__c.Residential_City__c} +{!Practicum__c.Residential_State__c} + {!Practicum__c.Residential_Zip_Code__c}
The destination address = {!Practicum__c.Site_Street__c} + {!Practicum__c.Site_City__c} + {!Practicum__c.Site_State__c} + {!Practicum__c.Site_Zip_Code__c}
I tried going the geocoding route and learned Salesforce retired data.com geo. I don't want to integrate with another service to get the url auto-populated. Here is what I've tried so far. I'm not sure if I'm overcomplicating it. All the field types are TEXT.
"https://www.google.com/maps/dir/?api=1&travelmode=driving&origin=" &
IF(ISBLANK(Practicum__c.Residential_Street__c), "", SUBSTITUTE(Practicum__c.Residential_Street__c, " ", "+") & ",") &
IF(ISBLANK(Practicum__c.Residential_City__c), "", SUBSTITUTE(Practicum__c.Residential_City__c, " ", "+") & ",") &
IF(ISBLANK(Practicum__c.Residential_State__c), "", SUBSTITUTE(Practicum__c.Residential_State__c, " ", "+") & ",") &
IF(ISBLANK(Practicum__c.Residential_Zip_Code__c), "", SUBSTITUTE(Practicum__c.Residential_Zip_Code__c, " ", "+")) &
"&destination=" &
IF(ISBLANK(Practicum__c.Site_Street__c), "", SUBSTITUTE(Practicum__c.Site_Street__c, " ", "+") & ",") &
IF(ISBLANK(Practicum__c.Site_City__c), "", SUBSTITUTE(Practicum__c.Site_City__c, " ", "+") & ",") &
IF(ISBLANK(Practicum__c.Site_State__c), "", SUBSTITUTE(Practicum__c.Site_State__c, " ", "+") & ",") &
IF(ISBLANK(Practicum__c.Site_Zip_Code__c), "", SUBSTITUTE(Practicum__c.Site_Zip_Code__c, " ", "+"))
Does anyone have any insight? Thank you!
I sorted the issue out. I believe I was making the formula unnecessarily complicated and didn't have it formatted according to google's guidelines. Here is how I solved it so that now when the user clicks on the "Drive Distance" button, a new window opens google maps, maps the origin and destination, and finds the distance by driving.
The url needed to be formatted like this:
https://www.google.com/maps/dir/?api=1&origin={address data}
&destination={address data}&travelmode=driving
I created two formula fields, one that combined all Account address fields and one that combined all Contact address fields with a formula following these parameters: all these fields are combined into a text sting (if they are not blank), then separated by "+" (if they are not blank), and all spaces found within the values of each field are replaced with a "+".
It ended up looking like this:
IF(
NOT(ISBLANK(Scholar__r.MailingStreet)),
SUBSTITUTE(Scholar__r.MailingStreet, " ", "+"),
""
) &
IF(
AND(
NOT(ISBLANK(Scholar__r.MailingStreet)),
NOT(ISBLANK(Scholar__r.MailingCity))
),
"+",
""
) &
IF(
NOT(ISBLANK(Scholar__r.MailingCity)),
SUBSTITUTE(Scholar__r.MailingCity, " ", "+"),
""
) &
IF(
AND(
OR(
NOT(ISBLANK(Scholar__r.MailingStreet)),
NOT(ISBLANK(Scholar__r.MailingCity))
),
NOT(ISBLANK(Scholar__r.MailingState))
),
"+",
""
) &
IF(
NOT(ISBLANK(Scholar__r.MailingState)),
SUBSTITUTE(Scholar__r.MailingState, " ", "+"),
""
) &
IF(
AND(
OR(
NOT(ISBLANK(Scholar__r.MailingStreet)),
NOT(ISBLANK(Scholar__r.MailingCity)),
NOT(ISBLANK(Scholar__r.MailingState))
),
NOT(ISBLANK(Scholar__r.MailingPostalCode))
),
"+",
""
) &
IF(
NOT(ISBLANK(Scholar__r.MailingPostalCode)),
SUBSTITUTE(Scholar__r.MailingPostalCode, " ", "+"),
""
)
The output looked like this: 415+Mission+St+San+Francisco+CA+94105
Last, I created a custom url button that referenced just the two formula fields I created.
The final url looks like this:
https://www.google.com/maps/dir/?api=1&origin={!Practicum__c.Scholar_Address__c}
&destination={!Practicum__c.Practicum_Site_Address__c}&travelmode=driving
Now it works!
