cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Questionasking
Helper III
Helper III

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

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

6 REPLIES 6
v-bofeng-msft
Community Support
Community Support

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

 

Questionasking
Helper III
Helper III

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..

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

 


@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..


@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

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

Helpful resources

Announcements
Super User 2 - 2022 Congratulations 768x460.png

Welcome Super Users

The Super User program for 2022 - Season 2 has kicked off!

Power Platform Conf 2022 768x460.jpg

Join us for Microsoft Power Platform Conference

The first Microsoft-sponsored Power Platform Conference is coming in September. 100+ speakers, 150+ sessions, and what's new and next for Power Platform.

<
Users online (2,163)