Calculated Column: Creating an external hyperlink using mutiple columns

Question

I am attempting to produce a hyperlink using a Calculated column. I have 2 standard text columns which i need to pass via the querystring.

 

This is what i currently have for the formula for calculated column: 

=("<a href='link.aspx?ename=" & [column1] & "&dname=" & [column2]& "'>"&[column1]&"</a>")

 

This is what it places on the page:

<a href='link.aspx?ename=happy&dname=sad'>happy</a>

 

This is producing a text column in the list but it is not a clickable link.

 

Any ideas? Thanks ahead of time,

-Stephen

Answer 1

Sadly, I don't think it's possible to get HTML from a calculated column.  You can always use the hyperlink column which has storage for both the href attribute and the display name of the link (so they can be different if you choose).  Otherwise I think you would have to write code to do something like this.

 

Chase

Answer 2

Anybody have and ideas how to write one so i can get the required functionality?

 

Thanks,

-Stephen

Answer 3

use syntax like that:

=CONCATENATE("http://wknt250:8182/sites/TeamSite1/","Good%20to%20Great%20Intranets")

Answer 4

Hi Mark,

 

Thanks for your example.

 

Unfortunately, your example doesn't work, just showing: "http://wknt250:8182/sites/TeamSite1/Good%20to%20Great%20Intranets" as the field value.

Answer 5

 Here is one method combining calculated columns and a content editor web part:
http://pathtosharepoint.wordpress.com/2008/09/01/how-to-open-hyperlinks-in-a-new-window-or-not/

Christophe

Answer 6

If you use the CONCAT function it'll provide a link (unless the browser being used doesn't recognize hyperlinks automatically).

=CONCATENATE("link.aspx?ename=",[column1],"&dname=",[column2],"'>",[column1])

But if you're wanting a friendly URL title in place of the hyperlink there isn't a way of accomplishing that without a coded solution...

Answer 7

What I normally do:

1. Create an optional hyperlink column in the list
2. Create a worklflow with SharePoint designer that starts on Item Created and with no conditions
3. In the workflow, create an "Store dynamic string" action and store into a new variable.
4. The dynamic string can then consist of your URL with lookups for any parameters you need. (Example: "http://www.google.com?q=[%YourItem:YourField%], Look up in google". The comma and subsequent text makes sure it fits nicely with the Hyperlink data structure, where the latter part of the string becomes the displayed text for the link
5. Then add another action "Set field to value", where you select the field from step 1 and for the value, the variable created in step 4.
6. Job done.

Answer 8

Joern,
Thanks for this useful approach.  It would be great if it could work for my scenario.  However, after following this, my new hyperlink field still displays the URL as the hyperlink text.   Did I not understand a step?  

I have both my 'set variable' and my 'set field' within the same action.  Meaning the set variable is one action and directly following it is the set field action.  Should it be broken out by creating a seperate workflow step?


Below is an example of what my 'Set Variable' and 'Set Field' looks like.   Should I be using quotes?

Store [%List:Field 1%],Go To Form   IN   Variable: Variable Name
    then Set Field 2 TO Variable: Variable Name


I'd appreciate any feedback on your approach.  FYI my URL string "Field 1" is a caclulated column that produces my required hyperlink URL.  So my workflow does not need to string together various parameters. 

Regards, Robert Sandoval

Answer 9

 Here is one method combining calculated columns and a content editor web part:
http://pathtosharepoint.wordpress.com/2008/09/01/how-to-open-hyperlinks-in-a-new-window-or-not/

Christophe

Christoph's suggestion should work fine for you. You don't have to have any workflow or use sharepoint designer for server side solution. You can solve this right from your browser.

You already have the appropriate link in your calculated column. When you see it in List view, it's not recognizing your HTML content. Now, All you need to do is put Christoph's javascript (that converts text into html in Sharepoint) in a CEWP, where you are displaying this list.

I have used this before and it worked just fine for me.

Answer 10

Got it to work.  Missed one big step.  Putting the CEWP on the page.  Got one last concern.  My view is in datasheet view and the cell displays the code. 

It would be great if I could make the field a Hyperlink field and get the workflow to work.  Any thoughts?  

Again, thank you for pointing me in the right direction to get the CEWP in place.

Robert 

Answer 11

If you are using Christoph's "text to html" approach, you don't need any workflow for this. I don't know for sure if that works in datasheet view. May be Christoph can help you with this.

If you are using a workflow on this list anyways, then all you need to do is construct the URL in the workflow and save that data in a single line text field. Sharepoint will automatically convert it into a hyperlink.

You can test it without going through workflow. Create a single line text field and enter any site address. It should be converted into hyperlink when viewing it in list view. Then check if it still shows as hyperlink in datasheet view.