Thursday, October 30, 2014

Easy Maps for Account and Contact Addresses in Salesforce

Some time ago, Salesforce had to remove their "Google Maps for Salesforce" from the App Exchange due to a change in Google's Licencing arrangements.

I've not seen a good example of easy to use Google Maps in Salesforce since, so here are some Custom Field Formulas. Simply create a new Custom Field with a Forumla -> Text type, and copy/paste the below into the formula area. On the page layout, it works really well below the address entry areas.

Each of these will display a static image map inline in Salesforce, and once the user clicks on the map, it will automatically display directions from the User's Address (stored on their User Profile) to that location. It will also complain to the user if they do not have an address on their profile, and will not display the Maps until they do.

Account Billing Address

if(or( $User.Street = "",  $User.City = "",  $User.Country  = ""),IMAGE("/img/samples/flag_red.gif", "red", 12, 12) + " Please Update "+HYPERLINK("/_ui/core/userprofile/UserProfilePage", "Your Profile")+" to include your Street, City and Country to see the map",

if(or(BillingStreet = "", BillingCity ="", BillingCountry =""), "Please Enter the Billing Address, Suburb and Country to see the Map.",

HYPERLINK(""+ $User.Street +","+ $User.City +","+ $User.State +" "+ $User.Country +"&daddr="+ BillingStreet +", "+BillingCity +" "+ BillingState +" "+ BillingCountry +"&hl=en&t=m",

IMAGE(""+ BillingStreet +","+ BillingCity +" "+BillingState+" "+BillingCountry +"&zoom=14&size=300x300&maptype=roadmap&markers=color:red%7C"+ BillingStreet +","+ BillingCity +" "+BillingState+" "+BillingCountry , 'Map'), "_Blank") + BR() + "Click to get Directions to this Address"

Account Shipping Address

if(or( $User.Street = "",  $User.City = "",  $User.Country  = ""),IMAGE("/img/samples/flag_red.gif", "red", 12, 12) + " Please Update "+HYPERLINK("/_ui/core/userprofile/UserProfilePage", "Your Profile")+" to include your Street, City and Country to see the map",

if(or(ShippingStreet = "", ShippingCity ="", ShippingCountry =""), "Please Enter the Shipping Address, Suburb and Country to see the Map.",

HYPERLINK(""+ $User.Street +","+ $User.City +","+ $User.State +" "+ $User.Country +"&daddr="+ ShippingStreet +", "+ShippingCity +" "+ ShippingState +" "+ ShippingCountry +"&hl=en&t=m",

IMAGE(""+ ShippingStreet +","+ ShippingCity +" "+ShippingState+" "+ShippingCountry +"&zoom=14&size=300x300&maptype=roadmap&markers=color:red%7C"+ ShippingStreet +","+ ShippingCity +" "+ShippingState+" "+ShippingCountry , 'Map'), "_Blank") + BR() + "Click to get Directions to this Address"

Contact Mailing Address

if(or( $User.Street = "",  $User.City = "",  $User.Country  = ""),IMAGE("/img/samples/flag_red.gif", "red", 12, 12) + " Please Update "+HYPERLINK("/_ui/core/userprofile/UserProfilePage", "Your Profile")+" to include your Street, City and Country to see the map",

if(or(MailingStreet = "", MailingCity ="", MailingCountry =""), "Please Enter the Mailing Address, Suburb and Country to see the Map.",

HYPERLINK(""+ $User.Street +","+ $User.City +","+ $User.State +" "+ $User.Country +"&daddr="+ MailingStreet +", "+MailingCity +" "+ MailingState +" "+ MailingCountry +"&hl=en&t=m",

IMAGE(""+ MailingStreet +","+ MailingCity +" "+MailingState+" "+MailingCountry +"&zoom=14&size=300x300&maptype=roadmap&markers=color:red%7C"+ MailingStreet +","+ MailingCity +" "+MailingState+" "+MailingCountry , 'Map'), "_Blank")+ BR() + "Click to get Directions to this Address"

Contact Other Address

if(or( $User.Street = "",  $User.City = "",  $User.Country  = ""),IMAGE("/img/samples/flag_red.gif", "red", 12, 12) + " Please Update "+HYPERLINK("/_ui/core/userprofile/UserProfilePage", "Your Profile")+" to include your Street, City and Country to see the map",

if(or(OtherStreet = "", OtherCity ="", OtherCountry =""), "Please Enter the Other Address, Suburb and Country to see the Map.",

HYPERLINK(""+ $User.Street +","+ $User.City +","+ $User.State +" "+ $User.Country +"&daddr="+ OtherStreet +", "+OtherCity +" "+ OtherState +" "+ OtherCountry +"&hl=en&t=m",

IMAGE(""+ OtherStreet +","+ OtherCity +" "+OtherState+" "+OtherCountry +"&zoom=14&size=300x300&maptype=roadmap&markers=color:red%7C"+ OtherStreet +","+ OtherCity +" "+OtherState+" "+OtherCountry , 'Map'), "_Blank")+ BR() + "Click to get Directions to this Address"

Tuesday, June 10, 2014

Australian Business Number (ABN) Validation Rule for

Assuming your Custom field is "ABN" (API Name = "ABN__c") then you can stuff this formula in a validation rule to ensure that ABNs entered are valid by using the same logic the government does.

  LEN(TRIM( ABN__c )) == 11,
     (((VALUE(MID(ABN__c, 1, 1)) - 1 ) * 10) + 
(VALUE(MID(ABN__c, 2, 1)) * 1) + 
(VALUE(MID(ABN__c, 3, 1)) * 3) + 
(VALUE(MID(ABN__c, 4, 1)) * 5) + 
(VALUE(MID(ABN__c, 5, 1)) * 7) + 
(VALUE(MID(ABN__c, 6, 1)) * 9) + 
(VALUE(MID(ABN__c, 7, 1)) * 11) + 
(VALUE(MID(ABN__c, 8, 1)) * 13) + 
(VALUE(MID(ABN__c, 9, 1)) * 15) + 
(VALUE(MID(ABN__c, 10, 1)) * 17) + 
(VALUE(MID(ABN__c, 11, 1)) * 19)),
  ) == 0

Friday, April 11, 2014

Cairns Webcams for Cyclone Ita

Here are some links for Webcams around Far North Queensland. I'll strike them out as they go down.


Monday, January 20, 2014

Adding Google Search to Internet Explorer in Australia

After I get my computer rebuilt at work, one of the first things I do is change the Search provider to Google in whatever archaic version of Internet Explorer we are still using.

However, when I tried to do this today, Google was not in the list of Search Providers:

After a moderate amount of cursing and nashing of teeth, I hacked the URL to point to the US page:

And you can reach the specific Google Search provider here:

Tuesday, November 26, 2013

Australian Postcode Boundaries for Google Earth (KMZ)

I searched for ages, and couldn't find where to get a KMZ / KML file for Google Earth that had all the Australian Postcode Boundaries in it...

So I made one based on the 1270.0.55.003 - Australian Statistical Geography Standard (ASGS): Volume 3 - Non ABS Structures, July 2011.

I claim no additional copyright on this, but please ensure you are able to use the data according to the ABS Copyright rules

Here is the download link

Tuesday, February 26, 2013

Inline 18 digit Salesforce ID Excel formula

If you have not got the ability to create a custom field to generate the 18 digit case insensetive ID, as demonstrated here, you can use this formula to convert the ID in "A2" to the 18 digit ID. Of course you can then extend this formula all the way down column A if you need to.

=A2 & LOOKUP(IF(AND(CODE(RIGHT(LEFT($A2,5),1))>=65,CODE(RIGHT(LEFT($A2,5),1))<=90),1,0) & IF(AND(CODE(RIGHT(LEFT($A2,4),1))>=65,CODE(RIGHT(LEFT($A2,4),1))<=90),1,0) & IF(AND(CODE(RIGHT(LEFT($A2,3),1))>=65,CODE(RIGHT(LEFT($A2,3),1))<=90),1,0)& IF(AND(CODE(RIGHT(LEFT($A2,2),1))>=65,CODE(RIGHT(LEFT($A2,2),1))<=90),1,0)& IF(AND(CODE(LEFT($A2,1))>=65,CODE(LEFT($A2,1))<=90),1,0),{"00000","00001","00010","00011","00100","00101","00110","00111","01000","01001","01010","01011","01100","01101","01110","01111","10000","10001","10010","10011","10100","10101","10110","10111","11000","11001","11010","11011","11100","11101","11110","11111"},{"A","B","C","D","E","F","G","H","I","J","K","L","M","N","O","P","Q","R","S","T","U","V","W","X","Y","Z","0","1","2","3","4","5"}) & LOOKUP(IF(AND(CODE(RIGHT(LEFT($A2,10),1))>=65,CODE(RIGHT(LEFT($A2,10),1))<=90),1,0) & IF(AND(CODE(RIGHT(LEFT($A2,9),1))>=65,CODE(RIGHT(LEFT($A2,9),1))<=90),1,0) & IF(AND(CODE(RIGHT(LEFT($A2,8),1))>=65,CODE(RIGHT(LEFT($A2,8),1))<=90),1,0)& IF(AND(CODE(RIGHT(LEFT($A2,7),1))>=65,CODE(RIGHT(LEFT($A2,7),1))<=90),1,0)& IF(AND(CODE(RIGHT(LEFT($A2,6),1))>=65,CODE(RIGHT(LEFT($A2,6),1))<=90),1,0),{"00000","00001","00010","00011","00100","00101","00110","00111","01000","01001","01010","01011","01100","01101","01110","01111","10000","10001","10010","10011","10100","10101","10110","10111","11000","11001","11010","11011","11100","11101","11110","11111"},{"A","B","C","D","E","F","G","H","I","J","K","L","M","N","O","P","Q","R","S","T","U","V","W","X","Y","Z","0","1","2","3","4","5"})  & LOOKUP(IF(AND(CODE(RIGHT(LEFT($A2,15),1))>=65,CODE(RIGHT(LEFT($A2,15),1))<=90),1,0) & IF(AND(CODE(RIGHT(LEFT($A2,14),1))>=65,CODE(RIGHT(LEFT($A2,14),1))<=90),1,0) & IF(AND(CODE(RIGHT(LEFT($A2,13),1))>=65,CODE(RIGHT(LEFT($A2,13),1))<=90),1,0)& IF(AND(CODE(RIGHT(LEFT($A2,12),1))>=65,CODE(RIGHT(LEFT($A2,12),1))<=90),1,0)& IF(AND(CODE(RIGHT(LEFT($A2,11),1))>=65,CODE(RIGHT(LEFT($A2,11),1))<=90),1,0),{"00000","00001","00010","00011","00100","00101","00110","00111","01000","01001","01010","01011","01100","01101","01110","01111","10000","10001","10010","10011","10100","10101","10110","10111","11000","11001","11010","11011","11100","11101","11110","11111"},{"A","B","C","D","E","F","G","H","I","J","K","L","M","N","O","P","Q","R","S","T","U","V","W","X","Y","Z","0","1","2","3","4","5"})

Thursday, November 8, 2012

Salesforce Merge Accounts URL Hacking

The Merge Accounts page is OK to use, however to start the process you have to search for the Accounts you want to merge, and to do that they have to have the same name. This is not always the case, and it can be annoying to have to rename an account, just so you can find it to merge it.

The official SFDC selection screen also limits you to a max of 3 accounts. This isn't actually a hard limit... More of a guide, as you'll see below.
Instead of this, you can actually hack the URL to include the IDs of any accounts, regardless of Account Name similarity. This is especially useful if you are using your own code to manage merges and would like to "deep link" into the SFDC platform.

Here's an example URL:
It can be broken down into 3 main segments:

The Merge Accounts page URL:

A Parameter to indicate that SFDC should start merging, and it will expect account IDs to be on the URL:

The last segment is a collection of SFDC Account IDs. All use the parameter name of "cid". In the above example there are 4, however you may include as many as you like (I've successfully merged 75 accounts at once), including more than the default 3 allowed by the web form:


Replace these IDs with the Accounts you would like to merge, and you should be presented with the familiar Merge Account page.