Map block is issuing excessive geocoding requests

[Anandaroop_Roy]

I have recently configured the Map block on a few tables with a total of couple of hundred rows between them.

A few days later I checked my Google Developers console to see how my geocoding API key was faring, and I was shocked to see ~18,000 requests for that key.

If I open a Map block and the associated table simultaneously, I can see the geocoding cache column get updated repeatedly as I interact with the map — even though no data in those rows is changing. The gif below shows cell updates being triggered just while viewing/panning/zooming the map…

Then, if I open up one of records in question, I can see a bunch of changelog entries of the form: “User X edited this record using Map block Y”…

Can anyone explain what’s going on here? I’d like to keep using the Map block, but not if it’s going to blow up my API quotas simply by viewing the maps

[Anandaroop_Roy]

One more thing about this table, in case it’s relevant.

The column that I’ve configured as the the Map block’s “location field” is actually a formula field, so that I can concatenate a few things together (e.g. address, city, state) to form a geocodable string.

Is there something about using a formula as the location field that would cause this overzealous geocoding?

[Mohamed_Swellam]

Hi @Anandaroop_Roy,

Welcome to Airtable Community!

I would definitely say yes it is the formula field that is causing this. As Airtable is kind of refreshing the formula field to make sure it is correct if you ever change anything in the fields that are used in the formula.

A quick fix would be using Zapier to copy the formula field into a text field, then using this text field as the address. Or you can also do it manually by duplicating the Formula field and changing it into a text field, this means that you will copy and paste the address manually every time you add a new one.

Hope this helps! If it does, please mark this as Solution so others can benefit from it.

BR,
Mo

[Bill.French]

@Anandaroop_Roy

Is there something about using a formula as the location field that would cause this overzealous geocoding?

Yes. It’s a field of type FORMULA which is very much unlike actual data fields. As such, it is prone to unanticipated behaviours including the recalculations that you are seeing. Unfortunately, there’s no way to control this and it creates a runaway quota attack when it really needs to remain quiet for any populated row.

This is precisely one of the many issues that arise with hybrid field types, recently discussed in depth over in this thread. Ideally, any field should be able to optionally apply a formula that is contingent on the underlying field value as being empty.

URL--Text to Display Feature

In the recent examples presented, field1 and field2 are not the fields where the formula exists; rather, they are fields referenced BY the formula. Wherever there is a field, I think it makes sense to have the ability to apply attributes, and attributes should include many things that fields are capable of including but not limited to: Field name Description Data type Conditional and default formatting Grid validation rules Form validation rules Security rules Formula Formulas - like all fi…

Since that’s not possible, what is the remedy?

Short answer - you cannot use a formula field for direct geo-encoding. Rather, you must use real fields for lat/lon or any type of geo-location object. This is best achieved through a Script Block that you run from time-to-time or an API process that runs periodically.

Another idea to consider is to cache lat/lng or geo-encoded data or addresses to avoid excessive Map quota costs. I always create and sustain location data and pull from the cache rather than re-encoding locations that I’ve already encountered. Our platform for public transit has performed more than a half-million reverse geo-code address lookups in the LA area and we never once paid a dime to Google by using this cache approach.

[Kamille_Parks]

Bill can you explain how that applies in this case? If the field in question is concatenating an address, a city, and a state together as required by the Map Block, wouldn’t conditionally applying a formula to a Single Line field run into the same problem? Whether or not the formula is a standalone field, wouldn’t Airtable still be constantly checking if the address/city/state fields have changed or if the conditions under which the formula should be applied have been met?

I feel like for this case, a more targeted feature request would be to just to let people define multiple location fields in the map block instead of requiring the full location definition (be it an address or a set of coordinates) be in one field.

[Bill.French]

@Kamille_Parks

If the field in question is concatenating an address, a city, and a state together as required by the Map Block, wouldn’t conditionally applying a formula to a Single Line field run into the same problem?

I’m certainly no expert in matters of formula computation rules internal to Airtable but it seems that the one aspect missing from this particular solution is the ability of formula fields to be able to know if they have already performed the calculation at least once. Since a formula field – by definition – uses the formula results as a proxy for the “field” value, it has no way to recursively inspect itself, right?

