Save the date! Join us on October 16 for our Product Ops launch event. Register here.
Feb 04, 2021 01:23 AM
Hi,
Firstly … sorry for such a basic question but I’ve been through pretty much all the options a few times and still a blank look on my face!
I’m planning to migrate over from Knack.com, here it’s easy to set a url column of data and change it to a hyperlink so it shows another field’s data as the hyperlink text. So for example you have a column of Product Titles and when clicked they go to the url in a new window. Simples! Also their formula field accepts html so you can create <a href … code with the other field variables.
But … how do you do this in AirTable. I’m hoping there is a way as I was under the impression AirTables is an upgrade and more developed. Fingers crossed!
Here is what I’ve tried:
Anyone else having this issue?
Many thanks
Nick
Feb 04, 2021 04:46 AM
Hi @Nick_Truch, and welcome to the community!
Correct, and the fact that Knack allows this exposes a potential security issue with that platform. Airtable doesn’t support this because they’re sensitive to the security of the data. However, there are at least two ways to overcome this limitation.
I prefer #2 because it allows you to pretty up the display - i.e., instead of showing a URL, a link that looks and works more like an anchor tag.
Feb 04, 2021 06:14 AM
Hi Bill,
Thank you so much for your message and solutions. The option 2 sounds like what I need.
Please can I check the correct steps:
Where do I enter the markdown code so that the cells are auto-populated with the mix of content form the Title field and the Url field? Are we talking about the same thing?
Feb 04, 2021 06:29 AM
Option #2 also presupposes the use of a script to fabricate the markdown for the targeted long text field. Unfortunately, formulas cannot exist in fields like long text fields (as mentioned here), so the only way to automatically populate a long text field with concatenated data is via a script process external to the field itself.
Ergo, my advice should have been stated more clearly -
Feb 04, 2021 06:31 AM
Hi Bill,
That’s great, thanks for clarifying. I’m off to now watch a few videos about Scripting Blocks so I’m sure I’ll have it up and running soon!
Cheers
Nick
Feb 04, 2021 07:27 AM
Yikes … looking in the Script Block and the amount of custom code needed to create a simple hyperlink, using text from another field, is utterly bonkers!
I think I might just use a button for now … and wait for Google Tables to be released to the UK!
Thanks for your help
Feb 04, 2021 07:57 AM
Your fear of javascript is getting the best of you. This example uses just five lines of code to transform a URL field into a linked long text field with Markdown. The only part missing is the concatenation of other fields to get you the URL.
Bear in mind, this example applies the linking outcome to every record in the table; not just one record, so it’s elegant and very efficient.
That will be a long wait I think.
Feb 04, 2021 09:43 AM
Hi Bill,
Ha ha … yes you are right and I’m afraid even that’s a bit beyond me. I did try it but not entirely sure what it’s doing.
I did however try and almost understand what’s going on here with this script example, posted here, but couldn’t get it to populate the Long Text field. It may not be the best way compared to your script … but with the below code that I tried I can see the field names it’s calling and concatenating.
Here is the code I tried:
let tb = base.getTable(“Curators”);
let query = await tb.selectRecordsAsync();
for (let record of query.records) {
let url = record.getCellValue(“Curator Link”);
let text = record.getCellValue(“Curator Name”);
if(url) {
tb.updateRecordAsync(record, {“Curator with Link”:[${text}](${url})
})
}
}
Unfortunately when I hit run nothing happened and no error messages. Just to clarify:
‘Curators’ is the name of the table
‘Curator Link’ is the name of the url field
‘Curator Name’ is the name of the text field - the bit I’d like to be hyperlinked
‘Curator with Link’ is the Long Text field where I’d like the data to be auto populated for each record
Is there an obvious newbee error is that script that I’ve missed? I think if it’s more complicated I might just go with the button.
Thanks so much for your help!
Feb 04, 2021 10:30 AM
Not sure - but I would try this:
let tb = base.getTable("Curators");
let query = await tb.selectRecordsAsync();
for (let record of query.records) {
let url = record.getCellValue("Curator Link");
let text = record.getCellValue("Curator Name");
if (url) {
tb.updateRecordAsync(record, {
"Curator with Link": "[" + text + "](" + url + ")\n"
})
}
}
IMPORTANT: I believe the added newline character (\n) at the end of the field value is critical.
Feb 04, 2021 12:38 PM
Ah fantastic … thank you so much for checking the code. It’s so almost working ;o)
When I hit run it’s populated 15 rows, in a random order, and missed the rest, which is about 435 other records. Mmmm. I’ll have a dig around the data to see if something might be stopping it, but strange that it would do the 15 in random order.