Help

Re: #ERROR! when using ENCODE_URL_COMPONENT() on lookup?

Solved
Jump to Solution
2160 0
cancel
Showing results for 
Search instead for 
Did you mean: 
Jgray
5 - Automation Enthusiast
5 - Automation Enthusiast

Getting and #ERROR! when using ENCODE_URL_COMPONENT() on a lookup field of a text field. Can the formula be used on lookups?

1 Solution

Accepted Solutions

It’s probably not working because a lookup field is an array, and Airtable has very limited array support.

What I would do is encode the value of that text field back in the other table, and then have your lookup bring in the already-encoded value into the current table.

See Solution in Thread

12 Replies 12

Hmmm… good question (I guess).

I hate it when support people ask me “Why would you want to do that?”, and yet – here’s a good example of why someone might ask this question.

I’m sure you have a good reason, but I would love to hear it anyway - just curious.

As far as a lookup function is concerned, whatever is in the string is what it will attempt to match on. If the contents of that field is also URL encoded, I suppose it should work as I can’t imagine why Airtable would sanitize lookups in any way.

AirJim
4 - Data Explorer
4 - Data Explorer

Hi Jgray, did you ever find a solution to this issue? I’m having the same problem. Thanks.

It’s probably not working because a lookup field is an array, and Airtable has very limited array support.

What I would do is encode the value of that text field back in the other table, and then have your lookup bring in the already-encoded value into the current table.

But won’t that create human-unfriendly values in the table? (i.e., unreadable?)

I’m still curious - after almost a year - why URL-encode a lookup field? Is it that the lookup field IS a URL? Or is it that the lookup field is a component of a URL that’s constructed elsewhere? This question matters a lot in the advice we might offer.

Over the years, I’ve noticed that the later any content is transformed for URL use, the better.

Jgray
5 - Automation Enthusiast
5 - Automation Enthusiast

Sorry for slow reply on why :slightly_smiling_face: I thought I had responded.

We were looking up information on a record from a linked table and trying to eventually end up with some html to push out in an email.

I believe we encoded in a new field in the source table and then looked up.

SendGrid block doesn’t handle generating html this way (seems to only like when coded in block), so this is then pulled into integromat to be processed and emailed.

Okay, so that’s a little different than URL encoding, right? The function in the title of this thread is designed to ensure that a URL with characters that are unsuitable for use in a URL (such as spaces, ampersands, some punctuations, etc) will transform these to allow them to be passed in a URL.

If you need to transform content into HTML, is it HTML entities that are creating issues for your process?

Jgray
5 - Automation Enthusiast
5 - Automation Enthusiast

Sorry, more details - we are generating mailto links that contain very specific details depending on record including an email body that varies by the record it relates to.

Got it. Making a bit more sense. So the using the mailto: protocol you are able to cast the to address, subject, body, etc and any content in that URL must be encoded for the links to work.

Jgray
5 - Automation Enthusiast
5 - Automation Enthusiast

Exactly. Might have been another way to approach, but seems to work. We then pull into integromat to email people with some additional work in integromat on who gets sent what.

Devon_Godfrey
5 - Automation Enthusiast
5 - Automation Enthusiast

Found a solution, use this in your formula: 

 

ENCODE_URL_COMPONENT(ARRAYJOIN( Email, ""))

 

"Email" was the field I wanted to encode into the URL. Arrayjoin basically converts the array into a string. Itl's a lookup field that looks up my contact table, and is on my opportunity table in my CRM.

This works, thank you so much @Devon_Godfrey!

To answer @Bill_French's question, I am using ENCODE_URL_COMPONENT in the formula of a button field to construct a link to an Interface form that is prefilled with data.

airtable782ccc
5 - Automation Enthusiast
5 - Automation Enthusiast

You are my hero @Devon_Godfrey  😄 Thanks so much for this.