In this Salesforce tutorial, 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. In addition, we will also focus on its syntax and some practical examples
While working in a Logistic Organization that uses Salesforce as a customer relationship management tool to track their delivery. I got a task to calculate the distance between the customer location and the store location.
As a solution, I found the Salesforce DISTANCE() function which helps to calculate the distance between two locations. So, let’s discuss in detail, how to use the Salesforce DISTANCE() function to calculate the distance between two points in Salesforce Lightning as well as Salesforce Classic.
What is the DISTANCE() function in Salesforce
In Salesforce, we have various functions that are used to perform mathematical calculations and the DISTANCE() function is one of the mathematical functions.
This DISTANCE() function in Salesforce is used to calculate the distance between two locations in miles and kilometers. In addition, we should always remind one thing the DISTANCE() function only returns results on numeric data types and accept the data type as geolocation.
Furthermore, even if we present the geolocation distance in degrees, minutes, and seconds the function always calculated the distance in decimals. So, it is recommended that while creating a custom field, specify how many decimal places we want to display.
Syntax and Usage of DISTANCE() function in Salesforce
The Syntax of the Salesforce DISTANCE() function is given below:
DISTANCE(location_1 , location_2, 'unit')Here:
- location_1, location_2: These parameters can be a field, numeric constant, or formula. Make sure both the parameters are of data type Geolocation.
- unit: This parameter specify the units such as mi for miles or km for kilometers.
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 and calculate it. The formula is as follows:
DISTANCE(Customer_Location_Address__c, Store_Location_Address__c, 'km')Limitations of DISTANCE() function in Salesforce
The DISTANCE() function also has some limitations. Let’s discuss each of them.
- We can not use this 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 a straight line.
With this, we have understood the DISTANCE() function with its syntax, important points, and example. Now, we will move and try to understand the steps to calculate the distance between two locations in Salesforce Lightning.
Calculate the distance between two points in Salesforce Lightning
Here, are the steps to calculate the distance between two points in Salesforce Lightning:
1: First, log in to Salesforce Lightning mode and navigate to the right corner of the page, then click on the “Gear Icon”. Now, from the dropdown menu click on “Setup”.
2: From the left side of the menu, click on “Object Manager”.
3: Search for the object in the search bar where we wish to utilize the DISTANCE() function. In this case, I search for the “Orders” object as I want to track the distance between the store location and the warehouse location.
4: Now, click on the “Order” object and it will redirect us to the order page.
5: From the left side of the page click on the “Field & Relationship” option.
6: Click on the “New” button to create a new field where we can use the DISTANCE() function.
7: It will redirect to the “Choose the field type” page, now check the radio button in front of the “Formula” data type.
8: To proceed to the next step, click on the “Next” button.
9: Enter the “Field Label” for the formula and the unique “Field Name” will be automatically populated. In this case, I entered the field label named “Store Locators”.
10: Choose the “Data Type” for the field for which we want to calculate the distance between locations value.
Make sure the data type must be of a numeric type such as Number. In this case, I check the radio button in front of the “Number” to set it as the formula return type and here I set the decimal point to 2.
11: Now, to move to the next step click on the “Next” button.
12: Move to the advance formula editor, and enter the formula that uses the DISTANCE() function.
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 Wearhouse_Location__c as the location parameter and ‘km’ as a unit for the resultant value to the DISTANCE function.
13: Click on the “Check Syntax” button to validate the formula and ensure that there are no errors.
14: Optionally, we can “Handle the empty field” as well as we can define the “Description” and “Help text” if we want.
15: Click on the “Next” button to move ahead.
16: Choose the appropriate field “Level Security”. Here, I check the “Visible” checkbox as I want it to be visible to all. Then, click on the “Next” button.
17: Now, select the appropriate “Page Layout” where we want to be visible.
18: Lastly, click on the “Save” button to save the formula field.
We can use the formula field once we’ve created it. Let’s look at an example:
- Open the Orders Tab and create a new order with the fields “Store Location” and “Wearhouse Location”
- After that, click on the Details section, and here the “Store Locators” field appears which shows you the result of the DISTANCE() function.
As a result, we now know how to calculate the distance between two points in Salesforce Lightning. Now, we’ll look at how to calculate the distance between two points in Salesforce Classic.
Read: EXP() Function in Salesforce
Calculate the distance between two points in Salesforce Classic
Here, are the steps to calculate the distance between two points in Salesforce Classic:
1: From the Salesforce Lightning to open your Salesforce Classic edition. Click on Avtar and then click Switch to Salesforce Classic.
2: After that, click on the Setup option.
3: Now, scroll down to the Build section. Then under Customize, select the object for whom field we want to use the DISTANCE() function. In this case, I select the “Leads” object as I want to track the punctuality probability of the employees.
4: Click on the “Leads” dropdown and select the “Fields” option to get the option of the new field.
5: Now scroll down to the “Custom Fields & Relationships” and click on the “New” button to create a new formula field.
6: It will redirect to the “Choose the field type” page, now check the radio button in front of the “Formula” data type.
7: To proceed to the next step, click on the “Next” button.
8: The unique “Field Name” will be filled out automatically when you enter the “Field Label” for the formula. I entered “Punctuality Probability” into the field labeled in this instance.
9: Select the “Data Type” for field for which we want to calculate the distance. Make sure the data type must be of a numeric type such as “Number”.
In this case, I check the radio button in front of the “Number” to set it as the formula return type. I also set the decimal place to 2.
10: Now click the “Next” button to go to the next step.
11: Move to the advance formula editor, and enter the formula that uses the DISTANCE() function to calculate the distance between two points.
In this case, I want to calculate the distance between the employee location and to office location using the DISTANCE() function. Here, is the formula:
IF(
DISTANCE(
Client_Location__c,
Organization_Location__c,
'km'
) > 20,
50,
100
)- Here, we pass two location points i.e. “Client_Location__c” and “Organization_Location__c”, and ‘km’ as a unit of the resultant distance to the DISTANCE function.
- Next, we check that wheater the resultant distance value is greater than 20 km.
- If it is, it prints 50 which means there is only a 50% punctuality probability that the client reaches on time.
- If it is not, it prints 100 which means there is a 100% punctuality probability that the client reaches on time.
12: To check the formula and make sure there are no mistakes, click the “Check Syntax” option.
13: Optionally, we can define the “Description” and “Help text” as well as “Handle the empty field” if we prefer.
14: Click on the “Next” button to move ahead.
15: Select the relevant “Level Security” field. I have selected “Visible” in this case because I want it to be visible to everyone. Following that, select “Next” to move ahead.
16: Now, select the appropriate “Page Layout” where we want this field to be visible.
17: Lastly, click on the “Save” button to save the formula field.
We can use the formula field once we’ve created it. Let’s look at an example:
- Open the Lead Tab and create a new lead with the fields “Client Location” and “Organization Location”
- On save, it will redirect us to the Detail page, and here the “Employee Performance” field appears which shows us the result of the DISTANCE() function.
As a result, we now know how to calculate the distance between two points in Salesforce Classic.
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 with its syntax and procedure of calculating the distance between two points in Salesforce Lightning and Salesforce Classic.
You may like the following Salesforce tutorials:
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.