Power App Stopped Pulling Choices from the target sharepoint list

[cgriese0310]

I have a custom Asset management Power App.  The App targets a list of Assets and Equipment to be checked out and a separate list to log the instances of Check out

My very first datacard is a drop down to select the Asset/Equipment you want to check out.  The choices in this drop down pull from the Asset List - a separate list maintained in SharePoint.  

The drop down is was working with no issues, but had suddenly stopped pulling in the choices from the Asset List.

I am having the same issue with some other drop down list in the app that are also targeting other lists for dropdowns, and cascading dropdowns.

When I open the App to Edit it in Power Apps Online, The drop down lists all work as designed.

In my dropdown items property I have the following expression:

Filter(TBL_Assets,Availability = "1")

I have the return set to Title - which is where I have the name of the asset/Equipment.

the issue is intermittent 

Please help me understand why I am seeing this issue, and help me figure out how to resolve it and make it stable?!

Thank you in advance! 

[newk1991]

How many items are in TBL_Assets after your filter? If it is more than 2000 records then you will continue to have issues. If that is the case then you will have to do some tricky work arounds like collecting the data onstart. 

--Collect over 2000 records--


This is a common issue. Unfortunately due to delegation issues specific to SharePoint list the search function is not delegable. You can use the function startswith(). However for most people that is not exact solution there looking for.

You can use a clearcollect() to collect in a local cashed table in PowerApps. This will only bring in the first 2000 record. There is a work around...

Basically you will do a filter 2 only show the first 2000 records and put that into a collection. Then you will collect the next 2000 records and so on. Each of these collections must have a different name. Once you have all the collections you can do a clearcollect() on all of the collections you make one massive table. You then will not have any delegation issues when referencing the local collection/table.

Here's an example:

Step 1.)

Create a new number column I'm in SharePoint and turn on index for it. Then create a Microsoft flow copy the SharePoint items ID for each row this new number column. The reason for the copy of the ID is because believe it or not the ID column only has delegation rights for "=" and we will need to use >= and <.


Step 2.)

Concurrent(


ClearCollect(CollectionA,Filter(NameofTable,ID#<2000)),
ClearCollect(CollectionB,Filter(NameofTable,ID# >=2000 And ID<4000)),
ClearCollect(CollectionC,Filter(NameofTable,ID# >=4000 And ID<6000)),


ClearCollect(CollectionD,Filter(NameofTable,ID# >=6000 And ID<8000)),


ClearCollect(CollectionE,Filter(NameofTable,ID# >=8000 And ID<10000)),


ClearCollect(CollectionF,Filter(NameofTable,ID# >=10000 And ID<12000)),


ClearCollect(CollectionG,Filter(NameofTable,ID# >=12000 And ID<14000)),


ClearCollect(CollectionH,Filter(NameofTable,ID# >=14000 And ID<16000))


)


 


Step 3.)


Then Collect your Collections together=)

;ClearCollect(NameofCollection,CollectionA,CollectionB,CollectionC,CollectionD,CollectionE,CollectionF,CollectionG,CollectionH)


 


You should now be able to Filter your NameofCollection with all 15000 records.  It just takes a bit of preloading first to get there.


If you would like some one on one coaching I do offer a free 15 minute session at digitalms365.com/help

[cgriese0310]

Thank you for taking the time to respond.   I only have 33 items in the list that it is targeting for choices.  

[WarrenBelz]

Hi @cgriese0310 ,

With only 33 items, delegation will not be an issue and Text or Numeric fields are Delegable anyway.

Briefly on that subject - here is a post of mine for @newk1991 's reference that may be a bit cleaner way of collecting big lists.

However this bit caught my attention

Filter(TBL_Assets,Availability = "1")

Is Availability or Text or Numeric field in the data source?

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.

[cgriese0310]

@WarrenBelz  - thanks for the response.  To answer your questions, it is in-fact a text field.  Originally we were going to use "Available vs. Unavailable in that field, however 1 and 0 was just simpler... 

[WarrenBelz]

Hi @cgriese0310 ,

This seems a bit strange - you are referencing it correctly and intermittent problems generally have causes not always directly related to what seems obvious.

If you put this on a button

ClearCollect(
   colTest,
   Filter(
      TBL_Assets,
      Availability = "1"
   )
)

and then have a label on the screen

CountRows(colTest)

and run it a few times, do you always get the number of records you expect?

[WarrenBelz]

Hi @cgriese0310 ,

Just checking if you got the result you were looking for on this thread. Happy to help further if not.

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.

[cgriese0310 · Accepted solution]

@WarrenBelz - I did not really find a good solution, but I also have not had the issue come up again.