Re: Creating a hyperlink from two fields

Nick_Truch · ‎Feb 04, 2021

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:

Formula field: doesn’t seem to like html so I can’s put … <@ href="{Product Link}" target="_blank">{Product Name}</@> … (I’ve put @ instead of a so you can see the code)
Button: I can select the url field but there no way to add a field variable to the hyperlink/button text

Anyone else having this issue?

Many thanks
Nick

Bill_French · ‎Feb 04, 2021

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.

Use the scripting block (or actions) to populate a field of type URL with the concatenated data from other fields.
Use a long text field with Markdown enabled to create a concatenation of Markdown code that exposes a link in the display.

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.

Nick_Truch · ‎Feb 04, 2021

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:

I’ve created a ‘Long Text’ field and enabled the ‘Enable rich text formatting’ option

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?

Bill_French · ‎Feb 04, 2021

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 -

Use the scripting block (or actions) to populate a field of type URL with the concatenated data from other fields.
Use the scripting block and a long text field with Markdown enabled to create a concatenation of Markdown code that exposes a link in the display.

Nick_Truch · ‎Feb 04, 2021

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

Nick_Truch · ‎Feb 04, 2021

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

Bill_French · ‎Feb 04, 2021

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.

Nick_Truch · ‎Feb 04, 2021

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!

Bill_French · ‎Feb 04, 2021

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.

Nick_Truch · ‎Feb 04, 2021

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.