Confused about filtering Sharepointlist in PowerApps when SP list goes above 2000 items

[drawser]

Hi, ive been looking at this for some time now and I feel I dont understand whats going on here

Maybe someone can provide some more information?

Scenario, I have two sharepointlists

One is a list with adresses, one is a list a list with questions that are related to the adresslist via a lookup

So it builds a list of questions per adress by adding the adress from the lookup

Now I'm aware of the 2000 items limit, for now I get by it by adding a column called Sort, with for the first 2000 items i add 1 and for the next items I add 2

Then I collect these by filtering one collection on 1 and one collection on 2 and then pull those together in a third collection that I use to display the data in powerapps. This all works fine, I would however be able to get past the loadtime of collecting all items and then filtering them.

Now comes my issue, if I for instance setup a gallery to filter the Questionslist based on the selected adress name

This will work:

Filter(Questions, Adress.Value = varSelectedAdress.Adress)

 If I however want to change the filter and base it on the lookup ID this for some reason this wont work (it returns 0 items)

Filter(Questions, Adress.Id = varSelectedAdress.ID)

I also tried adding the Value function like this, this wont work either

Filter(Questions, Adress.Id = Value(varSelectedAdress.ID))

I really dont want to filter based on the Adress.Value cause at some point duplicate names might happen in the list. 

Why does the filter work when I base it on text and not the ID provided by the Sharepointlist?

The ID is there, if I set a textlabel to varSelectedAdress.ID it displays the correct ID

The Powerapp is set to handle 2000 items in settings

[v-jefferni · Accepted solution]

Hi @drawser ,

I really don't understand why the Address list could include duplicate values as it is assumed to be a main list in your scenario. It should be a key column in the main list for LookUp from the Questions list since I believe you will display the address value rather than an ID in the LookUp column in Questions list. Imaging that when you are working in SP adding a new item in Questions list and selecting values in the Address LookUp column, if there are duplicate addresses in the Address list, how could users know which is the proper one to select?

If you insist that there must be duplicate addresses in the main list, then you will need to use a LookUp column in Questions list that looks up to the ID of the Address list. Please refer to below thread:

https://powerusers.microsoft.com/t5/Building-Power-Apps/Delegation-warning-on-SharePoint-list-lookup...

So on your end:

Filter(Questions, Adress.Value = Text(varSelectedAdress.ID))

Hope this helps.

Best regards,

Community Support Team _ Jeffer Ni
If this post helps, then please consider Accept it as the solution to help the other members find it.

[drawser]

Hi, late reply here, easter vacation got in the way.

As for the adress list might having duplicate values at some point the scenario is:

The adress list also uses a lookup based from a list of cities. And two different citites might have the same street adress, something generic like Main Street 1 for instance. So the steps in the app always prompt users to select the city before they have the option to select the adress. Hence why I wanted to work with the ID from the adress list and not the name of the adress.

I looked at the other thread you linked to and this does indeed work. I have to do some rewrites of some patch functions I use but over all changing the lookup to get information from Column ID then add Adress as an additional field intead of vice versa does the trick.

Do you know why the ID isn't possible to filter on when you use it from a lookup and you go above 2000 items but all the other fields are?

[v-jefferni]

Hi @drawser ,

Please refer to the example 2 in below blog which I think explained enough and there is a solution as well:

https://powerobjects.com/powerapps/delegation-warnings-powerapps/

Best regards,

Community Support Team _ Jeffer Ni
If this post helps, then please consider Accept it as the solution to help the other members find it.