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

I am using airtable for invoicing for a transportation company, and we bill by mileage. Ultimately, when billing, I want to be able to choose an origin point from a drop down, choose a destination from another drop down, and have airtable fill in the number of miles between the two locations in a third field.

I’m a bit of an airtable newbie, so I’m struggling to figure out how to set this all up. My current base is set up as follows:

Table containing a record for each item/route to be billed
Table for customer information
Table for invoices

Currently I am typing in the number of miles for each item to be billed. The routes we run have a finite number of origins and destinations, so it feels feasible (if a little arduous) to calculate up how many miles there are between each origin and each possible destination and store them in a table. I’m just not sure how to set that table up and how to connect it to my first table, especially by being able to select the origin/destination via dropdown.

Any help would be very appreciated!

Hi @Ashley_West, and welcome to the community!

Easily accomplished with some complex geometry in script. :wink: 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)
}
1 Like

@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.

This topic was solved and automatically closed 15 days after the last reply. New replies are no longer allowed.