Can’t use Calculated Column to generate a URL?

ANYBODY WHO HAS WORKED WITH SHAREPOINT ONLINE will know that Microsoft love to turn off functions we rely on. A recent casualty was the facility to use List Calculated Columns to concatenate a URL to a customised Display or Edit Form.

Oh, how I love to concatenate. And how sad I was when Microsoft decided
they didn't like me concatenating links.

How we would have done it in the past would be to create a Calculated column in a list, then add a formula like this:

=CONCATENATE("<A HREF='/sites/SiteName/Lists/ListName/DispForm00.aspx?ID=",[ID]'>Display Form</A>")

Or if you wanted to be really smart, you could include a redirect to take your user to a particular destination after they’d finished with the form, like this:

=CONCATENATE("<A HREF='/sites/SiteName/Lists/ListName/DispForm00.aspx?ID=",[ID],"&Source=https://xxx.sharepoint.com/sites/SiteName/Lists/ListName/'>Display Form</A>")

The problem is that Microsoft have now disabled that functionality, so that while the Calculated column will compile the link, the list will display the HTML line of code, and not a link as you intended.

I went trawling around Google, looking for a workaround for this “undocumented feature” and came up empty. There’s quite a few folks who suggest many different JavaScript and JQuery solutions, but I couldn’t get any of them to work. JavaScript is okay and can solve some of your problems in SharePoint, but I can’t help feeling it’s a bit of a hack. Far better to use in-built SharePoint technology to solve SharePoint problems where possible.

So I resolved to come up with a workable solution myself, something simpler than fiddling around with a JavaScript function that would magically transform a string of HTML into a working link.

Everything was pointing to a workflow. (You can also use a Flow, which I've documented at the foot of this posting.)

I set one up to copy the calculated HTML code into a simple text column, but of course that didn’t work.

My next attempt was to copy the concatenated string into a Hyperlink column. That kind of did work, but displayed both the link URL and the link text as the full line of HTML … and it seemed to be clickable, but it didn't quite work as I thought it would.

Even though it appeared to produce a functioning link, the "gotcha"
was that the call to the item's ID didn't work, so I had a rethink.

But that roadblock led me to a simpler, more elegant solution anyway ... use a Workflow Variable to compile and copy the URL string into a Hyperlink column.

From there, it was a straightforward task to fire up SharePoint Designer and add a simple workflow to copy the contents of a Variable to your Hyperlink Column.

In your new Workflow - I used a 2010 Workflow - under Actions, select "Set Workflow Variable".

Add your first action ...

In the following window, click on Workflow variable and select 'Create a new variable". Give a useful name and select "String" for the Type. The click on Value, and select the ellipsis to open the String Builder.

Make sure you set up the URL string in a format
that the Hyperlink column will accept.

The string should be in this format:

https://xxx.sharepoint.com /sites/SiteName/Lists/ListName/DispForm00.aspx?ID=[ID]&Source=https://xxx.sharepoint.com/sites/SiteName/Lists/ListName/, Display Form

Replace the "[ID]" in the above string with the "Current Item:ID" value from the Add or Change Lookup function.

The comma and the word space after the URL (but before the link text) is vital to ensure the string transfers to the Hyperlink field correctly.

I also set up my Workflow to check whether the Hyperlink Column was populated then, if not, copy the contents of the Variable into the Hyperlink Column, like this:

Having the Condition  place to check whether the Hyperlink column is populated or not
saves triggering the whole workflow process unnecessarily.

Then set the Workflow to trigger when a new item is created and that should be it.

This is how you want your Hyperlink column to look after the Workflow has done its job.

USING POWER AUTOMATE WITH SHAREPOINT ONLINE

So, someone asked in the comments if I'd gotten this to work with Flow ... and I can now reply, "Yes, I have."

I've had so much work converting all my existing 2010 WorkFlows to Flow that I haven't had any time to do bits of research explicitly for The SharePoint Hive, but I've just had to wait until one of my remediations called for me to do a Flow version of a WorkFlow action before posting the solution here.

It turns out that populating a SharePoint list URL field is a little fiddly in Flow, but works fine once you have it set up. Here's how ...

First we're going to add in an action - Send an HTTP Request to SharePoint. The way I used this, it was the only action in the Flow, as I needed it to create the appropriate dynamic link each time I added an item to a list. But you can use this method in any Flow where you need to create a dynamic link on the fly.

This Action is already set up, but check below for the changes you should make and any relevant code ...

So, first add your site address to the first field. Set the Method to POST. In the Uri field, add this:

_api/web/lists/GetByTitle('Reward Training')/items({ID})

... but make the {ID} bit a Dynamic Content call to the item ID. Reward Training is the name of my list

Next, click on the icon to the right of the Headers field and add this script:

 {

   "Content-Type": "application/json;odata=verbose",

   "X-HTTP-Method": "MERGE",

   "IF-MATCH": "*"

 }

The in the Body field, add this script:

{'__metadata': {'type':'SP.Data.NAMEOFYOURLISTListItem'},'NameOfColumn':

 {'Description': 'Book this slot',

 'Url': 'https://YourSite.sharepoint.com/sites/eo-event-registrations/Lists/Reward%20Training/EditForm.aspx?ID=[ID]&Source=https://YourSite.sharepoint.com/sites/eo-event-registrations/Lists/Reward%20Training/'}

}

It took me a couple of goes to understand what was going on here. I hope I've made it clear ... but the bit after SP.Data is the Name of Your List + "ListItem", no spaces. If your list name has spaces (best avoided, but on this occasion I didn't listen to my own advice) you'll need to use _x0020_ for the space.

(Weirdly, you can use a regular word space in the Uri field, and a %20 in the link you paste into the Body field. You have the whole set with this Flow Action!)

My link is a call to an Edit Form. This can be the standard out-of-the-box Edit Form or something you've customised in Power Apps - makes no difference.

And the Description value "Book this slot" is what will appear in the list as the link text.

I also added a "Source" reference so that users are taken back to the main List page once they've finished with the EditForm, but that's not mandatory.

I hope this has helped someone.