Getting and #ERROR! when using ENCODE_URL_COMPONENT() on a lookup field of a text field. Can the formula be used on lookups?
Solved
#ERROR! when using ENCODE_URL_COMPONENT() on lookup?
Best answer by ScottWorld
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.
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.
+35Hi 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.
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.
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.
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.
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?
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?
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.
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.
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.
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.
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.
+18Found 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.
+2Found 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.
You are my hero @Devon_Godfrey 😄 Thanks so much for this.
Login to the community
Enter your E-mail address. We'll send you an e-mail with instructions to reset your password.