Save the date! Join us on October 16 for our Product Ops launch event. Register here.
Mar 23, 2018 08:47 AM
I categorized this post under “support” because I suspect it can be accomplished without any additional functionality from Airtable but, if not, it may be more appropriate as a “feature request.”
Is it possible to have a field that calculates the distance (in miles/kil) between two address fields? I am trying to create a base to log miles driven for business since the IRS requires a milage log to take advantage of the “standard mileage deduction.”
Mar 24, 2018 04:21 AM
I don’t know any formula function to to this. Also, I think it can’t be done with Blocks.
Mar 24, 2018 11:18 AM
It appears to be a simple Zapier task but requires a paid Zapier account. (I say ‘appears’ because I was able to build a three-stage Zap — trigger on new Airtable record; issue request to Google Maps Distance Matrix API and receive response; update Airtable record. Component testing worked fine, but to perform an end-to-end systems test, I need a paid Zapier account.)
Essentially, I defined an Airtable table with three fields: {Origin}
, {Destination}
, and {Distance}
. ({Origin}
and {Destination}
are single-line text; {Distance}
is number.)
I then configured a three-step Zapier webhooks zap:
Airtable account, ‘Distance test’ base, [Test]
table
Trigger on new record
Zapier webhook Get
action.
URL: https://maps.googleapis.com/maps/api/distancematrix/json
Query string parameters:
units | imperial
origins | {Origin}
destinations | {Destination}
key | [My Google API key]
Send as JSON: no
Unflatten: yes
Airtable account, ‘Distance test’ base, [Test]
table
Action: Update record
Record: Use a custom value
Airtable: New record: Record ID
Set {Distance}
to Zapier ‘Get’: Rows elements distance value
This sets {Distance}
to the distance between the origin and destination addresses in meters; you’ll need to convert to miles for the IRS.
Mar 26, 2018 11:13 AM
Hmmm, ok thanks for the detailed response! I also don’t have a paid Zappier account and this issue isn’t quite worth it at this time.
Mar 26, 2018 11:14 AM
That’s what I thought. I think it would require a custom function option.
Jul 13, 2019 09:56 AM
Does Zapier allow you to share zaps? Because I would love to test this but all that code scares me :rofl:
Jul 15, 2019 07:28 PM
I don’t think so – well, except among members of a workgroup. I think I later built and tested such a Zap – you might find it documented in another post of mine. I’m in the [slow] process of sorting through the 300+ bases in my workspace and 1200+ Community posts and attempting to put the useful ones into some sort of resource base I can publish. I’ll see if I can find the distance code – which could easily be tied to one of the 60+ tables in one of my ‘scratch’ code testing bases – and share it.
Dec 25, 2019 09:03 AM
Thanks a lot for the instructions. I used it and tested it (I took a paid zapier account to test it) and it seems to work, however I wonder if I understood right that entering two addresses in AirTable should result in the distance and the duration (I added the duration).
It does so for the tests generated by Zapier, but when I type an address, nothing happens. I am assuming something incorrectly? Please see below.
Any suggestions that might solve this issue are very welcome!
Dec 25, 2019 09:30 AM
I came back to check and ‘it worked’, however the address are rewritten. That is a bit confusing. And there is something wrong with the distance and the timing (a small village in The Netherlands to Brussels in Belgium). Any the conclusion is that setting up a link between Google Maps and AirTable with Zapier works, but for the moment not as I had in mind.
Dec 26, 2019 10:54 AM
The issue about the address (to avoid it gets rewritten) I solved by adding a few extra steps, but the distance and timing problems remain.
question
I wonder how we can check the correctness of the distance and duration provided by the API? What extra steps do I need ?
As you can see the Royal houses of The Netherlands (The Hague) and Belgium (Brussels) only need one hour. Even for a royal that is fast…