SharePoint Get item not returning lookup column defined as site column

[slyuso]

Hi,

This is a long question. But I preferred to include everything that I think is relevent.

Ultimate goal: use values from a list defined at site collection level. Business wants to create a couple of lookup lists that can be used in subsites.

Here is a short description of such set up.

1. Create a list at a site collection level (say, list “State Capitols”) with 2 text columns: State and Capitol
2. Create a site column name “LookupCapitols” as a lookup column that is getting values from “State Capitols” list. Of the list of columns select “State” and “Capitol.

After this, the site column is ready to be used in any of the subsites.

In subsite “TestSubsite”, create a list named “Customer” and add a column called “LookupCapitols” from the site columns.
By default, this will add 2 new columns to the list: LookupCapitols.State and LookupCapitols.Capitol.

Now, verify that when need to enter state in the “Customer” list, the field gets values from LookupCapitols.
Until the point there no questions.

Now comes Flow part.

I need to create a Flow with “Customer” list.

In Flow, I tried both “Get Items” and Get Item actions.
Both of them work almost as expected – returning items, most with extended properties -- with the only exception – it does not return value of both lookup columns: LookupCapitols.State and LookupCapitols.Capitol.

This is the issue I’m contacting you about.

Question: Is this a bug or just a missing piece of functionality that we might expect at some point?

Here my observations.

1. It might be a known limitation or an issue.
I created a SharePoint 2013 targeting the Customer list and tried to get an item.
Here is what I got:

NOTE. The screenshot shows a field named differently and is from actual site. 

2. On the other hand, I checked that this call to the same resource
https://xxxx.sharepoint.com/teams/yyyy/mmmm/_api/web/lists(guid'77a08c8e-xxxx-yyyy-97f1-40349d4c55aa...

returns a correct value for the lookup columns .

So, in principal, it is possible to get the value using REST call. What would be suggestions to resolve the issue?

Note. I tried to use HTTP call but was getting
<?xml version="1.0" encoding="utf-8"?><m:error xmlns:m="http://schemas.microsoft.com/ado/2007/08/dataservices/metadata"><m:code>-2147024891, System.UnauthorizedAccessException</m:code><m:message xml:lang="en-US">Access denied. You do not have permission to perform this action or access this resource.</m:message></m:error>

A workaround with HTTP call works for me but this is a new area for me and worth asking another set of questions related to authentication.

Any suggestions are greatly appreciated.

[Pieter_Veenstra]

The screenshot that you included is from a SharePoint Designer workflow. Not Microsoft Flow.

If you are using Office 365 then you mgith want to have a look at Flow instead.

Finally than lookup columns have you considered Managed Metadata columns?

[slyuso]

Hi,
Thank you for the response.
I'm working with Flow and this is Flow question.
Flow does not return the lookup column and does not provide any clue why,
So, for illustration purposes, I included the screenshot from SharePoint 2013 workflow.
Yes, I can use Managed metadata column. But I don't want to have just one column of this type.

Sharing lookup data in the way I described is a very common way and I'm interested to know if it's not supported or why.
 
 

[Pieter_Veenstra]

Ah, Now I understand. 

Ok, the problem is that your lookup column isn't comeing from the same site as where your list lives. I've seen a few more issues like this outside SharePoint. I think that this is actually a problem with the SharePoint APIs.

A few weeks back I had the same issue where I ended up creating the lookup lists locally to my lookup columns which I had to create on the list.

We might need to get some Microsoft/SharePoint guys involved in this.

[Pieter_Veenstra · Accepted solution]

I have now reported this here:

https://powerusers.microsoft.com/t5/I-Found-A-Bug/Site-Collection-lookup-columns-and-sub-sites/m-p/9...

[Anonymous]

How is this "solved" there is no solution, just more pointless promises from MSFT

[bdc604]

Don't worry, they have a 100 year road map.

Flow doesn't work with site column lookups, plan to fix by 2067.

Flow doesn't work with site column lookups from parent site (insert subsites r bad mantra), plan to fix by 2093.

Of course, whenever you point out basic functionality, you get a response like 'oh, please explain your business use case for that feature'. Where do they find these guys? Perhaps they should hire programmers familiar, or at least somewhat interested, with SharePoint features, functionality, concepts??

[Anonymous]

Kind of a goofy workaround, but I addressed this issue by using a second flow to get the value of the lookup column using the ‘When an item is created or modified’ trigger and then stored it in a separate String column I created on the list level using ‘Update Item’. I then used this column to filter Get Item in my first flow.

[bdc604]

I ended up using Send an HTTP request, looked at the XML and saw I just needed to add Id to the end of my column names to retrieve the values. Still despise the half baked ootb ‘solutions’ these guys provide.