Formulas and URLs

[Scott_Simmonds]

I have a DB with names, zip codes, etc. It dawned on me that with this info I could create links to services making the data more useful.

Example, I have zip code so I can create a link to be able to see the weather at a client’s city so we can talk about the rain…

“https://www.wunderground.com/weather/”&Zip does this very well - where Zip is my field for zip code.

Trying to set up a similar link to search Google News - see if my client has been in the news lately.

The URL https://www.google.com/search?q=“davis+love” returns all the Google listings for “Davis Love”

When I try to build the formula I get errors.

“http://www.google.com/search?q=”&{Co Name} only recognizes the first word in the text string. So if my prospect is Davis Love I only get the google search for Davis.

AirTable is truncating the URL at the space between “Davis” and “Love.”

The formula generates: http://www.google.com/search?q=Davis

The issue is not the + sign. If you put the URL into your search bar without the + Google figures it out for you.

I have tried adding quotation marks and slashes. Nothing seems to help.

Advice, please?

[Scott_Simmonds]

Here is another example. This one returns a map showing the location of the client by zip code.

“https://www.google.com/maps/place/”&Zip

Where Zip is my field name for Zip Code.

[W_Vann_Hall]

With your Google News example, you’ll need to URL-encode your client’s name before appending it to the Google URL. Assuming {GoogleURL} = ‘https://www.google.com/search?q=’, either of the following formulas would work:

{GoogleUrl}&SUBSTITUTE({Co Name}," ","+")
{GoogleUrl}&SUBSTITUTE({Co Name}," ","%20")

The issue is the plus sign (’+’): When you enter it into the search bar, your browser encodes any embedded spaces to either ‘+’ or ‘%20’, depending on the browser, before transmitting the URL to the website. With formula-generated URLs in Airtable, you have to define the substitution explicitly.

(FWIW, the formula to create a URL to search Google News for client info would be either of these:

{GoogleUrl}&SUBSTITUTE({Co Name}," ","+")&"&tbm=nws"
{GoogleUrl}&SUBSTITUTE({Co Name}," ","%20")&"&tbm=nws" )

FWIW#2, there are a number of other, less-commonly encountered characters that need to be encoded before they can safely be used in a URL. You’ll probably not encounter any of these, but for completeness’s sake I’m including a link to my recent post on the Airpress support forum. (Airpress is Chester McLaughlin’s WordPress-to-Airtable plugin.) It contains a list of reserved characters and their encodings, along with one seriously ugly nested SUBSTITUTE() call to encode all but a few.

[Scott_Simmonds]

Perfect. Thanks. I’m not a coder and am learning this stuff as I discover my needs. Thanks for teaching.

[W_Vann_Hall]

Glad to help. I’m still on the uphill side of the learning curve, myself; I use the Support forum as sort of a crowdsourced Airtable exercise book of problems to solve. :winking_face: