Document Lookup Column Link

[Phil_COB]

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 directly open the chosen document stored in Reference Docs.

So, in Reference Docs I created a workflow that sets the value of the Title field with the Name field.

In Projects I created a lookup column referencing the Title field in Reference Docs

All works fine, except the link opens the document properties, not the document itself.

This should be simple, but I just can figure out what is incorrect.

Any help will be appreciated.

[#2]

Unfortunately there is not a direct way to achieve this...one posible solution could be to customize the project list forms using PowerApps

[#3]

@Juan Carlos González Martín 

Thanks for the reply.  Maybe I wasn't clear in my question.

The list (Projects) is like a spreadsheet, not a form.  In Quick Edit, I wanted users to choose a document from a look-up column connected to a library (Reference Docs). 

Since you can't base a look-up on the Name column, I assumed you could achieve this by setting the Name field to the Title field and basing the look-up on the Title field.

Everything works, except when Quick Edit is closed the look-up column shows the document name as a link, but clicking it opens the document's properties page, not the document itself as you would get by clicking the Name column.  

Does that make more sense?

[#4]

@Phil_COB

I found the same issue.  In the Classic UI, lookup documents opened directly from the list, but in Modern, clicking the document takes you to the document properties, which is not very useful.

Also, if you open the list item and click the lookup document, it opens a property window, but clicking the link to the document name doesn't do anything.  The only way to open the document is to right-click and open in a new tab.

It appears that the functionality for opening lookup documents didn't come over from Classic.  Not opening from a property window may be a bug.

[#5]

@Vernon Jones 

Thanks for the reply.  I figured this was the case.

I did get it to work, but it took a workflow and JSON coding to extract the link elements then combine them to recreate the link.

What was once simple is now way too complicated... 

If you want details, please let me know.   

[#6]

@Phil_COB 

I'm not sure if I'll go that route, but I'm curious, so yes, please send the details.

Thanks!

[#7]

@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. 

[#8]

@Phil_COB 

Thanks! Great workaround. I have the link working, but are you able to display the name of the file instead of the url?  If so, I need a pointer in how to fill in that missing piece.

[#9]

@Vernon Jones 

It wasn't part of the scope of the project, so no.  I'll experiment and if I find anything I'll let you know.

[#10]

@Vernon Jones 

I had a second lookup column that was coded with JSON to show a button as the link:

{
"$schema": "https://developer.microsoft.com/json-schemas/sp/v2/column-formatting.schema.json",
"elmType": "button",
"txtContent": "Open",
"customRowAction": {
"action": "defaultClick"
}
}

Unfortunately, my internal customer has discovered that they no longer get links :(

I am assuming that there has been an Evergreen update that has broken the functionality?

I found this thread in my research to find a new solution.

[#11]

@Phil_COB 

Dear Phil,

I am in a very much similar situation as you were.

I have a "Topics by Meetings" list in which Topics (from topics library) are associated with each Meeting (from Meetings List).

This was structure as such, because each Topic can be discussed in a series of meetings before it is actually "approved", while:

- the "evolving" documentation remains one single Topic folder

- the "Topics by Meetings" registers all the comments discussed about the Topic in each Meeting 

In this image, you will see the "Number" column which is a "lookup" from Topics as a hyperlink.

As you mentioned, the hyperlink points to the Document Properties rather than the actual Folder.

I tried to apply you json formatting....

------------------------------------------

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

------------------------------------------

and it resulted as follows:

Would you know what am I missing to get the hyperlink properly pointing to the original lookup folder ?

Appreciate any inputs...

Cheers

[#12]

On a lookup field, you need to reference @currentField.lookupId and/or @currentField.lookupValue to access its properties.

[#13]

@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!

[#14]

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 ... ;)