Forum Discussion

Phil_COB's avatar
Phil_COB
Copper Contributor
Aug 09, 2019

Document Lookup Column Link

Hi, all,   In SharePoint Online I have a doc library (Reference Docs) and a list (Projects).   The goal is for users to choose a doc from a lookup drop-down in Projects, which, when clicked, will...
admin
Document Library
Lists
lookup
SharePoint Online
workflow
Phil_COB's avatar
Phil_COB
Copper Contributor
Sep 10, 2019

Vernon Jones 

 

Hi,

 

A workflow copies the link from the Name column to a "linked" column in the doc library.  To stop it from opening the doc's properties, it needs to be formatted using the following JSON:

 

{
"$schema": "https://developer.microsoft.com/json-schemas/sp/column-formatting.schema.json",
"elmType": "a",
"txtContent": "@currentField",
"attributes": {
"target": "_blank",
"href": "=@currentField"
}
}

 

I then used a look-up column to pull this link to a data list, and formatted it with the same code. 

 

Let me know if you need anything else. 

Sarastor's avatar
Sarastor
Copper Contributor
Jun 23, 2022

Phil_COB 

 

Hi there!

 

Thanks for the insctructions with JSON formatting. This helped me a lot and I am now able to click on the name of the file in my Sharepoint List which then opens the document from my library.

 

 

My question concerns a step more. I want to lookup multiple files from my library. When I only use one file to lookup it works but when I choose multiple files they appear in just one link and I can not click them one by one.
It looks like this and leads me to a blank Sharepoint Site.

 

 

 

Is there a way to apply JSON formatting once again to separate the links for each row?

 

 

Another problem occurs when I manually add the file which I want to lookup:
I select the row which I want to edit, then I select "open the details pane", then I scroll down to my Lookup JSON column and click it to add my file which I want to lookup. When I do this, it automatically opens a new Tab which leads me to my library. Is it possible that this does not happen and I can add my lookup file without opening a new Tab?

 

Thank you very much!

  • AIsmaili's avatar
    AIsmaili
    Copper Contributor
    Apr 05, 2023

    I had a similar problem with site pages, that were linked to custom lists. Clicking on the lookup link just opened the details of the page, not the page itself.
    I now created a power automate workflow that always copies the page (file-)name including .aspx into a dedicated text-field "Pagelink".
    In the custom list, I then add this textfield as additional lookup field in the lookup-column, so that I have the title from the original field and the filename from the text-field.
    With JSON-formatting I combine them to a link text and link like:

     

    {
    "$schema": "https://developer.microsoft.com/json-schemas/sp/v2/column-formatting.schema.json",
    "elmType": "a",
    "txtContent": "=[$InternalLookupName.lookupValue]", // InternalLookupName = put internal name of title lookup field here
    "attributes": {
      "class": "sp-field-quickActions button ms-Link",
      "href": "='/sites/yourSitehere/SitePages/' + @currentField", // yourSitehere = site url
      "target": "_blank"
    }
  }

     

    (please be aware, that you have to replace some values and remove the comments for the JSON to work)

    The real challenge came up, when trying to implement this with multiple values.
    The biggest problem was, that SharePoint did not recognize the multiple text values as an array, but instead I had to split the string with ';' (, and it was not possible to split with a space behind the semicolon '; ', so I also had to remove the space at the beginning of every page filename, except from the first one ...)

    This is how I implemented it:

     

    {
  "$schema": "https://developer.microsoft.com/json-schemas/sp/v2/column-formatting.schema.json",
  "elmType": "div",
  "style": {
    "display": "block"
  },
  "children": [
    {
      "elmType": "span",
      "forEach": "svc in [$InternalLookupName]", // InternalLookupName = put internal name of title lookup field here
      "children": [
        {
          "elmType": "a",
          "forEach": "pagelink in split(@currentField,';')",
          "txtContent": "=[$svc.lookupValue] + '\n'",
          "style": {
            "display": "=if(loopIndex('svc') == loopIndex('pagelink'), '', 'none')"
          },
          "attributes": {
            "class": "sp-field-quickActions button ms-Link",
            "href": {
              "operator": "+",
              "operands": [
                "/sites/yourSiteHere/SitePages/", // yourSiteHere = site url
                {
                  "operator": "?",
                  "operands": [
                    "=loopIndex('pagelink') == 0",
                    "=[$pagelink]",
                    "=substring([$pagelink],1,indexOf([$pagelink] + ';', ';'))"
                  ]
                }
              ]
            },
            "target": "_blank"
          }
        }
      ]
    }
  ]
}

     

    (please be aware, that you have to replace some values and remove the comments for the JSON to work)

     

    So this is my working solution, except that I will have to implement the link color as hard coded styles, as SharePoint seems to choose some random root-xxx classes on different views, which contain the wanted styles.


    Hope that helps in this SharePoint typical fiddle-topic ... 😉

Resources