Formula Fields Lack Inspectable State

Lacking an ability to inspect the value of itself nor the ability to write values into a real data field, it cannot process the logic required to know if it has already computed the needed geo-encoding value once before. Ergo, it keeps processing and running up a tab for this customer’s account. It is simply a dumb function that has no idea if it has previously executed once, 20 times, 2,000 times, or never before.

That said, I’m pretty sure (correct me if I’m missing something) you can’t create a formula scenario with logic that says simply -

If this formula has already computed a value, stop computing a value.

What if …

What if we hypothesize for a moment and imagine that formulas are simply attributes of actual data fields. A real field with an attached formula could inspect the value in the field and if NULL, execute the formula repeatedly and as the computation engine calls for, but update the field itself only once. A lovely benefit of this model also emerges because deleting any existing value in such a field would cause the formula to reassert its logic, but only once if it was created with the specified conditional logic as noted in the diagram.

The design might look something like this:

My observation is simple - formula fields are not able to leverage the current value of themselves; ergo - they lack the ability to leverage their own state. In cases like this – and I assert, there are many others – the ability to use the previous results of a formula in future formulaic processing makes it possible to address many complex requirements involving computations and formula logic.

Workaround?

I tried to imagine a number of workarounds for this user’s situation and all of them led [me] to the conclusion that I needed to include a real data field with some sort of external scripting process - Script Block or API. Perhaps I’m not clever enough to come up with an approach using formulas only, but it looks like users are in a box when depending on formula fields to create dynamic data values that must mimic actual field values or suffer some rather nasty consequences.

Got a better idea for this user? Would love to learn how to do this a better way - I just can’t envision one.

[Kamille_Parks]

I’ve seen plenty of uses cases where someone needs to calculate a value via a formula, but wanting to keep that value once it’s been computed static. So in this case we have a field for a full address, concatenating a street address, city, and state field together. Is a full address for any particular going to change often or at all? Probably not, so it make sense that this would be a “static calculation”, where the value is computed once and Airtable stops checking.

I like that idea, but if someone wants to refresh a particular record’s value for that field (to account for changes to another referenced field), there should be some recourse for that. Maybe there’s a toggle for formula/rollup fields to keep values “synched” or to do the one-and-done calculation method. If toggled, and a formula output is found to be “outdated”, maybe Airtable just applies some styling to that cell to give the user an indication they need to update it.

How would Airtable know to stop checking? Well, according to the Custom Blocks doc, a formula field “knows” which other fields it references, meaning an array could be returned of those referenced fields. Airtable formulas have the ability to check the LAST_MODIFIED_TIME() of any particular field. So, I think there is some wiggle room for Airtable to devise a way to more cleverly check when to recalculate a formula value.

[Bill.French]

Sorry - I forgot to address the other part of your question.

@Kamille_Parks

I feel like for this case, a more targeted feature request would be to just to let people define multiple location fields in the map block instead of requiring the full location definition (be it an address or a set of coordinates) be in one field.

I’m certainly not ruling this out; it’s a good question. However, like all feature requests that deal with a single missing dimension, we see products become woefully burdened and featured to death. Systems grow to become clunky - pretty soon you’re addressing interest that are increasingly narrow.

I see things like this similarly to the lack of a Split() function. Would you ask Airtable to create a new formula that parses addresses? Or, would you recommend a new formula function that parses anything, allowing users to apply the new function to dozens of parsing objectives? If you convince them to implement an address parser, the very next request will be for postal code parsing for the UK. And then one that supports German post codes, etc.

There’s nothing wrong with adding new and specific features for location science - we live in a location commerce world now, so that’s good. But I like to encourage teams to be careful about expanding features that do stuff versus features that help users do lots of stuff - even stuff we can’t imagine.

[ScottWorld]

I have an address formula field that is tied to the Maps block, but it doesn’t keep triggering my Google Maps API over & over again.

It only triggers the API when the address changes.

Is it possible that there is something else going on with the formula field that @Anandaroop_Roy is using?

Mine is just a simple formula field — concatenating 4 text fields (address, city, state, zip).

@Anandaroop_Roy, are you using any lookups or rollups in your calculation field? Or anything else that might be interesting about your formula?

