Gallery to Search by multiple fields and Sort by Latest 500 Entries. (Formula Help)

(0) Share

Report

Posted on by Questionasking

116

Hi,

I wish to use a searchbox to search a gallery by two fields 'DisplayName' and 'Assett. + Only Display the latest 500 entries, however i can only get errors could someone please enlighten me to why my code is wrong and perhaps show myself the correct code?

Thankyou, here is my incorrect code

FirstN(
 Sort(
 Filter([@Prestarts],TextSearchBox1.Text in 'Created By'.DisplayName || TextSearchBox1.Text in Assett.Value)
 ),[@ID], If(SortDescending1, Ascending, Descending
 ),500
)

Thankyou

Categories:

Building Power Apps

I have the same question (0)

All responses (6)

Answers (0)

Sort by

v-bofeng-msft Microsoft Employee on at

Like
a
(0)

Report
Copy link

Link copied!

Hi @Questionasking :

Please try this formula:

FirstN(
Sort(
Filter(Prestarts,TextSearchBox1.Text in 'Created By'.DisplayName || TextSearchBox1.Text in Assett.Value),

ID,

If(SortDescending1, Ascending, Descending)

),

500
)

Best Regards,

Bof

Was this reply helpful? Yes No
Questionasking 116 on at

Like
a
(0)

Report
Copy link

Link copied!
I should say i know how to get it working using a collection

Collect(colPrestarts, FirstN(Sort(Prestarts,ID,Descending),500))

and

FirstN( Sort( Filter(colPrestarts,TextSearchBox1.Text in 'Created By'.DisplayName || TextSearchBox1.Text in Assett.Value) ,ID, If(SortDescending1, Ascending, Descending )),1000 )

However its not what i want to achieve for another reason..

Was this reply helpful? Yes No
v-bofeng-msft Microsoft Employee on at

Like
a
(0)

Report
Copy link

Link copied!

Hi @Questionasking :

Do you want to replace colPrestarts with items in the gallery?

If so,the formula should be:

FirstN(
Sort(
Filter(GalleryName.AllItems,TextSearchBox1.Text in 'Created By'.DisplayName || TextSearchBox1.Text in Assett.Value)
,ID, If(SortDescending1, Ascending, Descending
)),1000
)

Best Regards,

Bof

Was this reply helpful? Yes No
Questionasking 116 on at

Like
a
(0)

Report
Copy link

Link copied!

@v-bofeng-msft wrote:
Hi @Questionasking :
Please try this formula:

FirstN(
Sort(
Filter(Prestarts,TextSearchBox1.Text in 'Created By'.DisplayName || TextSearchBox1.Text in Assett.Value),
ID,
If(SortDescending1, Ascending, Descending)
),
500
)

Best Regards,
Bof

Sorry that only displays the first 500 entries, check my other reply. im working with a list of over 5000 entries. (it cannot be made smaller..

Was this reply helpful? Yes No
Questionasking 116 on at

Like
a
(0)

Report
Copy link

Link copied!

@v-bofeng-msft wrote:
Hi @Questionasking :
Do you want to replace colPrestarts with items in the gallery?
If so,the formula should be:

FirstN(
Sort(
Filter(GalleryName.AllItems,TextSearchBox1.Text in 'Created By'.DisplayName || TextSearchBox1.Text in Assett.Value)
,ID, If(SortDescending1, Ascending, Descending
)),1000
)

Best Regards,
Bof

No Sorry, i was just saying that using a collection to download the latest 500 entries i can get the gallery to display the items i want, however when i use the same code with the sharepoint online list i get only the first 500 items ID in a sharepoint list

Was this reply helpful? Yes No
v-bofeng-msft Microsoft Employee on at

Like
a
(0)

Report
Copy link

Link copied!
Hi @Questionasking :

Do you need your Gallery to display all 5000 items in the data source? (Or make your formula can handle 5000 items in the data source)

First of all,Because you used "FirstN(XX,500)", your formula can only return 500 results at most.

Secondly,could tell me what colPrestarts is ?

I assume colPrestarts is a SharePoint list

The Point is that your formula cannot be delegated.So your formula can only process up to the first 2000 records in the data source

"FirstN" and "in" are functions that cannot be delegated

Lookup and choices column cannot be delegated

I think this link will help you a lot:
Power Apps delegable functions and operations for SharePoint

Understand delegation in a canvas app

At present, the only way is to save the items in colPrestarts in batches in a collection. Then filter this collection.

For example:

Collect(Prestarts,Filter(colPrestarts,Type="Type1"));/*I assume there is a single line of text column named Type*/ Collect(Prestarts,Filter(colPrestarts,Type="Type2")); Collect(Prestarts,Filter(colPrestarts,Type="Type3")); Collect(Prestarts,Filter(colPrestarts,Type="Type4")); ……

Best Regard,

Bof

Was this reply helpful? Yes No