Scripting: creating hypertext links

[Dan_Billin]

Just added the Scripting app and am trying to write my first script: to create a new field of hyper-linked text (“Opportunity”) from existing text (“Program”) and url (“Website URL”) fields in my table. I found this suggested solution and adapted it to my table like this:

The problem: it works on only about 10 percent of my records:

I’d appreciate any suggestions for how to make this work for all records, or an alternate method. What is the significance of “selectRecordsAsync” showing as struck through after I run the script? Is this a problem with the script not waiting for calculations? This is my first experience with Javascript.

[Justin_Barrett · Accepted solution]

Welcome to the community, @Dan_Billin! :grinning_face_with_big_eyes: The reason for the strikethrough on selectRecordsAsync can be seen by hovering over that method name. Long story short, that style of calling the method without any arguments—meaning that you want to select all fields for all available records—is being deprecated. After a while, that argument-less way of calling that method won’t work. For now that strikethrough is there to remind users to begin adopting the preferred (soon to be required) syntax, which includes an object where you specify a collection of fields that you want to specifically collect when retrieving those records. Only those listed fields will be available for later operations on that query. (There are other things that can be passed via that same object; refer to the API docs for more info.)

For example, in your code you’re only accessing the {Website URL}, {Program}, and {Opportunity} fields, so you could modify that line to look like this:

let query = await tb.selectRecordsAsync({fields: ["Website URL", "Program", "Opportunity"]});

Dan_Billin:

The problem: it works on only about 10 percent of my records:

That’s because you omitted the “await” keyword before the call to the updateRecordAsync method. Long story short, all methods ending in “Async” must be await-ed to function properly.

On a side note, there’s a more efficient way of updating records in bulk instead of updating them one at a time, which would involve using the updateRecordsAsync method instead, but that takes a bit more setup. Search the forum and you’ll find examples of that method in use, or ask and we can guide you if needed.

[Dan_Billin]

Thanks a million! I’ll work on this.

[Dan_Billin]

Thank you! Two changes made this work: 1) specifying an array, and 2) await-ing the update. It took less than a minute to process 178 records in the dummy base where I’m testing this. Here’s the updated script:

This gets me to a new problem: this update is static. Can I make it automatic, so the hyperlinked field (“Opportunity”) is populated whenever a new record is added (rather than having to manually run the script every time I add records)? I’ll look into updateRecordsAsync to see if I can write a more efficient script.

[Justin_Barrett]

Dan_Billin:

It took less than a minute to process 178 records in the dummy base where I’m testing this.

It could take a matter of seconds with the optimization that I mentioned earlier. If you run into any hiccups working it out, just holler.

Dan_Billin:

Can I make it automatic, so the hyperlinked field (“Opportunity”) is populated whenever a new record is added (rather than having to manually run the script every time I add records)?

Definitely, and you wouldn’t even need a script to do it. If it can be safely assumed that new records will always have the {Program} and {Website URL} fields filled, you could build an automation that triggers on a new record, and then update that same record using an “Update record” action to fill the {Opportunity} field similar to how the script is doing now: by inserting the contents of those two fields into the proper markdown syntax.

However, that will only work if the data in the first two fields exists at the instant that the record is created (i.e. via a form, or perhaps some other process that creates the record). If you are making new records manually and manually entering the data for those two other fields, the new-record trigger will fire while the fields are still empty.

In short, be careful to pick a trigger that will only fire once those fields actually contain data. :slightly_smiling_face: