How to set up a table containing distances between locations for use in invoicing

Hi @Ashley_West, and welcome to the community!

Easily accomplished with some complex geometry in script. :winking_face: I like using the terms easy and complex in the same sentence.

Indeed, if you’re in transportation, you need location science and the most rudimentary element is miles between waypoints. I have one client who uses miles but also adds a surcharge for traffic. If a delivery run requires execution at peak rush hour times, the algorithm computes the cost of drivers waiting in traffic based on the known traffic load at the time of the run.

To do this, you’ll need a script block or script action that:

• … calls an API (like the Distance Matrix) to compute the driving distance from origin to destination.
• … updates the mileage field with the result.

If you happen to already know the lat/lng of origin and destination, you could avoid the API service call and compute the distance with this function. However, this would be estimated miles as the crow flies which could serve as a good estimate that is padded by 20%.

function getDistanceFromLatLonInKm(lat1,lon1,lat2,lon2) {
  var R = 6371; // Radius of the earth in km
  var dLat = deg2rad(lat2-lat1);  // deg2rad below
  var dLon = deg2rad(lon2-lon1); 
  var a = 
    Math.sin(dLat/2) * Math.sin(dLat/2) +
    Math.cos(deg2rad(lat1)) * Math.cos(deg2rad(lat2)) * 
    Math.sin(dLon/2) * Math.sin(dLon/2)
    ; 
  var c = 2 * Math.atan2(Math.sqrt(a), Math.sqrt(1-a)); 
  var d = R * c; // Distance in km
  return d;
}

function deg2rad(deg) {
  return deg * (Math.PI/180)
}

@Ashley_West: @Bill.French is our resident mapping expert, so everything he said above would be a great solution for you!

However, if you just want to type up all of your distances for your different routes in another table, you can use the new “Find Records” feature of Automations to grab the distance of matching routes and them paste the mileage back into your first table. This was not possible (outside of JavaScripting or using Integromat) until Airtable added the “dynamic find” feature into Automations for the “Find Records” command, which I think is brand new as of this week. I hope they roll this out across the entire product line — for filtering linked record fields, lookup fields, etc.

EDIT: I was just informed that the “dynamic value” feature of “Find Records” has been there since they launched “Find Records”, which was just a few weeks ago.

Thanks for your help, that sounds like what I’m looking for, but I’m still struggling to figure out how to implement it.

For a little more context, we’re talking about contracted field trips and sports runs for school transportation. So we’ve got about 10 possible origin points (different schools), with lets say 50 possible destinations. Exact driving distance is important since some of these runs have been awarded by bid which was based on exact mileage–so I probably will have to create a table containing all the distances if there’s no way to connect with Google Maps. That’s fine–it’s not ideal, but currently I have to do it for every item I invoice anyway. What I’m struggling with is that I’m not sure how to set up a table for that–particularly what to use for the primary field.

I am not completely sure how to set up the find records automation either, but can’t play around too much until I get the table set up.

Thanks for the help!

Yes, you can do it either way — create a table containing all the distances, or connect with Google Maps. Or a combination of both.

If you don’t want to write your own Javascript code for communicating with Google Maps, you can also do it in a low-code/no-code way with Integromat’s Google Maps integration:

p.s. Note that I am a professional Airtable consultant and a Registered Integromat Partner, and the Integromat link contains my personal referral code. If you have a budget for this project and you’d like to hire an expert to help you set this up, please feel free to contact me through my website at ScottWorld.com.