If not, I’m not sure why there’s difference between my Maps block and your Maps block. Maybe it has something to do with the number of records — I only have a small handful of records in my table.

[Bill.French]

@Kamille_Parks

I like that idea, but if someone wants to refresh a particular record’s value for that field (to account for changes to another referenced field), there should be some recourse for that.

I agree, and that’s yet another problem that exists today and for which there is no clever answer with or without fields that support formulas.

@Kamille_Parks

So, I think there is some wiggle room for Airtable to devise a way to more cleverly check when to recalculate a formula value.

I agree - lots of possibilities. But one aspect of [real] fields that support formulas is the fields are allowed to participate in all aspects of the platform such as the schema, API, etc. Fields populated with links would transform properly in PDF documents - there’s a universe of ways that formula fields trip up users and surprise them with cul-de-sacs.

[Bill.French]

@ScottWorld

It only triggers the API when the address changes.

I think yours will re-trigger if you render the data in the UI. As I understand Airtable’s behaviour, if you never look at it, you’ll be fine. I have not created a test-bed to validate this.

Have you actually looked at your Maps API console to see how often it is updating? A small number of records would likely skate under the daily quota even if you were looking at it a bunch.

[ScottWorld]

@Bill.French

I think yours will re-trigger if you render the data in the UI. As I understand Airtable’s behaviour, if you never look at it, you’ll be fine. I have not created a test-bed to validate this.

I just tested this multiple times on my end.

I kept refreshing my Airtable database, I kept opening up my Maps block fullscreen and closing it again, I kept refreshing my web browser, I kept scrolling through the Maps block and zooming in & out, and I even double-clicked on several of the dropped pins on my Maps block.

Then, I checked my Google Maps API usage: 0 requests.

Then, I went back and added a brand new address into my Airtable base. I went back to the Maps Block and saw that the brand new pin was dropped on the map.

Then, I checked my Google Maps API usage: 1 request.

So, this is working perfectly on my end, and I am indeed using a formula field for my Address.

One thing that I didn’t check, and this is just out of curiosity on my end, is whether the Google Maps API was triggered as soon as I typed in the new address, or not until I scrolled to the pin on the map itself. I’m running out the door now, so I’ll have to check this curiosity later.

[ScottWorld]

Wait, I’m back — I just couldn’t let my curiosity wait until later. Hahaha.

Okay, NOW I’m seeing the same problem as @Anandaroop_Roy!! This is so weird! I added ANOTHER new record, and this time around, I got 13 additional Geocode requests on my Google Maps API. This number 13 has nothing to do with the number of records in my Airtable base, so not sure where that number came from.

To be even more exact, I got:

13 additional Geocode requests.
2 additional Maps JavaScript API requests.

For a grand total of 14 geocode requests today and 3 Maps JavaScript API requests today.

Very strange!!

[ScottWorld]

Okay, I have a test table with only 4 records in it. I added a 5th record, and I got:

5 additional Geocode requests.
15 additional Maps JavaScript API requests.

And then, opening & closing the Maps block seems to OCCASIONALLY trigger 1 additional “Maps JavaScript API Request”, but not always. Although I can’t tell why or when.

Is this a formula issue? Or is this just how the Maps block communicates with the Google Maps API? Would the problem go away if the address field was 100% static?

This behavior really should be modified by Airtable — people can’t have Airtable chewing through their API limits like this.

[Kamille_Parks]

@Bill.French

However, like all feature requests that deal with a single missing dimension

And of course I prefer feature requests that tackle things from a big picture. This is a fundamental issue regarding an official, “standard” block: if you don’t have an address/set of coordinates in a single field it doesn’t work at all. That is an elimination of choice in an instance users would likely have had input values in separate fields, as is the case here as well as any time I’ve personally used the block. Map Blocks already do their own address parsing but at the roll of the dice of whether the user has put a full address in a compatible order. Other blocks already allow the user to set their own field mapping. The more consistent standard blocks are with each other, the better.

If the user already has “real” data, why is the Block designed to fit the data into a narrow box? All this discussion of when and where a formula should fire, why make them use a formula in the first place? Other blocks don’t and neither should Map.

The next time AT develops a block, I want them to ask themselves “Will this work right away, or do people have to do their own cleanup first? Can they do cleanup within the block itself?”

