Creating a hyperlink from two fields

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.

1. Use the scripting block (or actions) to populate a field of type URL with the concatenated data from other fields.
2. 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.

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?

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 -

1. Use the scripting block (or actions) to populate a field of type URL with the concatenated data from other fields.
2. 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.

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

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

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.

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!

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.

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"
    })
  }
}

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.

Is it possible those don’t have data in the URL field?

Are you looking at a sorted view vs the unsorted table?

Yes your absolutely right … I had a few Curators with our url data. Which was only a temp thing. In future this will be a required field so this shouldn’t be an issue … I’m hoping!

Am cleaning up that part of the data and will re-run … in theory I guess it should all complete and run ok.

Thanks again for all your help!

One thing is missing: the await keyword in front of the update command. Not waiting for each record to update before attempting to update the next record is likely why only a handful of records were updated. Try this modification:

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) {
    await tb.updateRecordAsync(record, {
      "Curator with Link": "[" + text + "](" + url + ")\n"
    })
  }
}

The downside to this method is that the total update will take a long time. Waiting for records to update means that only 2-3 records per second can be updated. A better option is to collect all updates into an array, and update them in batches of 50. Here’s the change to make that happen:

let tb = base.getTable("Curators");
let query = await tb.selectRecordsAsync();
let updates = [];

for (let record of query.records) {
  let url  = record.getCellValue("Curator Link");
  let text = record.getCellValue("Curator Name");
  if (url) {
    updates.push({
      id: record.id,
      fields: {
        "Curator with Link": "[" + text + "](" + url + ")\n"
      }
    });
  }
}

while (updates.length > 0) {
  await tb.updateRecordsAsync(updates.slice(0, 50));
  updates = updates.slice(50);
}