Answered

I'm unable to filter a FirstN (200 records) when the table has over 500 records.

(0) Share

Report

Posted on by dannyshane

Scenario: My sharepoint list just reached over 500 records. I have a powerapp to add new records and search those records. I only need to be able to search the last 200 records from the current date.

Initially I used the below code to list the records and filter them by the search box:

SortByColumns(Filter([@'PurchaseOrders-List'], TextSearchBox1.Text in Items ), "PODate", If(SortDescending1, Ascending, Descending))

Once I went over 500 records, I noticed it would not find recently added records. So I found that I could limit the search by the last 200 by using the below code:

FirstN(Sort('PurchaseOrders-List', PODate,Descending),200)

However, I still need to be able to search those 200 records with a search box (TextSearchBox1), So I tried combining the two :

FirstN(Sort(Filter([@'PurchaseOrders-List'], TextSearchBox1.Text in Items), PODate, Descending), 200)

But as soon as I added the "Filter", It wont show me any of the recently added records (above 500), even though the prior "FirstN" did show them. Is this because I am unable to filter the FirstN ?

FYI:

I know I can expand my powerapps to 2000 max, but I will still eventually run into this issue, so I wanted to set it up properly now.

Does anyone now how to do this with the FirstN statement?

thanks for your time

Categories:

Building Power Apps

I have the same question (0)

All responses (7)

Answers (1)

Sort by

mdevaney 29,991 Moderator on at

Like
a
(0)

Report
Copy link

Link copied!
@dannyshane

Your issue is actually the IN operator. Not the FirstN function.

Filter([@'PurchaseOrders-List'], TextSearchBox1.Text in Items)

What type of column is Items? If Items is simply a Text column you could use the equals operator instead.

FirstN(Sort(Filter([@'PurchaseOrders-List'], Items=TextSearchBox1.Text), PODate, Descending), 200)

Let me know what type of columns Items is please 🙂

---
Please click "Accept as Solution" if my post answered your question so that others may find it more quickly. If you found this post helpful consider giving it a "Thumbs Up."

Was this reply helpful? Yes No
dannyshane 32 on at

Like
a
(0)

Report
Copy link

Link copied!

@mdevaney
Thanks for your reply, I appreciate it.
Items is a multi line text field.

I tried it that way you mentioned but it didn't work. this wouldn't list any records at all even with the text field blank (TextSearchBox1). When I used the 'in' operator it works, but it just doesn't show me any records entered after the 500 limit (even though i only am requesting 200 of them).

Do you know any other ways to do this?
thanks again for your reply

Was this reply helpful? Yes No
mdevaney 29,991 Moderator on at

Like
a
(0)

Report
Copy link

Link copied!

@dannyshane

Thanks for sharing the additional info that it’s a multi-line text field.

The bottom-line here is no workaround exists for what you want to do. SharePoint does not support delegation for the IN operator or the SEARCH function. Delegation cannot be avoided while trying to search a multi-line text field. This is something I have learned during development of many SharePoint Power Apps.

---
Please click "Accept as Solution" if my post answered your question so that others may find it more quickly. If you found this post helpful consider giving it a "Thumbs Up."

Was this reply helpful? Yes No
dannyshane 32 on at

Like
a
(0)

Report
Copy link

Link copied!

Darn. OK thanks.
I do want to mention I replaced the multi line text field (Items) with a single line text field (MFG) and it still didn't work. just in case there is something im missing.

so is this true to say: If i want to pull 200 records into a gallery, I can't search them with a text box? that sounds kind of odd because how else would people find the record they need?

Was this reply helpful? Yes No
mdevaney 29,991 Moderator on at

Like
a
(0)

Report
Copy link

Link copied!
@dannyshane

Yes, I agree it does seem odd that you cannot do a SEARCH or use the IN operator with SharePoint but that's just a limitation of the connector right now. The most common workaround is to use the STARTSWITH function which is delegation-friendly.

FirstN(Sort(Filter([@'PurchaseOrders-List'], StartsWith(Items, TextSearchBox1.Text)), PODate, Descending), 200)

Otherwise, if you want SEARCH capabilities you must use either a SQL or CDS datasource.

---
Please click "Accept as Solution" if my post answered your question so that others may find it more quickly. If you found this post helpful consider giving it a "Thumbs Up."

Was this reply helpful? Yes No
dannyshane 32 on at

Like
a
(0)

Report
Copy link

Link copied!

I appreciate your help and responses.
I wont drag it in further, but i do want to ask something just because I may be confused here.

I do use the "IN" operator and search on my sharepoint list via the powerapp. It is only when I reached 500 records and then the powerap woudlnt list the recent records (any created after the 500). So i tried to pull s limited amount (200) and search that.

But just in case we are talking about two different things... So it is possible to do searches and use the "iN" operator on sharepoint lists, correct? it is just not possible to use them with the FirstN function?

ok thanks. just making sure i understand properly

Was this reply helpful? Yes No
Verified answer

mdevaney 29,991 Moderator on at

Like
a
(0)

Report
Copy link

Link copied!

@dannyshane

Yes, you can use the SEARCH function and IN operator on SharePoint lists.

No, these functions will not return any results past the 500th row.

Here's a chart of what can be delegated in SharePoint

https://powerapps.microsoft.com/en-us/blog/sharepoint-delegation-improvements/

---
Please click "Accept as Solution" if my post answered your question so that others may find it more quickly. If you found this post helpful consider giving it a "Thumbs Up."

Was this reply helpful? Yes No