Apr 20, 2020 09:44 AM
Hello, I have a formula for UTM and its pulling from the other columns in the sheet, but for some reason, it is not hyperlinking the entire URL. I not sure why, But I have doubled checked the formula to make sure there are no spaces. I am new to Airtable formulas so I am not sure what I am doing wrong. Thanks!
Solved! Go to Solution.
Apr 22, 2020 01:48 PM
It’s working on this copy of the base you posted. I’ll keep that base up for a week so you have time to duplicate it and poke around.
Here it is again in case a typo was introduced:
SUBSTITUTE( LOWER( TRIM({URL}) & "?utm_medium=" & TRIM({Medium}) & "&utm_source=" & TRIM({Source}) & "&utm_campaign=" & TRIM({Campaign Name}) & "utm_content=" & TRIM({Content}) )," ","-")
But every now and then I have to write a formula by hand to get Airtable to recognize some of the field values.
Best of luck!
Apr 20, 2020 12:27 PM
Please provide: (1) the link text you’re seeing (2) what you would like to see instead (3) the formula (4) details about any fields the formula is using.
Apr 20, 2020 12:46 PM
(1) this is what I am seeing(only half of the link is highlighted):
**https://www.understood.org/pages/en/families/?utm_medium=paid**
&*utm_source=facebook&utm_campaign=this-is-a-testutm_content=post-w-image`
(2)I would like for the entire link to be highlighted.
(3) this is the formula I am using: LOWER(SUBSTITUTE(URL & ‘?utm_medium=’ & Medium & ’ &utm_source=’& Source &’&utm_campaign=’ &{Campaign Name} &‘utm_content=’ & Content))
Apr 20, 2020 01:48 PM
Not sure what SUBSTITUTE is supposed to be doing. Try one of these:
URL & ‘?utm_medium=’ & Medium & ’ &utm_source=’& Source &’&utm_campaign=’ &{Campaign Name} &‘utm_content=’ & Content
LOWER(URL & ‘?utm_medium=’ & Medium & ’ &utm_source=’& Source &’&utm_campaign=’ &{Campaign Name} &‘utm_content=’ & Content)
If that doesn’t work, please try to re-communicate what you’re saying in (1). Your formatting is confusing.
Apr 20, 2020 05:15 PM
Hey, Thank you for all your help! unfortunately the formal didn’t work. I was trying to say in for question 1:
the URL the formula is producing isn’t hyperlinking. half of the URL is a hyperlink while the other isn’t. At first I assumed it there was a space in the formula but after rewriting the formula several times there doesn’t seem to be any spaces. I am not sure what I am doing incorrectly.
This is how it looks:
https://www.understood.org/en/friends-feelings/empowering-your-child/building-on-strengths/5-steps-f... &utm_source=fb-community&utm_campaign=this-is-a-test, covid-apr20-wp&utm_content=post-w-image
Apr 20, 2020 09:27 PM
Welcome to the community, @Harmony_Gilliard! :grinning_face_with_big_eyes: Actually, there is a space in your formula, because there was one in the version that @Zollie created above. In yours, though, it’s “hidden” by the line break in your formula entry field. If you were to copy that full formula into a text editor, you’d see the space.
To nix it, place your cursor immediately after the quote at the end of that first line, and press the Delete key. That quote will move to the following line because the space has been removed.
Apr 21, 2020 08:09 AM
thank you for noticing this, I went ahead and fixed it but unfortunately, it still didn’t fix the problem could there be anything else wrong with the Base or the formula? I could send you my Base if you would like to take a look at it.
Apr 21, 2020 09:07 AM
Could you post some of your actual values?
In the screen shot in one of your posts it looks like there are two question marks in the URL.
?_?
Apr 21, 2020 10:14 AM
this is a link to one of the records in the base https://airtable.com/tblxBdnafDwTpCxe0/viwf69BVDBvVJsdQd/recS6nijtkmxtYe71
Apr 21, 2020 10:33 AM
@Harmony_Gilliard That link doesn’t work for users you’re not paying for. @kuovonne is just asking for values that the formula is spitting out. It also might help to share example values for the columns your formula depends on - so URL, Medium, Source, Campaign Name, Content. If typing is too hard, create a shared view and post that link.
Apr 22, 2020 09:01 AM
@Zollie Typing is not too hard but rather time-consuming as I have been trying to fix this problem for about a week, with no solution. I am sure you could imagine how frustrating that is, especially on a tight deadline. Heres a read-only link : https://airtable.com/shr4GMQjgVpjd968A
let me know if it works. Thanks.
Apr 22, 2020 10:42 AM
Yes that can be frustrating. Sorry if my remark came off as flippant. The shared view helped a great deal.
The additional space characters are being introduced from multiple places:
(1) The formula itself
Whoever wrote it left some spaces in some of the strings (a string is anything with quotes around it). I’m not sure if those were intentional or not, but here it is without those additional spaces:
LOWER( {URL} & "?utm_medium=" & {Medium} & "&utm_source=" & {Source} & "&utm_campaign=" & {Campaign Name} & "utm_content=" & {Content})
(2) Single select fields that the formula relies on
Both “Source” and “Medium” have space characters added to some of the values - for example, “twitter” is "twitter ". There could be other fields with the same issue.
You could either keep a closer eye on any fields that the formula relies on, or build your formula to correct for user error. To remove spaces, you’d use TRIM, which removes leading or trailing space characters, or you could use a function like SUBSTITUTE to automatically replace any space characters with a hyphen (as you have with the “Campaign Name” field).
Examples:
TRIM({Source})
"Twitter " => “Twitter”
SUBSTITUTE({Content}, " ", “-”)
“Post With Image” => “Post-With-Image”
Putting it all together, you’d end up with this:
SUBSTITUTE( LOWER( TRIM({URL}) & "?utm_medium=" & TRIM({Medium}) & "&utm_source=" & TRIM({Source}) & "&utm_campaign=" & TRIM({Campaign Name}) & "utm_content=" & TRIM({Content}) )," ","-")
Apr 22, 2020 01:37 PM
Thank you @Zollie this is very helpful and informative I really appreciate it! I hate to be a bother but it’s still not working it’s telling me the formula is invalid which is strange. I am really not used to Airtable formulas. Heres what I am seeing :
Apr 22, 2020 01:48 PM
It’s working on this copy of the base you posted. I’ll keep that base up for a week so you have time to duplicate it and poke around.
Here it is again in case a typo was introduced:
SUBSTITUTE( LOWER( TRIM({URL}) & "?utm_medium=" & TRIM({Medium}) & "&utm_source=" & TRIM({Source}) & "&utm_campaign=" & TRIM({Campaign Name}) & "utm_content=" & TRIM({Content}) )," ","-")
But every now and then I have to write a formula by hand to get Airtable to recognize some of the field values.
Best of luck!
Apr 22, 2020 01:54 PM
It looks like there are a lot of curly quotes “ ”
in your screen captures. Change all of them to straight quotes "
.
This is a very common issue when copy/pasting formulas from the community.
Apr 22, 2020 02:04 PM
Thank you its working now! @Zollie I really appreciate all of your help