I have a parent table of Addresses (with LatLongs) and a child table of Viewings. When creating a new viewing and selecting an address, I want to display a show column with a list of the other addresses and their calculated distances from the selected one. Any idea how to do this?
I understand how to get a list of the other addresses’ latlongs but not how to calculate distance for each from selected address
In general, to show the list of other addresses, you may want to restrict the number other addresses and their distance being displayed. For example other addresses within say same zip code or state or city from the selected address that selects a reasonably finite number of nearby addresses.
To calculate distance on the fly from the selected address, I believe you may need an automation or a reference action called through the automation.
I believe the setup may be a bit elaborate needing a couple of columns to be added and an automation to be set up. I will work out and post further details, if I get a good practical way to achieve the needful.
However let us wait if someone else has a better idea to achieve the needful in an easier manner.
Hello @PGold,
I did some research and I found some information about this.
Method 1: Straight-Line Distance (AppSheet Expression)
Navigate to Data > Columns and select the table where you want the distance to appear.
Add a new Virtual Column and name it (e.g., Distance).
In the App Formula, use the following syntax:
Kilometers (km): DISTANCE([Location_1], [Location_2])
Miles: DISTANCE([Location_1], [Location_2]) * 0.621371
Distance to user’s current location: Use HERE() for Location_2 (e.g., DISTANCE([Destination], HERE()))
* Click here for more information about the Distance() function
Method 2: Driving Distance (Google Apps Script)
The native DISTANCE() function only measures straight lines. If you need actual driving/road distance, you must connect AppSheet to Google Apps Script using an automation.
Write a Google Apps Script using the Maps.newDirectionFinder() service to request directions between two addresses.
Have the script extract the route length and write it to your database.
Set up an AppSheet Automation (using the Call a Script task) to trigger this script automatically whenever new locations are saved.
* Click here for a detailed video on How to calculate distance between two points
I hope this helps!
@Jose_Arteaga thanks for the info
@Suvrutt_Gurjar thanks for responding. It will be a slice of addresses, only a few to calculate. I’m thinking to index through and calculate each one
This is what wanted, by indexing through list
Hi @PGold ,
Great.
It sounds that you got it working?
If so, please share the solution if possible with the community.
It will help any future reader of the post thread
Sure,
This is my expression, though i wonder if there’s a better way. It works for my case because I know there will only be a small, finite number of available properties . I’ve only indexed through 2 here to keep the example short
IF(CONTEXT(“ViewType”)=“Form”,
“Distance to other available properties:
“&
IF(ISNOTBLANK(INDEX(SELECT(Available Properties[Property ID],[_THISROW].[Property]<>[Property ID]),1)),SELECT(Available Properties[Short Name],[Property ID]=INDEX(SELECT(Available Properties[Property ID],[_THISROW].[Property]<>[Property ID]),1)) &” (”& ROUND(DISTANCE(Any(SELECT(Available Properties[LatLong],[Property ID]=INDEX(SELECT(Available Properties[Property ID],[_THISROW].[Property]<>[Property ID]),1))),[Property].[LatLong]))&" km)
“,”“)&
IF(ISNOTBLANK(INDEX(SELECT(Available Properties[Property ID],[_THISROW].[Property]<>[Property ID]),2)),SELECT(Available Properties[Short Name],[Property ID]=INDEX(SELECT(Available Properties[Property ID],[_THISROW].[Property]<>[Property ID]),2)) &” (“& ROUND(DISTANCE(Any(SELECT(Available Properties[LatLong],[Property ID]=INDEX(SELECT(Available Properties[Property ID],[_THISROW].[Property]<>[Property ID]),2))),[Property].[LatLong]))&” km)
“,”“)
,”")
Thank you @PGold. Got it. Great. It is what we call a brute force approach but works well for certain situations when there is no easy alternative.
Not sure at this moment if there is a better way. However the expression you shared could be possibly made shorter as follows. The sample below is also for 2 properties.
In terms of characters length the expression below has around 560 characters against 976 in the expression you shared, compressed to around 57 %
IFS(CONTEXT(“ViewType”)=“Form”,
“Distance to other available properties:”)&"
"&IFS( ISNOTBLANK(INDEX(Available Properties[Property ID]-LIST([Property]),1)),
INDEX(Available Properties[Short Name]-LIST([Short Name]),1)&", "& ROUND(DISTANCE(INDEX(Available Properties[Latlong]- LIST([LatLong]),1), [LatLong]))& " km" )& "
"& IFS( ISNOTBLANK(INDEX(Available Properties[Property ID]-LIST([Property]),2)),,
INDEX(Available Properties[Short Name]-LIST([Short Name]),2)&", "& ROUND(DISTANCE(INDEX(Available Properties[Latlong]- LIST([LatLong]),2), [LatLong]))& " km" )