Filter On data source connection

[Anonymous]

Hi all,

I am trying to connect to a SharePoint list and filter by user().email=person.email()

Filter('Active Field Leadership', 'Performed By'.Email=User().Email)

I previously had it wrapped in a Sort by columns  function

SortByColumns(Filter('Active Field Leadership', 'Performed By'.Email=User().Email), "Date", SortOrder.Descending)

i have been using a filter() query in my browse screen, but I think it has maxed out on the number of entries in the list.

How can I create a connection to the list that only pulls the list items of the user, rather than pulling all items and trying to filter?

[Anonymous]

989

I saved and closed Power Apps and opened it again with two loops. Screen shot attached.

Still showing older data though.

[yashag2255]

@Anonymous 

Seeing your attached screenshot, I see that the loop is running twice. Can you please share the result of CountRows(ListItems)?

[Anonymous]

22 within the first 500. 34 when i allow 2000 records

[yashag2255]

@Anonymous 

This means that you have only 22 records, out of all the records, that satisfy the condition, Performed By'.Email=User().Email .  Please verify the results in your sharepoint list if this differs from the actual results.

Based on your condition, it fetches only those records that belongs to current logged in user. If you want to fetch all the records, then you might want to get rid of this conditions(Performed By'.Email=User().Email )

Hope this Helps!

[Anonymous]

Yes. When I set 2000 as the row limit, I have 34 rows that meet that condition.

[yashag2255]

Can you please share the outputs of below expressions:

1) CountRows(Filter('Active Field Leadership','Performed By'.Email=User().Email && ID > 0))

2) CountRows(Filter('Active Field Leadership','Performed By'.Email=User().Email && ID > 500))

3) CountRows(Filter('Active Field Leadership',ID > 0))

4) CountRows(Filter('Active Field Leadership',ID > 500))

I am guessing that the issue you are seeing is because of the non delegable Email condition for the Lookup Field. This can be confirmed by the outputs I requested in this comment.

If Email condition is causing this issue, then we can get rid of this condition while collecting the collections and apply this when we are filtering the results into gallery

[Anonymous]

1) 20

2) 2

3) 500

4) 66

So weird, when i double check the sharepoint list I should have 26 entries. 6 entries after the ones shown. in the current visual

Same queries in the version that allows 2000 rows

1) 26

2) 8

3) 915

4) 481

---

@yashag2255 wrote:

Can you please share the outputs of below expressions:

1) CountRows(Filter('Active Field Leadership','Performed By'.Email=User().Email && ID > 0))

2) CountRows(Filter('Active Field Leadership','Performed By'.Email=User().Email && ID > 500))

3) CountRows(Filter('Active Field Leadership',ID > 0))

4) CountRows(Filter('Active Field Leadership',ID > 500))

I am guessing that the issue you are seeing is because of the non delegable Email condition for the Lookup Field. This can be confirmed by the outputs I requested in this comment.

If Email condition is causing this issue, then we can get rid of this condition while collecting the collections and apply this when we are filtering the results into gallery

---

[yashag2255]

@Anonymous 

As per the results from the last 4 queries, here is the explanation:

1) In the batch of 1=500, you got 500 results. Thats fine.

2) In the batch, 500-1000, there were only 66 records returned. The highest ID you got was 989. This was because a total of 989 rows were created but out of them few are deleted so instead of getting 489 records, it is getting 66, the ones that still exist in the list.

Now, coming to number of records, so since we got the matching results in both the cases(22 records). I don't think there is any problem with the queries since you got the matching results. I had used the same queries worked in one of my scenario where there were more than 10k+ data. Can you please check on your side that all the sharepoint list have the ID column value and please check once again on the number of records that you are expecting from the list. Kindly check if there are no extra spaces before or after the email address.

Hope this Helps

[yashag2255]

@Anonymous this is getting a bit longer, but if its not a prolem to you, you can try out:

In the app where you have saved Data Row limit as 500, 

ClearCollect(LoopCount,FirstN([0,500, 1000, 1500, 2000],RoundUp(First(Sort('Active Field Leadership',ID,Descending)).ID/500,0)));

Clear(ListItems);

ForAll(LoopCount,Collect(ListItems,Filter('Active Field Leadership',ID > Value)))

Now Gallery items as:

Filter(ListItems, 'Performed By'.Email=User().Email)

Please share the results of below queries:

1)CountRows(ListItems)

2) CountRows(Gallery1.AllItems)

Thanks,

[Anonymous]

1) 566

2) 22

Still not showing the 6 most recent items.