Salesforce How To’s: Transform ‘Location’ to ‘Text’ in a Formula Field

Today’s Salesforce admin tip comes straight from the trenches! There are a lot of neat tricks you can do with formula fields and they’re sometimes an admin’s best tool for creatively manipulating data across objects and records, especially if you’re already near your lookup limit. But, while formula fields have no issues with complex calculations, currencies, or most data types, one thing they don’t account for is location data as stored in Salesforce..

Today’s Salesforce admin tip comes straight from the trenches! There are a lot of neat tricks you can do with formula fields and they’re sometimes an admin’s best tool for creatively manipulating data across objects and records, especially if you’re already near your lookup limit. But, while formula fields have no issues with complex calculations, currencies, or most data types, one thing they don’t account for is location data as stored in Salesforce.

So what do you do if, for example, you want to add location data to a record in a formula field? There’s no checkbox for the location data type when you’re creating a formula field and, if you select “text” and then make a really simple formula like: “Account.BillingAddress”, you get the following syntax error:

Unfortunately, the “Text()” function doesn’t work on location data types either, so the solution isn’t quite as simple as wrapping the address with it.

But, what you can do is a bit of creative formula drafting to to combine all the separate text-based components of the address into one appropriately formatted field! Instead of grabbing the whole address at once, we’ll instead grab the street address, city, state, zip code, and country and use our formula field to add the necessary formatting.

What you end up with is a formula that looks something like this:

and we pass our syntax check with flying colors!

Heres a more copy-paste-able version of that formula in case you’re in-org right now:

Account.BillingStreet + BR() + <br> Account.BillingCity + “, “+ <br> Account.BillingState +” “ + <br> Account.BillingPostalCode + BR() + <br> Account.BillingCountry

And, now on the record we have a perfectly-crafted address field!

Congratulations! You’ve overcome the dreaded syntax block and have your data appearing exactly the way you want it!

And, until next time, keep working hard, smart, and happy. We’ll see you in the cloud.