We are using Salesforce CRM to manage customer visits for our sales team. Each customer’s address is stored with Latitude and Longitude fields in Salesforce.
As a Salesforce Admin, I created a Formula Field that uses the DISTANCE() function to calculate the distance between the customer’s location and the Sales Office location. The result is shown in kilometres.
This helps the sales team quickly check the travel distance before planning customer visits and optimising their schedules.
In this article, we will learn about the Distance() function in Salesforce and how to use this function to calculate the distance between two numerical values or points. Additionally, we will focus on its syntax and provide some practical examples.
What is the DISTANCE() Function in Salesforce?
In Salesforce, we have various functions used to perform mathematical calculations, and the DISTANCE() function is one such mathematical function.
This DISTANCE() function in Salesforce is used to calculate the distance between two locations in miles and kilometres.
In addition, we should always remind ourselves of one thing: the DISTANCE() function only returns results for numeric data types and accepts data of type geolocation.
Furthermore, even if we present the geolocation distance in degrees, minutes, and seconds, the function always calculates the distance in decimals.
Therefore, it is recommended that when creating a custom field, you specify the number of decimal places to be displayed.
Syntax and Usage of the DISTANCE() Function in Salesforce
The Syntax of the Salesforce DISTANCE() function is given below:
DISTANCE(location_1 , location_2, 'unit')- location_1, location_2: These parameters can be a field, a numeric constant, or a formula. Ensure that both parameters are of data type Geolocation.
- Unit: This parameter specifies the units, such as ‘mi’ for miles or ‘km’ for kilometres.
Let’s see an example to clearly understand the concept and syntax of the DISTANCE() function.
Suppose we want to calculate the distance between the customer location and the store location. For this, we use the DISTANCE() function to calculate the distance. The formula is as follows:
DISTANCE(Customer_Location_Address__c, Store_Location_Address__c, 'km')Limitations of the DISTANCE() Function in Salesforce
The DISTANCE() function also has some limitations. Let’s discuss each of them.
- We cannot use the DISTANCE function in the reports.
- The DISTANCE function is the only function that can use GEOLOCATION parameters.
- Only logical operators > and < are supported in the DISTANCE function.
- Despite the topography between the two points, the distance is always measured as the straight-line distance.
Distance() Function in Salesforce: Calculate Distance between Geolocations
Now, let’s understand how to create and use the Distance() function in Salesforce to calculate the distance between two locations (Geolocations) in miles and kilometres.
- Go to the Gear Icon. -> Click on the Setup. -> Select the Object Manager tab. Select the Object on which you want to create the Formula Field. Here, I’m going to create an Opportunity Object.
- In the Opportunity setup, click on Fields and Relationships to create a new field, then click the New button.
- In this step, we will use a formula to calculate and display the date after adding days to the given date, and display it on the product object. To do this, select the Formula option from the data type and click Next.
- After entering a Field Label for the formula, the Field Name will be filled in automatically. Here, I enter the Field Label as Store Locations.
- Select Number as the Formula Return Type. After that, click Next.
- Enter the formula that calculates the due date in the Advanced Formula editor. The formula is given below:
- In this case, I want to calculate the distance between two locations using the DISTANCE() function. Here is the formula:
DISTANCE(Store_Location__c, Wearhouse_Location__c, 'km')- Here, we simply pass the two location fields, i.e., Store_Location__c and Warehouse_Location__c, as the location parameter and ‘km’ as the unit for the resultant value to the DISTANCE function.
- Click the “Check Syntax” button to validate the formula and ensure that it contains no errors.
- Optionally, we can “Handle the empty field” as well as we can define the “Description” and “Help text” if we want.
- Click on the “Next” button to move ahead.
- Select the appropriate Field Level Security, and to make it visible to everyone, we select the Visible checkbox.
- Select the Page Layout Name for which the formula field is to be added. Click Save to save the formula field.
- We can use the formula field once it has been created. Let’s look at an example:
- Open the Orders Tab and create a new order with the fields “Store Location” and “Warehouse Location”
- After that, click on the Details section, and the “Store Locators” field appears, showing the result of the DISTANCE() function.
As a result, we now know how to calculate the distance between two points in Salesforce Lightning.
Conclusion
With this, we have concluded that the Salesforce function DISTANCE() is used for calculating the distance. In addition, we have learned practical examples such as calculating the distance between store location and customer location, store location and warehouse location, lead location, and office location, etc.
Moreover, we have also understood the concept of the Salesforce DISTANCE() function, including its syntax and procedure for calculating the distance between two points in Salesforce Lightning.
You may like the following Salesforce tutorials:
- CEILING() Function in Salesforce
- COS() Function in Salesforce
- ATAN() Function in Salesforce
- Salesforce DAY Function
I am Bijay Kumar, the founder of SalesforceFAQs.com. Having over 10 years of experience working in salesforce technologies for clients across the world (Canada, Australia, United States, United Kingdom, New Zealand, etc.). I am a certified salesforce administrator and expert with experience in developing salesforce applications and projects. My goal is to make it easy for people to learn and use salesforce technologies by providing simple and easy-to-understand solutions. Check out the complete profile on About us.
