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.
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
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.
Solved! Go to Solution.
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?
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.
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.
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??
We've updated and improved the layout and uploading format of the Power Automate Cookbook!
Fill out a quick form to claim your user group badge now!
We've given our badges an overhaul and also added some brand new ones!
Learn how to build the business apps that you need.
Find out where you can attend!
Watch & learn from the Power Automate Community Video Gallery!