12 lookup column limit: PA to SharePoint - NEED HELP

[Daryl_K]

I have been ordered to replace an aging Infopath solution using Power Apps this spring. The solution must connect to a corporate SharePoint list that has a large number of columns, and clearly way more than the prescribed 12.

The list is not mine and I have no control over it, so deleting or changing columns is not even a remote possibility. The owner would permit me to make a View, and I could live with one that has fewer than 12 lookup columns, but as PA does not obey Views this is not a valid solution.

I have been searching the forums for weeks and the only suggestion I have found is using Flow to get filtered items to your Power App, but the instructions are poor and I have been unable to get it to work, nor have I found anyone else who was able to get it to work to ask about it. I have found numerous conversations with input from Microsoft saying a fix to allow PA to obey Views is being considered, but no word if this will happen this year, this decade, or at all.

C'mon Microsoft, throw me a bone! I was able to establish this same data connection 7 years ago using Infopath. How can it be that after so long Power Apps is still unable to do the same? Are there any workable solutions now or on the immediate horizon???

[Daryl_K]

BTW, the article I refer to above is linked here. For some reason this editor does not allow me to embed a URL in th eoriginal post, but I can put it in this reply. (?)

https://www.netwoven.com/2018/12/11/how-to-get-filtered-items-from-sharepoint-list-to-powerapps-usin...

[WarrenBelz]

Hi @Daryl_K ,

I have picked your thread up from the unanswered list and will try to help you.

The first question I have is why need to use a Flow to get items into SharePoint - I need to understand what you are trying to achieve.

[Daryl_K]

HI. The initial post in this thread describes the issue in detail. I need to be able to make a data connection to a different, very large SharePoint list and retrieve a few columns. The other list is beyond my control so I cannot edit it, but trying to connect now produces the 12-column lookup limitation error.

[WarrenBelz]

OK @Daryl_K ,

Are you trying to create a new list with these columns or do they align with a common key with something you already have?

[Daryl_K]

Hi. Not a new list, but populate drop-downs.

The very large SharePoint list I mention (that I have no control over) is a manufacturing scheduling tool that has hundreds of columns and thousands of rows. The form and list I am re-creating is a Final Test and Inspection tool, but in order to enforce that each specific product is entered into the database only once the current form has a 1-2-3-step drop down where the first one shows unique entries only for manufacturing location, then #2 lists all project numbers assigned to be built in that location, then #3 lists all deliverables that are to be built as part of that project number.

Power Apps seems to have a similar filtering function int he drop downs ("Depends on...") but because the scheduling list is so huge I cannot pull data over in Power Apps (I made the old form in Infopath and used Data Connections to populate the three drop-downs).

[WarrenBelz]

Thanks @Daryl_K ,

I apologize if I still have not got my mind completely around this, but are you trying to get a set of distinct values from a field in this huge list into a drop down in your app? Can you please describe in a few short sentences exactly what result you are trying to achieve?

[Daryl_K]

The scheduling SharePoint list (the one I have no control of but need to connect to) is used to track and record status for builds. Each row represents one deliverable that must be built and shipped. There are a multitude of columns, but only three or four of concern to this question. 

One (columns) represents the manufacturing location. There are a few dozen possible, but each row will of course have only one entered. Because there are thousands of builds scheduled each location will be in many hundreds of rows, so the first drop-down only needs a list of unique entries from that column.

The next drop-down will be populated from a "project number" column, but filtered on the first row. So if they were to select "NYC" the drop-down would show only project numbers that have "NYC" in the "location" column. 

The third would behave like the second, but filtered on the second (instead of the first). Same idea, but shifted over one to the right.

[WarrenBelz]

Thanks @Daryl_K ,

It was exactly what I suspected from my last post.

The Distinct filter is not delegable, so you cannot do the job directly with this. This post shows a workaround of sorts, to do a collection (on which the Distinct filter will then work), but I suspect list your might be a bit bigger than this.

If the old list is not likely to change a lot (new locations etc), you might go low-tech and export to Excel, removed duplicates and then format some tables and link the these in OneDrive.

Please click Accept as solution if my post helped you solve your issue. This will help others find it more readily. It also closes the item. If the content was useful in other ways, please consider giving it Thumbs Up.

[Daryl_K]

Thank you, but unfortunately I am not even that far yet. I cannot even establish a connection from the Power App to the scheduling SharePoint list because of the 12 lookup column limitation described in the title. I don't need all the columns but I cannot edit or delete anything from the scheduling list, so changing the source is not possible. A View with only the necessary columns would help, but the Power Apps connector does not obey Views, so it brings in all 100+ columns (or rather tries to, then errors) no matter what.

This is what lead to the Flow as a way of creating a Collection and bringing over only the necessary columns, but the one article every one references does not seem to work. At least I cannot get it to, and everyone I've asked that has tried it cannot either, so either it does not work or the article poorly describes it and thus we're all doing it wrong.