cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Waldo100
Regular Visitor

SharePoint list - Lookup to second list with large number of items (15K+)

I have a SharePoint list (A) used to capture audit information. Part of the capture looks up to another list (B) within SharePoint holding over 15,000 records of assets.  I've tried to create a Power App to be more friendly on a mobile device, however when I add the Lookup combo box for the List ( A) pointing to the list (B) I get the standard warning about view threshold limits. I can't see on the forums any information to deal with this scenario.. typically they only cover searching the initial list... not the second list used as a lookup.

 

This works fine if used in SharePoint directly, however you can tell the SharePoint form is searching the asset list as it returns values as you type.

 

I've tried to edit the SharePoint form in power apps to figure out how this works to be able to replicate in power apps, but as soon as power apps tries to import the SharePoint form this too suffers from the threshold limit issue.

 

Any clues anyone??

 

1 ACCEPTED SOLUTION

Accepted Solutions

Hi @Waldo100 ,

SharePoint Lookup columns are a bad idea for Power Apps (and are totally unnecessary - see my blog on data structure), but you will not get one to work off a list of that size. The search function on a combo box is also limited to 2000 items, so finding one is not really possible, even if you based your Items off the other list.

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.

View solution in original post

4 REPLIES 4
WarrenBelz
Super User III
Super User III

Hi @Waldo100 ,

Depending on what you are doing with the second list, if you index and columns used in a Delegable Filter, it will work as usual.

 

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.

leyburn19
Memorable Member
Memorable Member

In powerapps,  are you talking about the yellow triangle?  that is not a warning based on view threshold.  It is about delegation.  Depending on your lookup code,  it may or may not be an issue even if if you get that warning.  I think your issue may be the combobox.  By Default it can only lookup a maximumn of 2000 records if you app is set to that limit.  The combo box can get a set based on a filter as long as the filter is delegable.  I assume the combo box uses a particular field from List A to match List B?  If the combo box uses Distinct() it is not Delegable. 

 

Yo may be better to put a Gallery with List B and a search box.  The filter on teh gallery uses the search box and you should be able create delegable searches with no problems.  Example:  I have a List with 180000 records.  Users can search that list based oncombinations or text, number and Listboxes 

Waldo100
Regular Visitor

Thanks guys for getting back to me.. Apologies in advance as I'm quite new to power apps.. and most of the guidance I've found online so far relates to searching where you want the results to appear in a gallery list. 

 

To try and figure out what was happening in my stand alone PowerApp, I decided to look at how SharePoint handles this to see if i could re-create the way it does it....  out of the box the default form in SharePoint works as it filters the combo choices as you type in a value (screenshots 1 and 2 below)

 

However if I try to customize the form in power query it stops working straight away.. I assume this is because the Items code is just a default Choices([@List Name].Column Name) and as there are 15k rows it just falls over in a heap. 

 

Can I change the code to include a filter to update the choices displayed as you type?

 

1 - Default SP form (lookup to 15K records in another list)1 - Default SP form (lookup to 15K records in another list)Using the default form combo box results update as you add more textUsing the default form combo box results update as you add more textTrying to edit in power apps.. (no changes made)Trying to edit in power apps.. (no changes made)

Hi @Waldo100 ,

SharePoint Lookup columns are a bad idea for Power Apps (and are totally unnecessary - see my blog on data structure), but you will not get one to work off a list of that size. The search function on a combo box is also limited to 2000 items, so finding one is not really possible, even if you based your Items off the other list.

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.

View solution in original post

Helpful resources

Announcements
PA User Group

Welcome to the User Group Public Preview

Check out new user group experience and if you are a leader please create your group

secondImage

Demo Extravaganza is Back!

We are excited to announce that Demo Extravaganza for 2021 has started!

MBAS on Demand

Microsoft Business Applications Summit sessions

On-demand access to all the great content presented by the product teams and community members! #MSBizAppsSummit #CommunityRocks

Power Apps June 2021

June Power Apps Community Call

Did you miss the call? Check out the recording here!

Top Solution Authors
Top Kudoed Authors
Users online (64,603)