Mar 02, 2020 03:59 PM
I order to properly store geographic coordinates in Airtable, I need to create a formula to transform a field like “Address: Province: Country: Longitude: -1.4934866Latitude: 12.3614676”
into “12.3614676, -1.4934866”
I have tried a few formulas but I systematically failed.
Anyone could give an hand on this please? I would really appreaciate.
Warm regards from Burkina Faso
Mar 06, 2020 04:14 PM
Hey Franck – this is probably a bit beyond what’s possible with Airtable’s formula field. You might be able to do something along these lines with Airtable’s newly-launched scripting block (Airtable.com/scripting), though writing such a script is a bit beyond the scope of support we’re able to offer here on the forum.
Mar 10, 2020 08:32 AM
Thank you @Pete for the advice. I am not an expert, but I’ll try to dig into the scripting block.
Have a great day!
Mar 10, 2020 08:51 AM
The only practical approach is with script. Even in Script Block you be unable to achieve this (natively) because you need the geo-location resources and the only way to do that is to call out to a service for this data.
We’ve built a number location-sciences integrations with Airtable using both API services (automated) and Script Blocks (which require manual invocation). Overall, our clients insist on automated solutions that simply watch for records lacking lat/lng data and our processes automatically add the properly lat/lng values in the background.
Mar 10, 2020 09:23 AM
Hello Bill, thanks for the comment. To give more background, I am collecting the string "Address: Province: Country: Longitude: -1.4934866Latitude: 12.3614676” from a Jotform form fetching the Long/Lat coordinates from the form user. Once the info is collected, I push it to Airtable. But as you can see, it doesn’t appear in an exploitable format.
Mar 10, 2020 09:48 AM
Ah, I should have read the entire thread more carefully. My bad. So, this is where both @Pete and I have failed you. :winking_face:
Parsing this data into adjacent formula fields (using native Airtable formulas) or into adjacent numeric fields (the approach I would typically chase) using Script Blocks are possible.
I pass this back over to @Pete to help you write a formula that extracts the lat/lng from the string example. It appears to have all the delimiters required - someone simply has to do the legwork to shape the formula.
The process is simple but very tedious using formulas.
Mar 11, 2020 10:23 AM
Thanks a million Bill! I’ll try to get some help to design the formula properly. Have a great day and thanks again.
Mar 11, 2020 10:51 AM
Mar 11, 2020 05:29 PM
@Bill.French – I was impressed by your solution so I marked it as solved :man_shrugging: , but we haven’t provided additional assistance here.
Mar 11, 2020 07:56 PM
Well, thanks! But it wasn’t a solution.
I simply demonstrated an approach that should be relatively easy to implement using native formulas. The customer still needs a solution and this is the perfect requirement to broadly show why a no-code approach is possible in Airtable.