locked
how to created calculated field that is a hyperlink RRS feed

  • Question

  • I am trying to create a hyperlink in a calculated field.  The text string renders, but only part of it is a hyperlink. I used this syntax:

    https://domain/sites/itd/SBTD/TSG/General%20Documents/Forms/by%20Document%20Category.aspx?View={373A9A36-C01A-4616-BADA-065DEEBC44C0}&FilterField1=Agreement&FilterValue1=IAA 2011-12-13

    Then, based on this article: http://blog.pathtosharepoint.com/2008/09/01/how-to-open-hyperlinks-in-a-new-window-or-not/,  I tried writing it as HTML like this:

    =CONCATENATE("<a href=’”,”https://domain/sites/itd/SBTD/TSG/General%20Documents/Forms/by%20Document%20Category.aspx?View={373A9A36-C01A-4616-BADA-065DEEBC44C0}&FilterField1=Agreement&FilterValue1=",Agreement Number,”’>”,documents,”</a>”).

    That got my an error regarding illegal syntax.

    Does anyone know how to do this in the UI?


    There are no mistakes; every result tells you something of value about what you are trying to accomplish.


    • Edited by run4it Tuesday, August 27, 2013 6:40 PM
    Tuesday, August 27, 2013 6:28 PM

Answers

All replies

  • Hi

    If what you have typed is an exactly what was in your calculated column formula, I think the problem may be just the lack of square brackets around the Agreement Number. As the column name has a space, it should read [Agreement Number].

    Mike

    Tuesday, August 27, 2013 6:42 PM
  • what portion of the formula actually renders as a hyperlink?

    Please mark my response as an answer if appropriate.
    Learn.SharePoint.com

    Tuesday, August 27, 2013 6:44 PM
  • When I put square brackets around [Agreement Number] I received an error about referring to a column that does not exist.

    There are no mistakes; every result tells you something of value about what you are trying to accomplish.

    Tuesday, August 27, 2013 6:45 PM
  • the part before View=.    It does the same thing if pasted into MS Word.

    There are no mistakes; every result tells you something of value about what you are trying to accomplish.

    Tuesday, August 27, 2013 6:46 PM
  • Where are you trying to get the "IAA 2011-12-13" from?

    By the look of what you are trying to do, this would read the value from a column called "Agreement Number" and embed it in to the URL. Do you have a column called "Agreement Number"?

    Tuesday, August 27, 2013 7:00 PM
  • I did a few more experiments.  I believe it is the curly brackets.  When I take them out the URL renders out at least to the space in my Agreement Number which is in the format of " ABC XXXX-XX-XXX", which may not work anyway without changing my naming convention of the Agreement Number.  Anyone know what why the { screws it up?

    There are no mistakes; every result tells you something of value about what you are trying to accomplish.

    Tuesday, August 27, 2013 7:24 PM
  • I don't think the brackets are actually even required, but if they are you should URL encode them

    Please mark my response as an answer if appropriate.
    Learn.SharePoint.com

    Tuesday, August 27, 2013 7:26 PM
  • I replaced the curly brackets with %7B and %7D and now I'm just left with the issue of the space in the Agreement Number.  Any ideas?

    There are no mistakes; every result tells you something of value about what you are trying to accomplish.

    Tuesday, August 27, 2013 7:27 PM
  • space is %20

    Please mark my response as an answer if appropriate.
    Learn.SharePoint.com

    Tuesday, August 27, 2013 7:30 PM
  • I know space is %20, but I don't see that a REPLACE function is available for me to use to wrap around [Agreement Number]

    There are no mistakes; every result tells you something of value about what you are trying to accomplish.

    Tuesday, August 27, 2013 7:57 PM
  • I found this javascript that took care of the encoding issue as well as letting me make a friendly link.

    http://blog.pathtosharepoint.com/2008/09/01/using-calculated-columns-to-write-html/


    There are no mistakes; every result tells you something of value about what you are trying to accomplish.

    Tuesday, August 27, 2013 9:50 PM
  • That link is the HARD way. Try setting your calculated column's return value to number instead of text. SharePoint will make you happy :)

    (Thanks to http://sharepoint.rackspace.com/calculated-columns-tutorial for that little tip-ola.)

    • Proposed as answer by Golfarama Wednesday, January 6, 2016 4:09 PM
    Wednesday, November 12, 2014 4:10 PM
  • Older post but since this is what comes up first on a google search, just thought I'd add something. I needed to add a column that just linked to the "View" page for an item (the Map View doesn't have clickable Title Links).

    Anyway, it was easy to do..

    1) Create a Calculated column

    2) Set the formula to 

    ="<a href='http://...../sites/mySiteName/Lists/myListName/DispForm.aspx?ID="&ID&"&InitialTabID=Ribbon.Read'>View Details</a>"

    3) make sure to set your Calculated Output type to NUMBER and not text.

    Then, at least for me, I added this field to only the Map View and now users can click the hyperlink.

    • Proposed as answer by Mult0 Wednesday, March 16, 2016 12:50 PM
    • Unproposed as answer by Mult0 Wednesday, March 16, 2016 12:50 PM
    • Proposed as answer by Jonas Gro Thursday, July 20, 2017 4:25 PM
    Wednesday, August 26, 2015 5:54 PM
  • this solution work great!! thank you
    Wednesday, March 16, 2016 12:49 PM
  • Thank you, :)
    Wednesday, August 24, 2016 10:49 AM
  • This trick with setting the calculated field to 'Number' doesn't work anymore in SharePoint Online since 13th of June 2017: https://support.microsoft.com/en-us/help/4032106/handling-html-markup-in-sharepoint-calculated-fields 
    Wednesday, June 14, 2017 2:28 PM
  • https://support.microsoft.com/en-us/help/4032106/handling-html-markup-in-sharepoint-calculated-fields
    Wednesday, June 14, 2017 2:29 PM
  • Thanks this item has really been helpful, in my quest why this stopped working all of a sudden.
    Wednesday, June 14, 2017 9:15 PM
  • Great - so Microsoft makes SharePoint less useful, yet again.  Well done.
    Friday, June 16, 2017 1:21 AM
  • Interestingly, I have found that under particular circumstances HTML markup in calculated fields still works.  I am too scared to disclose in public - since if I do, then Microsoft will break these as well.
    Wednesday, June 21, 2017 1:11 AM
  • Denis, If you have a work around I could really use it.  I have links in lists all over the place.  Users are noticing!



    Thursday, June 22, 2017 12:33 AM
  • Contact me directly and we can talk.  Maybe via Linkedin?  You should be able to find me easily (Melbourne Australia).
    Thursday, June 22, 2017 3:12 AM
  • Further follow-up on this ... I logged a case with our service desk and they have finally come back and said "it works" ... which it now does!  Everything is now back to what it was before the 13 June 2017 update.

    I'm not sure what they did, or if maybe Microsoft reverted their change, whether it's specific to our company, or something else ... nor am I sure whether what I thought was a 'workaround' last week was actually them implementing this workaround/reversion.

    Anyway ... things are back to normal and I can now spend another week undoing all my secondary awful workarounds that I spent the last two weeks implementing ...

    Monday, June 26, 2017 4:51 AM
  • I used this solution, it was pretty easy, I made sure I used the correct internal name for my field that I needed to display the html markup and also make sure the JS was successfully being called from my web part and my links came out fine: http://www.carlosmontilla.com/single-post/2017/06/14/SharePoint-Calculated
    • Proposed as answer by arevsun Tuesday, August 8, 2017 7:22 PM
    Thursday, June 29, 2017 6:51 AM
  • This is exactly what I was looking for!  Thank you!
    Tuesday, July 25, 2017 1:02 PM
  • https://support.microsoft.com/en-us/help/4032106/handling-html-markup-in-sharepoint-calculated-fields
    Friday, August 4, 2017 10:34 AM
  • In SharePoint Online, there is a new feature that allows you to embed JSON within a column for formatting (at the very bottom of the screen when you edit a column: "Column Formatting: 
    Change the display of this column by adding JSON below." 

    The SharePoint team has provided an example of how you can build a URL in github here:

    https://github.com/SharePoint/sp-dev-column-formatting/tree/master/samples/generic-hyperlink-values 

    I needed to append the List Item ID onto the end of a custom page and it worked like a charm, here was my code if it's helpful (btw I created a calculated column that just equaled [ID]):

    {
        "$schema": "http://columnformatting.sharepointpnp.com/columnFormattingSchema.json",
        "debugMode": true,
        "elmType": "a",
        "txtContent": "View Details",
        "attributes": {
           "target": "_blank",
           "href": {
              "operator": "+",
              "operands": [
                 "https://{yourtenant}.sharepoint.com/Demo/SitePages/YourPage.aspx?formID=",
                 "@currentField"
              ]
           }
        }
     }


    Tuesday, December 19, 2017 5:17 AM