[Kasra]

@Anandaroop_Roy sorry about this, can you email support@airtable.com so we can investigate further?

[Bill.French]

We’re getting confused here - at least I am. Let’s summarize the points.

@Kamille_Parks

If the user already has “real” data, why is the Block designed to fit the data into a narrow box?

That’s just it; the user has real data distributed across many fields. Combining them via a formula field triggers the issue that @ScottWorld just verified.

I’m simply suggesting (as part of the remedy) that the user create a real field with the combined values. How he might achieve that requires (in my view) either an external process using the API, or an internal process using a script block.

The added observation is simply that this is one additional cul-de-sac that we see users driving into with formula fields. Formula fields tend to surprise users; this is just one of many and this one is a bit more egregious in that it can actually cost money for unsuspecting users.

If you believe the Map block itself is a (or the) problem, that’s a deeper dive that I’ll leave you and @Kasra to investigate; I’m simply offering a remedy to the user while tossing another log on the formula fields can create issues crusade.

Thanks @ScottWorld for verifying the issue - very helpful to know.

Last Point

When it comes to geo-location and any process associated with location science, I always help my clients avoid any features that magically geo-encode natively because I’ve learned that so few vendors design their solutions to be considerate of encoding quotas and costs. None of them has actually designed systems that allow you to control processing to stay within the daily encoding limits for free or any pricing tier. Given that Airtable is a low-cost data management solution, it’s in their best interest to defend customers from potential wasted encoding charges. Just sayin ’ …

[Kamille_Parks]

@Bill.French

Combining them via a formula field triggers the issue that @ScottWorld just verified.

Exactly. Separate street address, city, state, etc fields are common especially for records intended to be mapped, and the Maps block should have been designed to accommodate that. We shouldn’t have to use a formula in the first place.

[Bill.French]

@Kamille_Parks

and the Maps block should have been designed to accommodate that.

Perhaps - I certainly agree that had Maps Block provided one additional feature, this issue may never have arisen and I would have 90 minutes of my day back. Until, of course, the future Data Science block makes the mistake of doing this as well.

If you follow that logic, you are setting up the Maps Block developers and all other blocks developers who might perform API calls for whatever purpose across an unimaginable array of possible services - some with charges - to be aware that formula fields must be treated differently and/or that their solution mustn’t use formula fields and must, therefore, accommodate concatenations outside of the database.

Furthermore, the blocks developers would also need to test the data types of every field and they would have to support a collection of fields, adding further complexity to the configuration of the block itself. This would essentially require custom blocks to provide the formula layer externally; that’s a big ask.

This approach pushes the responsibility to address the wonkiness of formula fields from Airtable itself down into the hands of every custom script block maker. That seems like a bad policy following on the heels of an incomplete formula design. It’s a policy that places an unreasonable technical constraint and deep knowledge on the third-party community of developers. It’s simply added friction that will likely result in this issue popping up repeatedly while creating risks for users when someone building a block didn’t get the “memo” that formula fields sometimes go into Mad Max mode.

Architecture and design choices have deep consequences. I would recommend Airtable do exactly the opposite - remedy this issue at the head, not the tail because there are hundreds of tails and one head.

[Kamille_Parks]

@Bill.French

to be aware that formula fields must be treated differently and/or that their solution mustn’t use formula fields and must, therefore, accommodate concatenations outside of the database.

… I don’t see how you got such apocalyptic implications of switching the user interface of the Maps Block from a single field input to potentially multiple field inputs to match the interface of other existing blocks. Page Designer gets UI/functionality updates out the whazoo that only apply to that block, there’s absolutely no reason why Maps settings can’t get a new input/button or two. Nowhere did I say formula functionality shouldn’t be improved, but fixing formulas won’t address my complaint at all. Its a fairly simple request with exactly zero impact on other developers/blocks/scripts/API; Its not going to trigger some coding revolution by third-parties to unilaterally abandon formula fields. Not every complaint intrinsic to an individual block should have to be connected to some grand, all-encompassing api complaint to get addressed or even mentioned.

Its not like Airtable was going to go “Well we tweaked some design here, now we’ll never ever ever have to improve our formulas/scripting/API, time to shut down that department” lol